Search code examples
powershellcsvexport-csv

Using Powershell to add values to existing CSV

I have been battling this for way too long and I hope you can be of assistance.

I have a CSV for which I need to add some values, preferrably with Powershell. I need to add header row and one column with a fixed text value.

My CSV, before anything done to it, looks like this:

contact-email;contact-sms;contact-firstname
test@example.com;+3580000000;Mike

And I need it to look like this:

contact-email;contact-sms;contact-firstname;order-site
test@example.com;+3580000000;Mike;Finland

So the last column "order-site" needs to be added and every line in that column should have a value of "Finland".

So far I have written this Powershell script I got off a tutorial:

$file = Import-Csv E:\Raportit\SALES\SALES_TD_S01.csv -Delimiter "`t" -Encoding Default -Header "contact-email;contact-sms;contact-firstname"

foreach($c in $file) {

    Add-Member -Input $c -MemberType NoteProperty -Name "order-site" -Value "Finland"

}

$file | Export-Csv -Path "C:\Temp\test.csv" -Encoding Default -NoTypeInformation

But unfortunately, this makes the CSV look like this:

"contact-email;contact-sms;contact-firstname","order-site"
"test@example.com;+3580000000;Mike","Finland"

For the use case I have for this file, it need to look like the first should-look-like example, without double quotes and columns separated by semicolon (;). The double quotes are OK as long as the output looks like this:

"contact-email;contact-sms;contact-firstname;order-site"
"test@example.com;+3580000000;Mike;Finland"

I thank you guys so much in advance, I know this is probably a super simple task but I just cannot wrap my head around it to save my life.

Solution
    1. if the file HAS headers:
    Import-Csv -Path 'E:\Raportit\SALES\SALES_TD_S01.csv' -Delimiter ';' | 
    Select-Object *, @{Name = 'order-site'; Expression = {'Finland'}} |
    Export-Csv -Path "C:\Temp\test.csv" -Delimiter ';' -NoTypeInformation
    1. if the file DOES NOT HAVE headers:
    $headers = 'contact-email','contact-sms','contact-firstname'
Import-Csv -Path 'E:\Raportit\SALES\SALES_TD_S01.csv' -Delimiter ';' -Header $headers | 
    Select-Object *, @{Name = 'order-site'; Expression = {'Finland'}} |
    Export-Csv -Path "C:\Temp\test.csv" -Delimiter ';' -NoTypeInformation

    CSV Output:

    "contact-email";"contact-sms";"contact-firstname";"order-site"
"test@example.com";"+3580000000";"Mike";"Finland"