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.

You do know that your CSV file misses a column in the CD line don't you?. Also, your question is about adding a new column with no header to the file.. — Theo
– Theo, Commented Mar 10, 2021 at 12:41
@Theo You're right regarding the column name. As I am preparing this file right now, I've added an additional column to the file (and edited it to the qeustion just now). The rows starting with CD are in fact shorter. They have less columns. I know it's weird - it's just how the provided files and data are. That's directly connected to my other question from 1 hour ago which is also not cleared yet. — Nerevar.de
– Nerevar.de, Commented Mar 10, 2021 at 13:03

Theo · Accepted Answer · 2021-03-10 13:06:37Z

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

answered Mar 10, 2021 at 13:06

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

2 Comments

Nerevar.de Over a year ago

Works like a charm. Thank you very much. Now there's just the split left that I mentioned in my other question.

Theo Over a year ago

@Nerevar.de Have a look, I just posted an answer on that one to ;)

Collectives™ on Stack Overflow

Adding text to existing csv file's rows using PowerShell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related