Prof. Powershell

CSV, Easy as ABC

XML was an easy one; how about CSV? PowerShell 2.0 has a cmdlet for that too.

• By Jeffery Hicks
• 08/11/2009

Last time I showed you how to create XML documents from PowerShell objects using ConvertTo-XML. PowerShell 2.0 includes a similar cmdlet for creating CSV objects. The Export-CSV cmdlet can still be used to create a CSV file. However what if you need to work with the raw CSV data or need a different delimiter? For these situations, ConvertTo-CSV might be your answer. Even so, it might take a little extra work to get the information you need.

Try this in a Windows 7 PowerShell session:

PS C:\> get-process | where {$_.workingset -gt 5MB} | convertto-csv

You'll see a lot of CSV data scroll across the screen. The ConvertTo-CSV cmdlet is converting all process object properties. You'll likely need to be more selective:

PS C:\> get-process | Select Name,*Set,ID,Handles,Path,*time | ConvertTo-CSV

If you want to work with the raw data, assign this expression to a variable. If you simply want to create a file then pipe the expression to Out-File:

PS C:\> get-process | Select Name,*Set,ID,Handles,Path,*time | ConvertTo-CSV | Out-File

This is equivalent to using Export-CSV. Like that cmdlet, ConvertTo-CSV has the -NoTypeInformation parameter if you want to have a traditional CSV file.

What I like about ConvertTo-CSV is that you can specify the delimiter. Sometimes you need data separated by a semicolon or a vertical bar. All you need to do is tell PowerShell what to use:

PS C:\> dir $env:temp -recurse | where {$_.getType() -match "File"} | sort Length |Select fullname,length,LastWriteTime | ConvertTo-CSV -Delimiter ";" -notypeinformation | Out-File c:\files\tempdata.csv

This creates a CSV file using a semicolon as the delimiter of files in my %TEMP% directory. To be fair, if all you need is a file, you can use Export-CSV in PowerShell v2 as it too supports the -delimiter parameter.

If you need a CSV formatted file, continue to use Export-CSV, perhaps with one of the new parameters. If you'd like to work with the CSV data outside of a file then use ConvertTo-CSV:

PS C:\> $converted=dir $env:temp -recurse | where {$_.getType() -match "File"} | sort Length |Select fullname,length,LastWriteTime| ConvertTo-CSV -Delimiter ";" -notypeinformation | foreach {$_.replace("C:\","X:\")}

The $converted variable now has the file information, but drive C:\ references have been replaced with drive X:\. I can pipe $converted to Out-File to save the results.

The bottom line is that PowerShell 2.0 offers more opportunities to work with the data you need.

Note: This information is based on pre-release software and is subject to change.