Excel "Save as CSV" delimiter incorrect

This topic contains 7 replies, has 4 voices, and was last updated by  Paps Sale 2 years, 7 months ago.

  • Author
  • #33157

    Paps Sale

    I have a problem with a script where Excel saves the file as CSV.
    The delimiter is always ",", when in fact in my windows system settings the delimiter is set to ";".
    When I normally save the file in Excel as CSV, it will have the delimiter as it should, which is ";", however in Powershell it doesn't seem to take that into account and always saves with "," delimiter.
    Is there a way to override it with the correct delimiter?

    Here is the code I run:

    $files = Get-ChildItem -Path "D:\Product Feeds\" *.xlsx -Recurse
    $xl = New-Object -ComObject Excel.Application
    foreach ($file in $files) {
        $newFile = "$($file.DirectoryName)\$($file.BaseName).csv"
        $wb = $xl.Workbooks.Open($file.FullName)
        if (Test-Path -Path $newFile) {
            Remove-Item $newFile -Force
  • #33158

    Wei-Yen Tan


  • #33159

    Richard Siddaway

    You're using file format 6 which is generic CSV
    have you tried format 23 which is Windows CSV. That may pick up your default Windows settings
    see https://msdn.microsoft.com/EN-US/Library/ff198017.aspx for file format info

  • #33160

    Paps Sale

    Hi Richard,

    I tried setting the format to 23 and still didn't change anything.

    I actually ran the following code to get the currently used list separator info:
    Which strikes me as odd, is that Powershell claims the list separator to be ",", while in Windows region settings I've set it to ";".

    I'm using Windows Server 2012 R2 btw.

  • #33165

    Richard Siddaway

    Just out of curiosity do you get the same result if you use Get-UICulture instead of Get-Culture?

  • #33167

    Paps Sale

    Hi Richard,

    Yes, I still get the same result ("," delimiter).

  • #33169

    Dave Wyatt

    I can't reproduce this. Both the Get-Culture command and Excel are behaving fine on my system, using your code as-is. The only thing I've noticed is that if I change those settings in the control panel, they don't take effect until the next time I start PowerShell (or Excel).

  • #33170

    Paps Sale

    Hi Dave,

    I finally resolved the issue and it was quite simple: restart the system after applying list separator changes in Windows region settings :).

    At first I preferred not to restart the server, as there were other important background services running, but I was left with no other choice. Happy it helped.

You must be logged in to reply to this topic.