Excel "Save as CSV" delimiter incorrect

PowerShell Help
1

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)
2

_

3

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

4

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.

5

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

6

Hi Richard,

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

7

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).

8

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.