Excel "Save as CSV" delimiter incorrect

Welcome Forums General PowerShell Q&A Excel "Save as CSV" delimiter incorrect

This topic contains 7 replies, has 4 voices, and was last updated by

 
Participant
3 years, 5 months ago.

  • Author
    Posts
  • #33157

    Participant
    Topics: 5
    Replies: 10
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 58
    Replies: 89
    Points: 3
    Rank: Member

    _

  • #33159

    Participant
    Topics: 0
    Replies: 669
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 5
    Replies: 10
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 0
    Replies: 669
    Points: 0
    Rank: Member

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

  • #33167

    Participant
    Topics: 5
    Replies: 10
    Points: 0
    Rank: Member

    Hi Richard,

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

  • #33169

    Member
    Topics: 9
    Replies: 2322
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 5
    Replies: 10
    Points: 0
    Rank: Member

    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.

The topic ‘Excel "Save as CSV" delimiter incorrect’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort