CODEWITHSUNDEEP
powershellcsv
Jérôme Verstrynge
Jérôme Verstrynge

Reputation: 59634

How to split a CSV according to a column's value in PowerShell?

Say I have a input.CSV looking like this

234321, AAA, value1
7751, BBB, value2
32123, AAA, value2
34553, CCC, value4
65433, BBB, value3
43664, AAA, value3

I would like to split it into multiple CSVs according to the second column's values. So, a AAA.csv file, a BBB.csv file, a CCC.csv file, etc.

I don't know all the 2nd column's values in advance, but they are limited to at most 100. And, input.CSV contains at most 100 000 lines.

How should I proceed?

Upvotes: 2

Views: 131

Answers (2)

BeMayer
BeMayer

Reputation: 400

Would this work ?

$test = @"
col1,col2,col3
234321, AAA, value1
7751, BBB, value2
32123, AAA, value2
34553, CCC, value4
65433, BBB, value3
43664, AAA, value3
"@
$test | Out-File input.csv

$csv = Import-Csv input.csv

$listofcol2values = @()
$listofcol2values = $csv | Select -ExpandProperty col2 -Unique

foreach ($value in $listofcol2values)
{$csv | ?{$_.col2 -eq $value} | Export-Csv ($value + ".csv")}

EDIT:

The solution suggested by iRon is indeed more appropriate:

Import-Csv input.csv | % {$_ | Export-Csv ($_.col2 + ".csv") -Append}

Upvotes: 1

iRon
iRon

Reputation: 23830

As your working with a large input file, I recommend you to use the PowerShell streaming capabilities for your input data so that it doesn't fill up all your memory.
To do this you should avoid assigning your input data to a variable (like: $csv = Import-Csv input.csv) or surround it by brackets (like: (Import-Csv input.csv) | ...).

Import-Csv input.csv | ForEach-Object {$_ | Export-Csv ($_.col2 + ".csv") -Append}

Upvotes: 2

Related Questions