MFfn7

I had posted an article some months ago about querying a CSV data file to return an array of items by column name, rather than column index number.  For example, the CSV file may have a heading row with “City, State, “Last, First Name””, whereby I needed to use a function to handle different CSV files with different formats, and simply provide the column name as an input parameter.  That means no relying upon “$row.City” and so on.

Here’s an example CSV…

csvdata

I wanted (still want, actually) to get an array from the “Last, First Name” column.  Such as (“Jorgan, Hugh”, “Hunt, Mike”, “McCracken, Phill”), if you can pardon the adolescent humor embedded in the data structure (snicker).

The Wrong Way

It turns out, I had gone down a bad path (pardon the pun).

I was using the NoteProperty embedded within the CSV custom object (hash-ish) table.  Like this…

$csvdata = Import-Csv ".\csvtest.csv"
$cols = Get-Member -InputObject $csvdata[0] |
  ? {$_.MemberType -eq "NoteProperty"} }
    Select-Object -ExpandProperty Name
$cindex = [array]::IndexOf($cols, "Last, First Name")
foreach ($row in $csvdata) {
  $i = 0
  $v = $row.psobject.Properties.Value[$cindex]
}

What’s wrong with this? In a nutshell (or powernutshell) is that the table embedded within the member structure which correlate to “NoteProperty” items, is not sorted in the same way as the source CSV import data.  So if you pull the columns above using the Wrong method, the array of column names is returned in alphanumeric sorting as: (“City”, “Department”,”DeptCode”, “ID”, “Last, First Name”, “State”, “Zip”).  Meanwhile, if you grab $csvdata[0] you’ll notice it returns them in the order of Left-to-Right as found in the actual CSV file: (“ID”,”Department”,”Last, First Name”,”City”,”State”,”ZIP”,”DeptCode”)

The Right Way

After ingesting sufficient quantities of heated, liquefied, caffeinated substances, I realized I had take an entirely wrong path on this post.  So much so, that I decided a correction was warranted, primarily to head-off others who run across the previous post (don’t worry, I’ll post a redirect from there as well).

$csvdata = Import-Csv ".\csvtest.csv"
$csvdata | Select -ExpandProperty "Last, First Name"

And it’s obviously much, MUCH less coding.

Once again, proof that caffeine is good.

Advertisements

One thought on “CSV and PowerShell Column Extraction Infraction Redaction

Leave a Reply

Please log in using one of these methods to post your comment:

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