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: empexportedProcesses.csv'
#Exporting as CSV - basic
Get-Process | Export-Csv $exportedFile
#Opening the file
psedit $exportedFile
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. Importing is very straightforward:
#Importing CSV file
$data = Import-Csv $exportedFile
#Showing content
$data | Out-GridView
#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: empexportedProcesses.csv -Delimiter ';'
#Importing the data
$data = Import-Csv C: empexportedProcesses.csv -Delimiter ';'
#Showing the data
$data | Out-GridView
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.
You can find it on GitHub, and in the PowerShell Gallery as well. It delivers strong automation mechanisms to generate tables and diagrams within Excel and to retrieve data from Excel sheets easily.