Splitting CSV file by two columns

Question

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

$data | Where-Object { $_.Day -and $_.Hour -eq $group.Name } -> $_.Group contains all the things in the group, no need to rescan all 500,000 things to try and make up the group again. $_.Day -and $_.Hour is not doing what you think, this is a boolean operator and returns $True which will never match the group name. — TessellatingHeckler
– TessellatingHeckler, Commented Aug 25, 2016 at 22:53

Ansgar Wiechers · Accepted Answer · 2016-08-25 22:35:02Z

3

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.

answered Aug 25, 2016 at 22:35

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jjamesjohnson Over a year ago

Thanks, I'll give this method a try when I can reboot to install PSv3

Ansgar Wiechers Over a year ago

On earlier versions you could use something like $_ | ConvertTo-Csv -NoType | Select-Object -Skip 1 | Add-Content. Less elegant (and you'll lose the header), but should work.

jjamesjohnson Over a year ago

Thanks I've marked this as the answer as it did exactly what I needed done.

Collectives™ on Stack Overflow

Splitting CSV file by two columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related