Dealing with duplicate headers in .csv files in Powershell

Question

I'm trying to use a PS script to try to automate part of a process where an export from one department's system can be imported into another departments system. I need to take rows with multiple ";" separated email address and split them into separate rows with 1 address each, while preserving the rest of the data. I have a solution for this that is mostly working, which relies on Import-CSV.

The problem I'm having is that The export I'm getting has multiple rows with the same column header. This causes Powershell to give me the error "The Member "end" is already present." (where "end" is the header on more than one column).

This happens just running "import-csv ./addresses.csv" when there are duplicate column headers, so I'm running afoul of the import-csv command itself I think, verses anything odd I may be doing with the script.

editing the csv to change one of the column names fixes this, but that requirement makes it difficult to automate the process.

Is there a way with powershell to work with a CSV with duplicate column names? I'd rather avoid specifying column names besides the one containing the e-mail addresses, because they seem to periodically change. It would be best if I could preserve the column names they are using.

Marc Kellerman · Accepted Answer · 2016-02-22 19:21:19Z

I know this is an old post, but figured someone else might be looking for a 'better solution'.

I needed to dynamically correct the headers that where duplicate. And not knowing the fields in advance of whatever file i was going to import caused some significant challenges.

Here is what i did:

# Use System.IO.StreamReader to get the first line. Much faster than Get-Content.
$StreamReader = New-Object System.IO.StreamReader -Arg $csvTemp

# Parse the first line with whatever delimiter you expect. Trim + Remove Empty columns.
# Comment out the last part if you want to generate headers for those who are empty.
[array]$Headers = $StreamReader.ReadLine() -Split "," | % { "$_".Trim() } | ? { $_ }

# Close the StreamReader, as the file will stay locked.
$StreamReader.Close()

# For each Header column
For ($i=0; $i -lt $Headers.Count; $i++) {

    if ($i -eq 0) { Continue } #Skip first column.

    # If in any previous column, give it a generic header name
    if ($Headers[0..($i-1)] -contains $Headers[$i]) {
        $Headers[$i] = "Header$i"
    }
}

# Import CSV with the new headers 
Import-Csv $csvTemp -Header $Headers

nimizen · Accepted Answer · 2013-09-17 22:47:39Z

1

If you know what your column data is going to be ahead of time then just specify your own header names, this will ignore the header row in the file.

Import-CSV -Header header1, header2, header3 addresses.csv

answered Sep 17, 2013 at 22:47

nimizen

3,4292 gold badges26 silver badges35 bronze badges

1 Comment

DJDoubleDave Over a year ago

Won't that still leave me with an output file with different headers than the input file? I'm hoping to leave the headers intact if at all possible.

Collectives™ on Stack Overflow

Dealing with duplicate headers in .csv files in Powershell

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related