Prof. Powershell

PowerShell to CSV

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

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.

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.