Typecasting imported CSV data

When you do an import of .csv data in powershell the all of the properties will normally be of type [string]. Sometimes this is fine, other times, not so much.

Creating a test file:

get-process | select Name,ID,WorkingSet,CPU | where-object {$_.cpu} | export-csv test.csv -notype

Now we have a test file (test.csv) with 4 columns. One is string data, two are interger, and one is floating point, but import-csv will create them as noteproperties, with a type of [string]. This can cause unexpected results when you try things like sort operations on those properties.

If we import that csv, pipe it out to grid-view, and try sorting on the ID, WorkingSet, or CPU columns, the problem becomes apparent:

import-csv test.csv | ogv

How easy is it to fix?

Here’s mine (so far):

filter set-type {
param([hashtable]$type_hash)
foreach ($key in $($type_hash.keys)){
$_.$key = $($_.$key -as $type_hash[$key])
}
$_
}

Then:

import-csv test.csv | set-type -type_hash @{id=[int];workingset=[int];cpu=[float]} | ogv

Now the data sorts correctly on those columns.

Advertisements

One response to “Typecasting imported CSV data

  1. Pingback: Opticon OPN2001 – CSV file data manipulation using PowerShell « Ing. Lele's Blog – HeadQuarter

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s