Using Types with Imported CSV Data in PowerShell

The Import-CSV cmdlet in PowerShell is incredibly useful. You can take any CSV file and pump objects to the pipeline. The cmdlet uses the CSV header as properties for the custom object.


PS S:\> import-csv .\testdata.csv

Date : 1/18/2012 2:17:30 AM
Name : Data_2
Service : AppIDSvc
Key : 2
Size : -30
...


But there is a downside: all of the properties are strings.


PS S:\> import-csv .\testdata.csv | get-member

Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Date NoteProperty System.String Date=1/18/2012 6:45:30 AM
Key NoteProperty System.String Key=1
Name NoteProperty System.String Name=Data_1
Service NoteProperty System.String Service=ALG
Size NoteProperty System.String Size=25


The means some tasks such sorting or filtering will fail. But there are ways to get around this limitation. One way is to use an expression to cast a property to a different type. For example, I want to sort my test data on the Date property, but it needs to be a [DateTime] object to sort properly. Here's how:


PS S:\> import-csv testdata.csv | sort @{expression={$_.date -as [datetime]}} | Select Date,Name,Size

Date Name Size
---- ---- ----
1/9/2012 6:28:30 PM Data_25 26
1/11/2012 11:13:30 AM Data_20 44
1/11/2012 6:28:30 PM Data_23 33
1/13/2012 12:13:30 AM Data_16 42
1/13/2012 4:45:30 PM Data_24 47
...


My output object properties are all still strings. All I did was cast the Date property in the Sort expression. Here's an example using filtering.


PS S:\> import-csv testdata.csv | where {($_.date -as [datetime]) -le ("1/12/2012" -as [datetime])} | Select Date,Name,Size

Date Name Size
---- ---- ----
1/11/2012 11:13:30 AM Data_20 44
1/11/2012 6:28:30 PM Data_23 33
1/9/2012 6:28:30 PM Data_25 26


These examples are only producing results. More likely I want to import the CSV file as typed objects. Assuming you know in advance the property names and what types you want to use, here's how you could achieve this.


PS S:\> $data=import-csv testdata.csv | Select @{Name="Date";Expression={[datetime]$_.Date}}, Name,Service,@{Name="Key";Expression={[int32]$_.Key}},@{Name="Size";Expression={[int32]$_.Size}}


I imported my CSV file and piped it to Select-Object, using hash tables to redefine the properties with appropriate types. Import-CSV writes a PSCustomObject to the pipeline anyway so using Select-Object has no effect other than giving me typed properties.


PS S:\> $data | get-member

Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Date NoteProperty System.DateTime Date=1/18/2012 6:45:30 AM
Key NoteProperty System.Int32 Key=1
Name NoteProperty System.String Name=Data_1
Service NoteProperty System.String Service=ALG
Size NoteProperty System.Int32 Size=25


Now I can use $data objects anyway I want.


PS S:\> $data | where {$_.size -ge 40 -AND $_.key -le 10}

Date : 1/17/2012 11:57:30 PM
Name : Data_3
Service : Appinfo
Key : 3
Size : 42


I'm working on something that takes this idea to the next level but it isn't quite ready for prime time. But I hope this will help manage imported objects a bit more efficiently and let you really take advantage of the PowerShell pipeline.

---

---