Excel "Save as CSV" delimiter incorrect

This topic contains 7 replies, has 4 voices, and was last updated by Profile photo of Paps Sale Paps Sale 1 year, 7 months ago.

  • Author
    Posts
  • #33157
    Profile photo of Paps Sale
    Paps Sale
    Participant

    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)
        $wb.RefreshAll()
        if (Test-Path -Path $newFile) {
            
            Remove-Item $newFile -Force
        
        }
        $wb.SaveAs("$newFile",6)
        $wb.Close($true)  
           
    }
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
    
  • #33158
    Profile photo of Wei-Yen Tan
    Wei-Yen Tan
    Participant

    _

  • #33159
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    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
    Profile photo of Paps Sale
    Paps Sale
    Participant

    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:
    (Get-Culture).TextInfo.ListSeparator
    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
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

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

  • #33167
    Profile photo of Paps Sale
    Paps Sale
    Participant

    Hi Richard,

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

  • #33169
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    Profile photo of Paps Sale
    Paps Sale
    Participant

    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.