Reading CSV-files in PowerShell

If you work in IT, you have most likely stumbled upon a CSV-file. CSV stands for Comma-separated values. You can read more about CSV-files here.

Two IT-related examples of what data a CSV-file can contain would be a list of AD-users or network traffic. I’ll use network traffic as my example. I’m not going to spend more time explaining what a CSV-file is, but I will show you how to read its content with PowerShell!

Please take note that everything in this post is done with PowerShell.exe, and some examples might not work in ISE or VSCode.

Let’s say you got a file called traffic.csv. The raw content of the file looks like this:

csv1

In this case the file is located under $home\documents\traffic.csv

In order to read this CSV-file in PowerShell, we’ll have to use the Import-CSV cmdlet. If you don’t know how to discover PowerShell commands, you can read about that in my previous post How to discover PowerShell Commands.

There is a twist tho. First we need to create a variable that we assign the Import-CSV command to. By storing the output (objects) in a variable, we can more easily play around with the data:

 $data = Import-Csv .\traffic.csv -Delimiter "," 

The parameter that we are adding is -Delimiter, delimiter is saying what character is separating the data within the CSV-file. In our example it’s a comma.

Now if we just run our variable $data we’ll see the contents of the file.

data1.png

If we pipe our $data variable into Get-Member we can see what kind of properties and methods we have available. (Properties being the most interesting right now.)

data2gm.png

Notice that sourceIP, destinationIP, port and action are all available for us to play with. If you don’t know how, check out my previous post on Filtering and using Comparison operators in PowerShell.

Back to our CSV-file: :Let’s say that we only want to display the events where the traffic was allowed. We would then run:

 $data | Where-Object {$_.action -eq 'Allow'} 

csv2.png

What if we want to see all the traffic that is not on port 80?

 $data | Where-Object {$_.port -ne '80'} 

csv3.png

(Again, if you don’t know what {$_.port -ne ’80’} means, check out this post.)

Besides using Where-Object to filter out the data, you can also use Sort-Object to sort the data. Say we wanted to sort on the action property. We would run this:

 $data | Sort-Object -Property action 

csv4.png

You can also use Select-Object to select the properties you’d like:

 $data | Select-Object -Property destinationIP,port 

csv6.png

You can also combine these cmdlets:

 $data | Where-Object {$_.destinationIP -like "*0.0.1"} | Sort-Object -Property action,port 

csv5.png

Alright. That was my very short, but very straight forward write-up on how to read CSV-files in PowerShell. I hope you found this post helpful. Please consider following the blog using the button on my homepage. That way you’ll receive an email when I publish a new post!

Feel free to leave suggestions about other topics in the comments.

// Tr4p

2 thoughts on “Reading CSV-files in PowerShell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.