Split content from one .csv to multiple files based on content using powershell

Question

I've got a .csv file with two types of rows. The first one contains the header-information. It always starts with AB. The second type contains the content. This one always starts with CD. There can be multiple content-rows after each header-row (always at least one). They belong together until the next header-row (starting with AB again).

Example:

header1; header2; header3; header4; header5; header6; header7
AB; 12345; AB123456789; 10.03.2021; GT; BC987654321; EUR
CD; 456789; 22.24; Text; SW;
AB; 12345; AB123456789; 10.03.2021; GT; BC987654322; EUR
CD; 354345; 85.45; Text; SW;
CD; 123556; 94.63; Text; SW;
CD; 354564; 12.34; Text; SW;
CD; 135344; 32.23; Text; SW;
AB; 12345; AB123456789; 10.03.2021; GT; BC987654323; EUR
CD; 354564; 12.34; Text; SW;
CD; 852143; 34.97; Text; SW;

How Can I split this file into several .csv-files - one for each header-row (AB) - using PowerShell. My desired outcome would be

BC987654321.csv

header1; header2; header3; header4; header5; header6; header7
AB; 12345; AB123456789; 10.03.2021; GT; BC987654321; EUR
CD; 456789; 22.24; Text; SW;

BC987654322.csv

header1; header2; header3; header4; header5; header6; header7
AB; 12345; AB123456789; 10.03.2021; GT; BC987654322; EUR
CD; 354345; 85.45; Text; SW;
CD; 123556; 94.63; Text; SW;
CD; 354564; 12.34; Text; SW;
CD; 135344; 32.23; Text; SW;

and BC987654323.csv

header1; header2; header3; header4; header5; header6; header7
AB; 12345; AB123456789; 10.03.2021; GT; BC987654323; EUR
CD; 354564; 12.34; Text; SW;
CD; 852143; 34.97; Text; SW;

I am not used to PowerShell at all - so I'll appreciate a newb-friendly solution.

Thank you very much in advance.

Is this a real example of your CSV file? Where is the data and why do we only see headers? What is your desired output? — Theo
@Theo Those were not the headers but just randomly picked cell-names. I just edited the question so the example looks more like the actual file now. — Nerevar.de
@AbrahamZinala I am currently reading how PowerShell works in general as I've mentioned in the question, I got literally zero experience in using it yet. I've been able to add a header to the file at least (there is none in the provided file), so it might be easier to work with the collumns from now on. — Nerevar.de

Theo Theo · Accepted Answer · 2021-03-10T13:24:18

If I understand correctly, you want to split the csv on every row where 'header1' is equal to 'AB' and then use what is in that row under 'header6' for the output file name.

$path = 'D:\Test'
$fileIn = Join-Path -Path $path -ChildPath 'input.csv'
$fileOut = $null   # will get a value in the loop
$splitValue = 'AB' # the header1 value that decides to start a new file
$csv = Import-Csv -Path $fileIn -Delimiter ';'
# get an array of the column headers
$allHeaders = $csv[0].PsObject.Properties.Name
foreach ($item in $csv) {
    if ($item.header1 -eq $splitValue) { 
        # start a new file
        $fileOut = Join-Path -Path $path -ChildPath ('{0}.csv' -f $item.header6)
        # create the new csv file with the first row of data already in it
        $item | Select-Object $allHeaders | Export-Csv -Path $fileOut -Delimiter ';' -NoTypeInformation
    }
    else {
        # rows with header1 not 'AB' are added to that file
        if ([string]::IsNullOrEmpty($fileOut)) {
            Write-Warning "Could not find a starting row (header1 = '$splitValue') for the file"
        }
        else {
            $item | Select-Object $allHeaders | Export-Csv -Path $fileOut -Delimiter ';' -Append
        }
    }
}

Of course, change the paths to match your environment.

Output:

BC987654321.csv

"header1";"header2";"header3";"header4";"header5";"header6";"header7"
"AB";"12345";"AB123456789";"10.03.2021";"GT";"BC987654321";"EUR"
"CD";"456789";"22.24";"Text";"SW";"";

BC987654322.csv

"header1";"header2";"header3";"header4";"header5";"header6";"header7"
"AB";"12345";"AB123456789";"10.03.2021";"GT";"BC987654322";"EUR"
"CD";"354345";"85.45";"Text";"SW";"";
"CD";"123556";"94.63";"Text";"SW";"";
"CD";"354564";"12.34";"Text";"SW";"";
"CD";"135344";"32.23";"Text";"SW";"";

BC987654323.csv

"header1";"header2";"header3";"header4";"header5";"header6";"header7"
"AB";"12345";"AB123456789";"10.03.2021";"GT";"BC987654323";"EUR"
"CD";"354564";"12.34";"Text";"SW";"";
"CD";"852143";"34.97";"Text";"SW";;

Split content from one .csv to multiple files based on content using powershell

1 Answers