Prof. Powershell

The Sky's Delimit

The -Delimiter parameter of the Export-CSV cmdlet is what makes working with CSV files in PowerShell so, well, limitless.

• By Jeffery Hicks
• 03/01/2011

Without a doubt the CSV file is the workhorse of Windows administration. We open these files in Microsoft Excel, import into our SQL databases or simply use as a storage mechanism for later use within PowerShell. Here's a typical command you might run to create a CSV file from within PowerShell:

PS C:\> dir c:\scripts\*.ps1 | select fullname,name,extension,*time | >> export-csv -Path c:\work\scripts.csv

With this command I've taken all PowerShell scripts and "saved" the objects to a CSV file using the Export-CSV cmdlet. I've elected to only save a subset of object properties using Select-Object. If I open this file in Notepad, I'll see all the elements separated by a comma. But what if you prefer a different delimiter? Perhaps you need to use the CSV file in an application that is expecting a semi-colon as the delimiter.

The Export-CSV cmdlet includes a parameter called -Delimiter, which accepts any character as a delimiter. The comma is the default, so let's re-run the command with the new delimiter:

PS C:\> dir c:\scripts\*.ps1 | select fullname,name,extension,*time | >> export-csv -Path c:\work\scripts.csv -Delimiter ";" -NoTypeInformation

In addition to specifying a delimiter, I also specified that the export omit the PowerShell type definition line, since I don't need it in my external application. Although I can still import the CSV file with Import-CSV, as long as I specify the delimiter since it is no longer the default comma:

PS C:\> $data=import-csv c:\work\scripts.csv -Delimiter ";"
PS C:\> $data[0]

FullName       : C:\scripts\13ScriptBlocks.ps1
Name           : 13ScriptBlocks.ps1
Extension      : .ps1
CreationTime   : 8/14/2010 9:15:22 AM
LastAccessTime : 8/14/2010 9:15:22 AM
LastWriteTime  : 8/13/2010 10:41:16 AM

The ConvertTo-CSV cmdlet also accepts a custom delimiter:

PS C:\> $s=get-service | select *Name,Status | convertto-csv
-Delimiter "|"
PS C:\> $s[0..3]
#TYPE Selected.System.ServiceProcess.ServiceController
"Name"|"DisplayName"|"MachineName"|"ServiceName"|"Status"
"AeLookupSvc"|"Application Experience"|"."|"AeLookupSvc"|"Stopped"
"ALG"|"Application Layer Gateway Service"|"."|"ALG"|"Stopped"

You can only use a single character as a delimiter. One potentially tricky delimiter is the tab character, but that is easily accommodated using the 't special character:

PS C:\> $s=get-service | select *Name,Status | convertto-csv -Delimiter 't

Again, you can specify a custom delimiter for any cmdlet that supports the parameter:

PS C:\> get-help * -Parameter delimiter

Name            Category Synopsis
----            -------- --------
Export-CSV      Cmdlet   Converts Microsoft .NET Framework
                         objects into a series...
Import-CSV      Cmdlet   Converts object properties in a
                         comma-separated value ...
ConvertTo-CSV   Cmdlet   Converts Microsoft .NET Framework
                         objects into a series...
ConvertFrom-CSV Cmdlet   Converts object properties in
                         comma-separated value (C...

Now you know delimits you can go!