
close
close
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)
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.
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)
$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)
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)
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 from Jeff Hicks
advertisment
Petri Newsletters
Whether it’s Security or Cloud Computing, we have the know-how for you. Sign up for our newsletters here.
advertisment
More in PowerShell
Microsoft’s New PowerShell Crescendo Tool Facilitates Native Command-Line Wraps
Mar 21, 2022 | Rabia Noureen
Most popular on petri
Log in to save content to your profile.
Article saved!
Access saved content from your profile page. View Saved
Join The Conversation
Create a free account today to participate in forum conversations, comment on posts and more.
Copyright ©2019 BWW Media Group