jjamesjohnson jjamesjohnson - 3 months ago 27
PowerShell Question

Splitting CSV file by two columns

Starting with a 500,000 line CSV, I need to split the files by day and hour (the second and third columns). I've tried the modify the group to include the hour and while I see the hour get added to my filename, I get no results in the exported file.

The foreach doing the work:

foreach ($group in $data | Group Day,hour) {
$data | Where-Object { $_.Day -and $_.Hour -eq $group.Name }
ConvertTo-Csv -NoTypeInformation |
foreach {$_.Replace('"','')} |
Out-File "$Path\Testfile_$($group.name -replace $regexA, '').csv"


Sample Data:

Bob,1/27/2012,8:00,Basic,Operations
Charlie,2/3/2012,9:00,Advanced,Production
Bill,3/7/2012,10:00,Advanced,Production

Answer

You could import the CSV, determine the output filename on the fly, and append each record to the matchning file:

Import-Csv 'C:\path\to\input.csv' | ForEach-Object {
  $filename = ('output_{0}_{1}.csv' -f $_.Day, $_.Hour) -replace '[/:]'
  $_ | Export-Csv "C:\path\to\$filename" -Append -NoType 
}

Note that Export-Csv -Append requires PowerShell v3 or newer.