Import CSV Cleanup into PowerShell -- Microsoft Certified Professional Magazine Online

Import CSV Cleanup into PowerShell

Here's a quick way to import a CSV file without the need to heavily edit it for compatibility.

By Jeffery Hicks
06/24/2014

I love using Import-CSV because anything you import will be converted into an object. The source of your CSV file can come from anywhere. It does not have to be created in PowerShell. This is extremely useful because you can pipe directly to the cmdlets and take advantage of pipeline binding.

But in order for that to work, the column headings in your CSV file must match the parameter names of your cmdlet or function. Sometimes that doesn't always work out. But you can fix that when you import the file, without having to edit it. Here's a sample CSV for my demo.

PS C:\work> get-content .\dcdata.csv
A,B,C,D
chi-dc01,chicago,administrator,1234
chi-dc02,chicago,administrator,3465
nyc-dc01,nyc,chris,1535
nyc-dc01,nyc,administrator,8732
mia-dc04,miami,jeff,1577

When I import the CSV file, the first row is used as property names.

PS C:\work> import-csv .\dcdata.csv

A                        B                        C                        D
-                        -                       -                        -
chi-dc01                 chicago                  administrator            1234
chi-dc02                 chicago                  administrator            3465
nyc-dc01                 nyc                      chris                    1535
nyc-dc01                 nyc                      administrator            8732
mia-dc04                 miami                    jeff                     1577

Naturally, a property name like A or D isn't very practical. If you look at help for Import-CSV you'll see that it has a –Header parameter, which allows you to substitute your own header. Let's try it out:

PS C:\work> import-csv .\dcdata.csv -Header "Computername","Location","PrimaryUser","AssetID"

Computername             Location                 PrimaryUser              AssetID
------------             --------                 -----------              -------
A                        B                        C                        D
chi-dc01                 chicago                  administrator            1234
chi-dc02                 chicago                  administrator            3465
nyc-dc01                 nyc                      chris                    1535
nyc-dc01                 nyc                      administrator            8732
mia-dc04                 miami                    jeff                     1577

Well, that sort of worked. The original header is treated as another row of data which is not what I wanted. If you had read help closely you would have seen a comment to delete the header in the file first. But I want to leave the original file alone. So let's go to Plan B.

There is also a Convert-FromCSV cmdlet that can give me the same type of result:

PS C:\work> get-content .\dcdata.csv | ConvertFrom-Csv -Header "Computername","Location","PrimaryUser","AssetID"

Well that didn't do any better. But we're closer. All we have to do is read the contents of the text file and skip the first line, which we can do with Select-Object.

PS C:\work> get-content .\dcdata.csv | select -skip 1 | ConvertFrom-Csv -Header "Computername","Location","PrimaryUser","AssetID"

Computername             Location                 PrimaryUser              AssetID
------------             --------                 -----------              -------
chi-dc01                 chicago                  administrator            1234
chi-dc02                 chicago                  administrator            3465
nyc-dc01                 nyc                      chris                    1535
nyc-dc01                 nyc                      administrator            8732
mia-dc04                 miami                    jeff                     1577

Although you could also use the –Tail parameter and get all rows except the first.

PS C:\work> get-content .\dcdata.csv -tail 5 | ConvertFrom-Csv -Header "Computername","Location","PrimaryUser","AssetID"

In any event, now I have something that I can incorporate into a larger Pipelined expression:

PS C:\work> get-content .\dcdata.csv -tail 5 | ConvertFrom-Csv -Header "Computername","Location","PrimaryUser","AssetID" | where {$_.location -eq 'chicago'} | get-ciminstance win32_logicaldisk -filter "deviceid='c:'" | Select PSComputername,DeviceID,Size,FreeSpace

PSComputerName           DeviceID                 Size                     FreeSpace
--------------           --------                 ----                     ---------
chi-dc02                 C:                       12777943040              3106775040
chi-dc01                 C:                       15999168512              1074495488

The point of this week's lesson is that there are usually several ways to accomplish a task in PowerShell. You need to be ready to spend a little time reading help and testing things out.

Register! Top 5 Hybrid AD Management Mistakes and How to Avoid Them