Identifying Duplicate CSV Headers (Part 2)

When a CSV file contains duplicate column headers, it cannot be imported. In the previous tip we illustrated how you can detect duplicate column headers in a CSV file. Now here is an approach that auto-corrects duplicate entries.

First, you need a CSV file with duplicate column headers. On German systems, for example, you could create one like this:

Here is a new function called Import-CsvWithDuplicate that can deal with duplicate entries automatically:

functionImport-CsvWithDuplicate($Path,$Delimiter=',',$Encoding='UTF8')
{
# get the header line and all header items
$headerLine=Get-Content$Path|Select-Object-First1$headers=$headerLine.Split($Delimiter)
# check for duplicate header names, and if found, add an incremented
# number to it
$dupDict=@{}
$newHeaders=@(foreach($headerin$headers)
{
$incrementor=1$header=$header.Trim('"')
$newheader=$header# increment numbers until the new name is unique
while ($dupDict.ContainsKey($newheader) -eq$true)
{
$newheader="$header$incrementor"$incrementor++
}
$dupDict.Add($newheader,$header)
# return the new header, producing a string array
$newheader
})
# read the CSV without its own headers..
Get-Content-Path$Path-Encoding$Encoding|Select-Object-Skip1|# ..and replace headers with newly created list
ConvertFrom-CSV-Delimiter$Delimiter-Header$newHeaders
}