Identifying CSV Delimiter

When importing a CSV file with Import-Csv, you need to specify a delimiter, and if you pick the wrong one, import obviously fails. You need to know the delimiter a CSV file is using.

Here is a simple approach that tells you the delimiter for a given CSV file:

function Get-CsvDelimiter($Path)
{
    # get the header line
    $headerLine = Get-Content $Path | Select-Object -First 1
    
    # examine header line per character
    $headerline.ToCharArray() | 
      # find all non-alphanumeric characters
      Where-Object { $_ -notlike '[a-z0-9äöüß"()]' } |
      # find the one that occurs most often
      Group-Object -NoElement |
      Sort-Object -Descending -Property Count |
      # return it
      Select-Object -First 1 -ExpandProperty Name
}

And here is a test:

 
PS> Get-Date | Export-Csv -Path $env:temp\test.csv -NoTypeInformation 

PS> Get-CsvDelimiter -Path $env:temp\test.csv
,

PS> Get-Date | Export-Csv -Path $env:temp\test.csv -NoTypeInformation -UseCulture

PS> Get-CsvDelimiter -Path $env:temp\test.csv
;

PS> Get-Date | Export-Csv -Path $env:temp\test.csv -NoTypeInformation -Delimiter '#'

PS> Get-CsvDelimiter -Path $env:temp\test.csv
#
 

Twitter This Tip! ReTweet this Tip!