fix duplicate columns names in csv file

0
votes

Given a comma separated plain text file import-week10.csv file, I'd like to change the header, to fix a duplicate column name issue. The "corrected" header would be available in another file correct.csv (the only row there). File import-week10.csv would then be saved (overwritten) with this new header.

Example first row in the file: 

ID,name,surname,DOB,name,surname,DOB,amount

or

ID,"name","surname","DOB",name,surname,DOB,amount

should become, with unique column names: 

ID,"name main","surname main","DOB main","name joint","surname joint","DOB joint",amount

First question, how do I script this change with Powershell ?

Second question, I would like to compare and get a warning: if this first row has changed in import-week10.csv, from last week's file import-week09.csv. Assuming new columns will be added, or changed, every couple weeks.

Environment: WS 2012 R2, could update to latest PS version if need be.

1
powershellcsv
What have you tried so far, and with what results? – Bill_Stewart
Get-Content "import-week10.csv" | select -First 2 | Out-File "test.csv" Import-CSV -Header ID,"name main","surname main","DOB main","name joint","surname joint","DOB joint",amount test.csv – Razvan Zoitanu

1 Answers

1
votes

Here is the answer to your literal question but I'm not positive it is really what you want:

$correctHeader = (Get-Content .\correct.csv)[0]
$in = Get-Content  .\import-week10.csv
$in[0] = $correctHeader
$in | Set-Content .\correct.csv