PowerShell: Read text, regex sort, write output to file and formatting

Question

I am a Powershell novice and have run into a challenge in reading, sorting, and outputting a csv file. The input csv has no headers, the data is as follows:

05/25/2010,18:48:33,Stop,a1usak,10.128.212.212
05/25/2010,18:48:36,Start,q2uhal,10.136.198.231
05/25/2010,18:48:09,Stop,s0upxb,10.136.198.231

I use the following piping construct to read the file, sort and output to a file:

(Get-Content d:\vpnData\u62gvpn2.csv) | %{,[regex]::Split($, ",")} | sort @{Expression={$[3]}},@{Expression={$_[1]}} | out-file d:\vpnData\u62gvpn3.csv

The new file is written with the following format:

05/25/2010
07:41:57
Stop
a0uaar
10.128.196.160
05/25/2010
12:24:24
Start
a0uaar
10.136.199.51
05/25/2010
20:00:56
Stop
a0uaar
10.136.199.51

What I would like to see in the output file is a similar format to the original input file with comma dilimiters:

05/25/2010,07:41:57,Stop,a0uaar,10.128.196.160
05/25/2010,12:24:24,Start,a0uaar,10.136.199.51
05/25/2010,20:00:56,Stop,a0uaar,10.136.199.51

But I can't quite seem to get there. I'm almost of the mind that I'll have to write another segment to read the newly produced file and reset its contents to the preferred format for further processing.

Thoughts?

Answer 1

So you want to sort on the fourth and second columns, then write out a csv file?

You can use import-csv to suck the file into memory, specifying the column names with the -header argument. The export-csv command, however, will write a header row out to the destination file and wrap the values in double-quotes, which you probably don't want.

This works, though:

import-csv -header d,t,s,n,a test.csv |
    sort n,t | 
    %{write-output ($_.d + "," + $_.t + "," + $_.s + "," + $_.n + "," + $_.a) }

(I've wrapped it onto multiple lines for readability.)

If you redirect the output of that back to a file, it should do what you want.

Answer 2

You can also use the ConvertFrom-CSV in a similar way

ConvertFrom-Csv -Header date, time, status,user,ip  @"
05/25/2010,18:48:33,Stop,a1usak,10.128.212.212
05/25/2010,18:48:36,Start,q2uhal,10.136.198.231
05/25/2010,18:48:09,Stop,s0upxb,10.136.198.231
"@