1

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?

2 Answers 2

0

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

0

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!

2
  • Thanks, looks interesting. My search machine didn't present me with that so far.
    – kutschkem
    Jul 26 at 5:58
  • Hopefully that gets you where you need to be. If you are still having issues please update your question or add a comment. If either answer resolved the issue, please accept it as the answer. Thx!
    – DBADon
    Jul 26 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.