Working With CSVs In Windows PowerShell

CSV ( Comma Seperated Value)

A CSV file is a text file but is not meant to be read as text files. A CSV file stores the table data in plain text with each file separated by a comma. The file extension for CSV file is “.csv”. Let’s see how we can work with CSVs in Windows PowerShell.

Find All CSV Cmdlets

To find all CSV Cmdlets in PowerShell, use Get-Help along with a wildcard character to search for CSV.

Example

PS C:\ > Get-Help *csv*

[ As you can see in the above screenshot, it returned all the Cmdlets that have CSV.]

Export-Csv

Use this Cmdlet to export the result of your Cmdlets to a CSV file.

To better understand this, let’s first run a simple Cmdlet and then export the output of that Cmdlet to a new CSV file. The below example will help you understand this concept better. In the below example, I run Get-Service Cmdlet to get a list of all the services (running and stopped services) and then I will export the results to a CSV file.

Example

PS C:\ > Get-Service



Image: List of all the services ( Running and Stopped Services )

Now, to store this list of services information into a CSV file, you can make use of Export-Csv Cmdlet.

Example

PS C :\ > Get-Service | Export-Csv -Path C: \Example.Csv



[ Note: -Path is a mandatory parameter if you use Export Cmdlet. In the above Cmdlet, Example.Csv is the new file I created to store the output of Get-Service Cmdlet]

The above Cmdlet will export the results of Get-Service into Example.csv file in my C: Drive. To verify this, let’s open it using Notepad.

PS C:\ > notepad C:\Example.csv



As you can see above, it exported the results of Get-Service to Example.Csv file and each file is separated by a comma. If you want to remove the type information ( First line in notpad), add -NoTypeInformation parameter

PS C:\ > notepad C:\Example.csv -NoTypeInformation

Import-Csv

Import-Csv Cmldet creates a table-like custom objects from the items in a CSV file. This Cmdlet provides us a way to read in data from a comma-separated values file (CSV) and then displays that data in tabular format ( Column : Data ).

To better understand Import-Csv Cmdlet, Let’s look at our Example.Csv file ( Comma Separated Values) that we just created and import it into PowerShell.

Example

PS C:\ > Import-Csv -Path C: \Example.Csv



Output



If you notice the output of Import-Csv Cmdlet, it just brought back all the information from the Example.Csv file and displayed in Column name and Data ( ColumnName:Data ) format.

ConvertTo-Csv

ConvertTo-Csv and Export-Csv do the same job, the only difference between these two Cmdlets is - if you use Export-Csv, you can’t further manipulate it. But ConvertTo-Csv does allow you to manipulate the results. Let’s look at an example to understand this better.

Example

PS C:\ > Get-Service | Export-Csv -Path Example1.csv | Out-file C:\Example2.csv



In the above example, I put the output of “Get-Service” into a CSV file called Example1.csv file, then write it to another file called “Example2.csv. After that, I was trying to display Example1.Csv and Example2.Csv directories to test if Example2.csv has any content in it, but I found out that Example2.csv file is empty (Length is 0 ). That means, Export-Csv Cmdlet did not write the results of Example1.csv file to Example2.csv.

It is very clear that using Export-Csv Cmdlet, we can not manipulate it further once we run Export-Csv Cmdlet. But this can be done using “ConvertTo-Csv” cmdlet. Let’s look at that in the below example..

Example

PS C : > Get-Service | ConvertTo-Csv | Out-file -

Filepath C:\Example3.Csv



It’s clear that using ConvertTo-Csv, we were able to Convert the results to Csv and then write it to Example3.Csv.

[ Important Note: If you use Export-Csv Cmdlet, you cannot manipulate the results, but ConverTo-Csv lets you to manipulate the results ]

Now, let’s import this Example3.csv file into PowerShell to verify.

Example

PS C:\ Import-Csv C:\Example3.Csv



Output