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.

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!

About the Author

Jeffery Hicks is an IT veteran with over 25 years of experience, much of it spent as an IT infrastructure consultant specializing in Microsoft server technologies with an emphasis in automation and efficiency. He is a multi-year recipient of the Microsoft MVP Award in Windows PowerShell. He works today as an independent author, trainer and consultant. Jeff has written for numerous online sites and print publications, is a contributing editor at Petri.com, and a frequent speaker at technology conferences and user groups.

comments powered by Disqus

SharePoint Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.