Adding text to existing csv file's rows using PowerShell

Question

I'd like to add a the text ;12345 in a csv file to every row that contains DE123456789 in the column VAT Number.

Example:

header1; header2; header3; header4; header5; VAT Number; header7; Kundennummer
AB; 12345; AB123456789; 10.03.2021; GT; DE123456789; EUR
CD; 456789; 22.24; Text; SW;
AB; 12345; AB123456789; 10.03.2021; GT; DE123456789; EUR

My desired saved file would be

header1; header2; header3; header4; header5; VAT Number; header7; Kundennummer
AB; 12345; AB123456789; 10.03.2021; GT; DE123456789; EUR; 12345
CD; 456789; 22.24; Text; SW;
AB; 12345; AB123456789; 10.03.2021; GT; DE123456789; EUR; 12345

My attempt was like this:

$File = 'S:\Test\test.csv'
$Check = (Import-Csv $File)."VAT Number" | ForEach-Object {
    if ($Check -Contains "DE123456789") {
        Select-Object @{Name='Kundennummer';Expression={'12345'}} | 
        Export-Csv $File -NoTypeInformation
    }
}

but there's nothing happening to the file at all.

I've found this code in a forum where a similar question has been asked. There was no feedback though.

Answer 1

Ok, since you have provided enough headers to the file, this could work for you:

$csv = Import-Csv -Path 'D:\Test\TheInputFile.csv' -Delimiter ';'
# get an array of the column headers
$allHeaders = $csv[0].PsObject.Properties.Name
$data = foreach ($item in $csv) {
    if ($item.'VAT Number' -eq 'DE123456789') { $item.Kundennummer = 12345 }
    # output a new object making sure all fields are present (allbeit some will remain empty)
    $item | Select-Object $allHeaders
}

$data | Export-Csv -Path 'D:\Test\TheNewFile.csv' -Delimiter ';' -NoTypeInformation