Working with CSV Files

For this week’s tip we will review the use of CSV’s and PowerShell. While it is almost always better to not use CSV files for output or object manipulations, there are other uses of CSVs that can come in handy when working with PowerShell.

CSV Operations

Exporting a CSV

One csv operation we can perform is exporting to a CSV file. This file could be used for reporting purposes, historical data, for future conversion to HTML or even for future processing in this script or another script. Exporting to a CSV file can be as simple as this one liner:
[sourcecode language=”powershell”]
$Results | Export-CSV c:\reporting\results.csv
[/sourcecode]
And can be as complex as specifying Append or NoClobber, choosing an encoding type, settings delimiter and more like this:
[sourcecode language=”powershell”]
$Results | Export-CSV -NoClobber -Delimiter "|" -NoInformation c:\reporting\results.csv
[/sourcecode]
It is important to note, that the parameters above do serve a purpose and could be key. Here is quick explanation of these options:

NoClobber – this ensures that when exporting to a CSV file, we do not overwrite and existing CSV file that is located at the exported location.
Delimiter – this can make it easier to work with especially if there are fields with standard delimiters like ‘,’
NoType – this is used to remove a line that is inserted into a file that contains information on the CSV file.

Importing a CSV

PowerShell also has the option to import CSV data to be used by PowerShell cmdlets and scripts. Similar to the Export cmdlet, there are parameters and switches that can be of use when importing a CSV file.

Delimiter – this can make it easier to work with especially if there are fields with standard delimiters like ‘,’
Encoding – The encoding method that was used to create the original CSV file
Header – To create an alternate set of column headers to replace the ones in the CSV file.

Examples

[sourcecode language=”powershell”]
$MailboxData = Import-CSV c:\reports\mailboxdata.csv
$UsersToImport = Import-CSV c:\reports\users.csv -Delimiter "|"
[/sourcecode]
Once these files are now stored in variables, PowerShell could reference the variables for processing. Some quick examples:
[sourcecode language=”powershell”]
Foreach ($Line in $MailboxData) {
<Insert Code here>
}
$UsersToImport | ConvertTo-HTML > UserDataTable
[/sourcecode]
Sample Usage of CSV files

CSV files can be used for many purposes. They are a convenient vehicle for storing data for the current script or even future script references and manipulation. CSV files can also be used for reporting and general information gathering. Handled properly, data stored in a CSV could be used for historical purposes as well.

Cleaning up CSV files

Refer to the previous PowerShell tip of the Week that can be found here:

https://www.practicalpowershell.com/blog/powershell-cleanup-tips

More examples of how to use CSV files as well as how to use PowerShell for output files can be found the books written and sold on this site. Thanks again for reading our blog.