Prof. Powershell

PowerShell to CSV

We all love the CSV format, so Powershell has a cmdlet that supports it.

• By Jeffery Hicks
• 10/08/2008

The comma separated value (CSV) format is probably one of the most widely used formats in administrative scripting. I'm betting that you've either saved data to a CSV file or parsed one for an admin process.

You'll be happy to know that PowerShell supports the CSV format in a few ways. First you can export any PowerShell object to a CSV file using the Export-CSV cmdlet. All you need to do is specify a filename:

PS C:\> get-service | export-csv services.csv

The CSV file can then be re-imported into any PowerShell session using Import-CSV which recreates the service objects:

PS C:\> import-csv c:\services.csv

You can work with these deserialized objects just as they were the original objects. You can sort, filter, select or whatever you want:

PS C:\> import-csv c:\temp\s.csv | where {$_.status -eq "running"} | Sort Displayname | Select Displayname,ServiceType

DisplayName                            ServiceType
-----------                            -----------
Acronis Scheduler2 Service             Win32OwnProcess, InteractiveProcess
Application Experience                 Win32ShareProcess
AVG8 WatchDog                          Win32OwnProcess
Background Intelligent Transfer Service Win32ShareProcess
Base Filtering Engine                  Win32ShareProcess
Bluetooth Support Service              Win32ShareProcess
CNG Key Isolation                      Win32ShareProcess
COM+ Event System                      Win32ShareProcess
Computer Browser                       Win32ShareProcess
Cryptographic Services                 Win32ShareProcess
DCOM Server Process Launcher           Win32ShareProcess
...

The PowerShell CSV cmdlets are designed with defaults that assume you'll be importing back into PowerShell. If you open an exported CSV file you'll see a line like this:

#TYPE System.ServiceProcess.ServiceController

PowerShell uses this line to help reconstruct the original objects. However, if you exported a CSV file with the intent of loading it somewhere else like Excel, this line breaks the import. You can omit it by using the –NoTypeInformation parameter:

PS C:\> get-service | Select Name,Displayname,Status,ServiceType | export-csv services.csv -NoTypeInformation

In this example I only want certain properties so I select them before exporting to the csv. By the way, the Export-CSV file does not support appending so any existing CSV file with the same name will be overwritten

You can import any CSV file as long as it has a header row. The first row will be used as properties. Each line in the CSV will be created as a custom object. For example, you might have a CSV file with new user information. You can import it using Import-CSV and pipe the "object" to ForEach-Object to do something such as creating a new user account with New-QADUser:

$file="C:\employees.csv"
Import-Csv $file | ForEach-Object {
$OU=('OU=' + $_.Department + ',OU=Employees,DC=mycompany,dc=local')
Write-Host Creating $_.samaccountname -ForegroundColor Green
New-QADUser -parentcontainer $OU `
-name ($_.FirstName+' '+$_.LastName) `
-samAccountName $_.samaccountname -Notes "Imported User Account" `
-firstname $_.FirstName -lastname $_.LastName `
-title $_.title -department $_.department -company "mycompany.com" `
-phonenumber $_.Telephone -userpassword "P@ssw0rd" `
-office $_.Office -userprincipalname ($_.samaccountname+'@mycompany.com') `
-displayname ($_.FirstName+' '+$_.LastName) `
-City ($_.City) `
-objectattributes @{"Comment"="created by PowerShell"} |
Enable-QADUser | Set-QADUser -UserMustChangePassword $True
}

The CSV format is fine for simple objects. For more robust object exporting you'll want a different format, which I'll discuss another time.