CODEWITHSUNDEEP
powershell
aby191
aby191

Reputation: 45

Split CSV file into small number of csv files based on value of a column

I am trying to split a csv file into smaller csv files, on the basis of the BusinessUnit which is a column in the csv file.

Until now I've tried:-

$fullpath = "C:\Batch\Output Folder\ErroreportforAllregions.csv"

$path = Split-Path $fullpath -parent
$data = Import-CSV  -Path $fullpath -Header BusinessUnit,Date,Time,PolicyNum


foreach ($group in $data | Group BusinessUnit){        
    $data | Where-Object {$_.species -eq $group.name} | 
        ConvertTo-Csv -NoTypeInformation | 
         foreach {$_.Replace('"','')}|
        Out-File "$path\$($group.name).csv"     
}

But in this rows(data) are not coming in small csv files. Please guide me as to where I am making the mistake.

My data in big csv file:-

BusinessUnit    serialno
Eastern Region   1
Ottawa           2
India            3

The output should be

Upvotes: 1

Views: 92

Answers (1)

Theo
Theo

Reputation: 61218

Supposing your big csv actually looks like this:

"BusinessUnit","Date","Time","PolicyNum","serialno"
"Eastern Region","2019-02-04","11:09:45","123","1"
"Eastern Region","2019-02-03","10:09:45","123","1"
"Ottawa","2019-02-04","11:09:45","123","1"
"India","2019-02-04","11:09:45","123","1"

Then this should do it:

$fullpath = "C:\Batch\Output Folder\ErroreportforAllregions.csv"
$path = Split-Path $fullpath -parent
$data = Import-CSV -Path $fullpath

$data | Group-Object -Property BusinessUnit | ForEach-Object {
    $file = Join-Path -Path $path -ChildPath ($_.Name + '.csv')
    $_.Group | Export-Csv -Path $file -NoTypeInformation
}

Using the data from above, it will create three csv files called Eastern Region.csv, Ottawa.csv and India.csv.

Eastern Region.csv

"BusinessUnit","Date","Time","PolicyNum","serialno"
"Eastern Region","2019-02-04","11:09:45","123","1"
"Eastern Region","2019-02-03","10:09:45","123","1"

Ottawa.csv

"BusinessUnit","Date","Time","PolicyNum","serialno"
"Ottawa","2019-02-04","11:09:45","123","1"

India.csv

"BusinessUnit","Date","Time","PolicyNum","serialno"
"India","2019-02-04","11:09:45","123","1"

Hope this helps

Upvotes: 2

Related Questions