
close
close
Chance to win $250 in Petri 2023 Audience Survey
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.
$data = import-csv C:\scripts\testdata.csv
Using Import-CSV in Windows PowerShell. (Image Credit: Jeff Hicks)
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)
$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.
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)
$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)
Filtering and sorting the properties in Windows PowerShell. (Image Credit: Jeff Hicks)
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)
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)
More in PowerShell
Most popular on petri