Export Excel sheet as CSV, with regional settings

Question

I want to export an excel sheet as CSV, with powershell. The issue I have is that the regional settings are not respected. They say to use semicolon as delimiter, but comma is used instead. If I manually Save As in Excel, I get the correct delimiter.

I tried using $Local:true but it didn't seem to help:

$Excel = New-Object -ComObject Excel.Application
$wb = $Excel.Workbooks.Open($excelFile, $Local:True)

$ws = $wb.Worksheets("Issue_Data")

$ws.SaveAs("$myDir\" + $File + ".csv", 62, $Local:True)

What am I doing wrong here? How to get this script to respect regional settings?

Answer 1

Working with Excel COM objects in PowerShell takes some doing sometimes..
When using a method that takes multiple parameters of which you want to set just some, you need to also set the parameters in-between, because the method's parameters are used as positional parameters (not named unfortunately as in VBA)

Try this:

$excelFile  = 'D:\Test\test.xlsx'
$outputFile = 'D:\Test\Output.csv'

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false

$wb = $Excel.Workbooks.Open($excelFile)
$ws = $wb.Worksheets("Issue_Data")
# activate the worksheet or you'll risk saving a different sheet !
$ws.Activate()

# use [Type]::Missing for parameters that should remain at their default values
$useDefault = [Type]::Missing

# do the call, and specify all parameters, even the ones you will leave at their default values
# in this case, the 'Local' parameter is the last in line and here set to $true
# see https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.saveas
$ws.SaveAs($outputFile,62,$useDefault,$useDefault,$false,$false,$false,$useDefault,$useDefault,$true)            

$Excel.Quit()
# clean up the com objects from memory
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ws)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Note:

XlFileFormat xlCSVUTF8 (62) unfortunately is not available for Excel versions up to and including version 2016.
For creating UTF8 csv files on those versions, see this answer

Answer 2

If the $ws variable is already well formed, I suspect you can simplify it by simply exporting it as a CSV and setting the delimiter:

$ws | Export-Csv -NoTypeInformation -Delimiter ";" -Path "X\Your\Path\Here.csv"

Also, while working with the Com objects is noble, and used to be necessary, have you taken a look at the very useful ImportExcel? Creating and manipulating Excel files with ImportExcel is much easier than using the Com objects!