Home

PowerShell

PowerShell Problem Solver: Importing CSV Files Revisited

Jeff Hicks

Jan 28, 2015

In my previous article, I guided you through the potential pitfalls of importing a CSV file in PowerShell. If everything in your CSV file can be treated as a string, then you shouldn’t have any issues. But it’s more than likely that you will have some properties that need to be treated as integers or dates.

As promised, there’s another technique, albeit a little advanced. This is a great technique for when you’re inserting the same type of CSV file repeatedly. Let’s go back to my testdata.csv file.

"Date","Name","Service","Key","Size","Test"
"11/18/2014 6:45:30 AM","Data_1","ALG","","25","false"
"11/18/2014 2:17:30 AM","Data_2","AppIDSvc","2","-30","false"
"11/17/2014 11:57:30 PM","Data_3","Appinfo","3","42","true"
"11/17/2014 11:21:30 AM","Data_4","AppMgmt","4","-7","true"
"11/17/2014 11:33:30 AM","Data_5","aspnet_state","","28","true"
"11/16/2014 8:51:30 AM","Data_6","AudioEndpointBuilder","6","35","false"
"11/16/2014 6:00:30 PM","Data_7","AudioSrv","7","12","false"
"11/16/2014 3:41:30 AM","Data_8","AxInstSV","8","","true"
"11/16/2014 11:39:30 AM","Data_9","BDESVC","9","32","true"
"11/16/2014 11:03:30 PM","Data_10","BFE","10","15","false"
"11/18/2014 4:03:30 AM","Data_11","BITS","11","28","true"
"11/15/2014 7:57:30 AM","Data_12","Browser","12","31","true"
"11/14/2014 12:23:30 PM","Data_13","bthserv","13","16","true"
"11/17/2014 10:49:30 PM","Data_14","CertPropSvc","14","-39","false"
"11/14/2014 5:33:30 AM","Data_15","cfWiMAXService","15","20","true"
"11/13/2014 12:13:30 AM","Data_16","clr_optimization_v2.0.50727_32","16","42","true"
"11/15/2014 10:54:30 PM","Data_17","clr_optimization_v2.0.50727_64","17","28","true"
"11/16/2014 11:57:30 PM","Data_18","clr_optimization_v4.0.30319_32","18","-45","true"
"11/14/2014 2:38:30 AM","Data_19","clr_optimization_v4.0.30319_64","19","24","true"
"11/11/2014 11:13:30 AM","Data_20","cmdAgent","20","44","true"
"11/16/2014 1:33:30 AM","Data_21","COMSysApp","21","24","true"
"11/17/2014 8:37:30 AM","Data_22","ConfigFree Gadget Service","22","41","true"
"11/11/2014 6:28:30 PM","Data_23","ConfigFree Service","23","33","true"
"11/13/2014 4:45:30 PM","Data_24","CryptSvc","24","47","true"
"11/9/2014 6:28:30 PM","Data_25","CscService","25","26","true"

Let’s say I get a new version of this file daily and want to do something with it in PowerShell. Reverting back to Import-CSV, I can see that everything is a string.

advertisment

$data = import-csv C:\scripts\testdata.csv

Using Import-CSV in Windows PowerShell. (Image Credit: Jeff Hicks)

But I need the properties to be treated like this:

[DateTime] Date
[int32] Key
[String] Name
[String] Service
[Int32] Size
[Boolean] Test

What I need to do is to create a custom type extension that defines these properties. Starting in PowerShell 3.0, this became very easy to do using the Update-TypeData cmdlet.

The Update-TypeData cmdlet in Windows PowerShell. (Image Credit: Jeff Hicks)

Normally you would need to create a special XML file with all of the type extension information. But now we can do it dynamically and on-the-fly. To begin with, I’ll need to define a name for my custom class.

$myClass = "My.TestData"

Right now, when I import test data everything is a PSCustomObject. I’ll show you how to change that before we are through.

I will add a new member, or property, to my custom class for each property I need to change. However, there is a problem. I can’t use the existing property value with a new name. That is to say a command like this won’t do what you think it will.

Update-TypeData -TypeName $myClass -MemberName "Date" -MemberType ScriptProperty -Value {$this.date –as [datetime])}

Instead, I have to define a new property name.

advertisment

Update-TypeData -TypeName $myClass -MemberName "TestDate" -MemberType ScriptProperty -Value {$this.Date -as [datetime]}

Because I need to invoke some methods, I will create a ScriptProperty. Use $this in the scriptblock instead of $_ to reference the current object being processed. I’ll repeat this for all of the other properties I want to customize.

Update-TypeData -TypeName $myClass -MemberName "TestKey" -MemberType ScriptProperty -Value {$this.key -as [Int32]} -force
Update-TypeData -TypeName $myClass -MemberName "TestSize" -MemberType ScriptProperty -Value {$this.Size -as [Int32]} -force
Update-TypeData -TypeName $myClass -MemberName "TestResult" -MemberType ScriptProperty -Value {$this.Test.toBoolean([System.Globalization.NumberFormatInfo]::CurrentInfo)} -force

Next, I am going to define a default set of display properties so that these new properties are used by default.

Update-TypeData -TypeName $myClass -DefaultDisplayPropertySet "TestDate","TestKey","Name","Service","TestSize","TestResult" -Force

Some of the properties I’m not changing so I’ll use the original name. Using Get-TypeData I can see my changes.

Using Get-TypeData with Windows PowerShell. (Image Credit: Jeff Hicks)

Finally, I need to import my data from the CSV file and assign it a new type.

$data = Import-Csv C:\scripts\testdata.csv | foreach {
$_.psobject.typenames.insert(0,"My.TestData")
$_
}

Piping to Get-Member, you can see the new properties.

The pipe to Get-Member shows us the new properties. (Image Credit: Jeff Hicks)

And because I defined a set of default property names, that is what I see.

As long as I reference my new properties, I can easily filter and sort because they are already formatted as the proper type.

Filtering and sorting the properties in Windows PowerShell. (Image Credit: Jeff Hicks)

There is another way to configure all of this, especially if the header in your CSV isn’t what you want to use anyway. Let’s remove the custom type and try again.

advertisment

Remove-TypeData my.testdata

My header looks like this: “Date”,”Name”,”Service”,”Key”,”Size”,”Test” but I can substitute in any header I want, although I need to read in CSV data that lacks a header. Instead of using Import-CSV, I’ll use ConvertFrom-CSV.

get-content C:\scripts\testdata.csv |
select -skip 1 |
convertfrom-csv -Header "A","B","C","D","E","F" |
select -first 3

Using ConvertFrom-CSV in Windows PowerShell. (Image Credit: Jeff Hicks)

I show this to you so that you’ll understand this revised code:

Update-TypeData -TypeName $myClass -MemberName "Date" -MemberType ScriptProperty -Value {$this.A -as [datetime]} -force
Update-TypeData -TypeName $myClass -MemberName "Name" -MemberType AliasProperty -Value B -force
Update-TypeData -TypeName $myClass -MemberName "Service" -MemberType AliasProperty -Value C -force
Update-TypeData -TypeName $myClass -MemberName "Key" -MemberType ScriptProperty -Value {$this.D -as [Int32]} -force
Update-TypeData -TypeName $myClass -MemberName "Size" -MemberType ScriptProperty -Value {$this.E -as [Int32]} -force
Update-TypeData -TypeName $myClass -MemberName "Test" -MemberType ScriptProperty -Value {$this.F.toBoolean([System.Globalization.NumberFormatInfo]::CurrentInfo)} -force
Update-TypeData -TypeName $myClass -DefaultDisplayPropertySet "Date","Name","Service","Key","Size","Test" -Force

I’ve essentially recreated my original CS header, but now the property values will be typed. Let’s re-import the data.

$data = get-content C:\scripts\testdata.csv | select -skip 1 |
 convertfrom-csv -Header "A","B","C","D","E","F"| foreach {
$_.psobject.typenames.insert(0,"My.TestData")
$_
 }

The “junk” property names are still part of the object.

But when it comes to working with the object, I can use my new properties.

I can use the properties when working with the object. (Image Credit: Jeff Hicks)

You only have to create the type definition once. You could put it in your PowerShell profile or make it part of the script or function you write to import the data. You can use –Force to override any existing property names and avoid errors. Or use Get-TypeData to test if your custom type has already been added.
I’ve given you several techniques for importing CSV data and getting the values properly typed. You’ll have to decide what makes the most sense for your management or automation task.