Managing CSV Files using Import/Export-CSV in Powershell

In the PowerShell series, we are looking into working with files in PowerShell. The first types of files we are covering which are used are CSV(comma separated values) file types. We are going to look into two important cmdlets import-csv/export-csv which are widely used while working with CSV.








We start with the CSV file extension, as this is the most basic one. We will make use of the previous example, where we stored the currently running processes to file:

#Defining file for export
$exportedFile = 'C:\temp\exportedProcesses.csv'

#Exporting as CSV - basic
Get-Process | Export-Csv $exportedFile

#Opening the file
psedit $exportedFile

By default, Export-Csv will write a comma-delimited file using ASCII encoding and will completely overwrite any file using the same name.

Export-Csv may be used to add lines to an existing file using the Append parameter. When the Append parameter is used, the input object must have each of the fields listed in the CSV header or an error will be thrown unless the Force parameter is used.

After running this simple example, you will have the opened CSV file in front of you, which consists of all the processes and each value, separated by commas. And that is what CSV actually stands for comma-separated values. The benefit of working with CSV files is that you will get table-like custom objects returned, which can easily be filtered. This file type makes sense, especially for simple data objects.

Export-Csv can be instructed to exclude this header using the NoTypeInformation parameter:

Get-Process | Export-Csv processes.csv -NoTypeInformation 

Importing is very straightforward.

#Importing CSV file
$data = Import-Csv $exportedFile

#Showing content
$data | Out-GridView

Comma-Separated Values (CSV) files are plain text. Applications such as Microsoft Excel can work with CSV files without changing the file format, although the advanced features Excel has cannot be saved to a CSV file.

By default, Import-Csv expects the input to have a header row, to be comma-delimited, and to use ASCII file encoding. If any of these items are different, the command parameters may be used. For example, a tab may be set as the delimiter.

Import-Csv TabDelimitedFile.tsv -Delimiter `t 

A tick followed by t (`t) is used to represent the tab character in PowerShell.

Data imported using Import-Csv will always be formatted as a string. If Import-Csv is used to read a file containing the following text, each of the numbers will be treated as a string.

#Showing its type
$data | Get-Member # TypeName: CSV:System.Diagnostics.Process
$data[0].GetType() # PSCustomObject
$data.GetType()    # System.Array

It's interesting to see here what type is being retrieved after you import the CSV file. The Get-Member cmdlet on the $data object itself shows that it is a CSV file, and the exported objects are of type System.Diagnostics.Process. But, after taking a dedicated look at the first object and at the type of the container, you will recognize that the imported object cannot be used as a process anymore. It has become a PSCustomObject. Nevertheless, it is still an improvement over exporting it as a plain string. You can easily import it and use it as a simple data store.


The next big benefit when working with CSV files is that you can make them editable with Microsoft Excel. To achieve this, you just need to change the delimiter from comma (,) to semicolon (;), as this is the default delimiter for Excel files. You can use the dedicated -Delimiter flag for this task.

#Exporting as CSV with specified delimiter ';'
Get-Process | Export-Csv C:\temp\exportedProcesses.csv -Delimiter ';'

#Importing the data
$data = Import-Csv C:\temp\exportedProcesses.csv -Delimiter ';'

#Showing the data
$data | Out-GridView

Be careful though here, as this is a culture-specific-behavior. To avoid the problems with the different cultures, you can use the flag -UseCulture.
Now, editing with Excel is possible. To demonstrate the power of PowerShell, we will now open up the file with Excel via PowerShell and the use of the ComObject of Excel itself.

#Create ComObject for Excel
$excel = New-Object -ComObject Excel.Application

#Make it visible
$excel.Visible = $true

#Open the CSV file
$excel.Workbooks.Open($exportedFile)

You can try to open up a CSV file that was exported with the comma and the semicolon delimiter to see the difference between the two approaches by yourself.

Conclusion:

We already covered a section to understand working with CSV. We covered the cmdlets export-csv to while trying to export files as CSV. They are mostly used while reporting which can be directly viewed using excel. I have personally used Export-CSV and import-CSV while working with the azure platform which we will cover some other days in detail.