jjamesjohnson jjamesjohnson -4 years ago 291
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:


Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download