Prof. Powershell

CSV, Easy as ABC

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

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.

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
Most   Popular