Importing and exporting csv files with Powershell

Many applications are able to export data into a CSV file, making it easy to import the data into a separate application.

Fortunately, Powershell is no different!

If you wish to export data from Powershell as a CSV file, or import a CSV file into Powershell, you can use the Export-CSV and Import-CSV commands respectively.

Importing a CSV file

To begin with, let's create a file called people.csv, and populate it with the following data:

Name,Age,Location
Mike,20,London
Doug,43,New York
Mary,21,Sydney
Linda,42,Paris

To import it into powershell, you can copy it over to the same folder as your script, and use the Import-CSV command:

$csv = Import-CSV $PSScriptRoot'/people.csv'

# View the output of the imported CSV
$csv

Name  Age Location
----  --- --------
Mike  20  London  
Doug  43  New York
Mary  71  Sydney  
Linda 42  Paris 

Accessing data within the CSV

If we run GetType() on the imported CSV, we see the following:

$csv.GetType()

IsPublic IsSerial Name      BaseType     
-------- -------- ----      --------                                                                       
True     True     Object[]  System.Array                                              

Since the object is an array, we can access the elements as we would an array:

# Get the person at index 2
$csv[2]

Name Age Location
---- --- --------
Mary 21  Sydney  

Let's say Mary no longer wants to be called Mary, and prefers the name Sue.

We also got her age wrong, and she is only 31.

We can change both of these by simply changing the Name and Age properties on the object:

$csv[2].name = "Sue"
$csv[2].age = 31

# Show the changes
$csv

Name  Age Location
----  --- --------
Mike  20  London  
Doug  43  New York
Sue   31  Sydney  
Linda 42  Paris 

Exporting a CSV file

To export from Powershell to a CSV file, we can use the Export-CSV command, passing in the new path with the -Path parameter:

<object-to-export> | Export-CSV -Path <export-path>

For example, to export our updated CSV object, we can do the following:

$csv | Export-CSV -Path $PSScriptRoot'\peopleUpdated.csv'

If you open the new peopleUpdated.csv file within a text editor, the format will appear like this:

#TYPE System.Management.Automation.PSCustomObject
"Name","Age","Location"
"Mike","20","London"
"Doug","43","New York"
"Sue","31","Sydney"
"Linda","42","Paris"