Saving excel as csv with comma delimiter instead of semicolon

Question

I have a script which converts from xls to csv. It is very simple and just opens the xls with an Excel Object and saves it as csv:

cls Remove-Item *.csv $stringBuilder = New-Object System.Text.StringBuilder $objExcel = New-Object -ComObject Excel.Application $objExcel.Visible = $false $Tab = [char]9 $Pestana = 2
# Lee todos los excel de esta carpet $ListadoExcel = Get-ChildItem  -filter "*.xlsx" foreach ($Linea in  $ListadoExcel) {
    $Archivo = $Linea.FullName
    $ArchivoCorto = $Archivo.Replace('.xlsx','')
    "Procesando: "+$Archivo
    $WorkBook = $objExcel.Workbooks.Open($Archivo,$null,$True) #Solo lectura
    #Leemos la tercera pestaña
    $WorkSheet = $WorkBook.sheets.item($Pestana)
    $WorkSheet.SaveAs($ArchivoCorto+".csv", 23)
    #"Filas leidas: "+$Filas
    #$range = $WorkSheet.UsedRange
    #$WorkSheet.Range('B5').Text

    #$stream.WriteLine($WorkSheet.UsedRange.Cells.Item.Text)
    $WorkBook.Close($False) #Sin guardar }

$objExcel.Quit()

The weird thing is that the result csv is delimited with , when I want to be delimited with ;.

If I do (Get-Culture).TextInfo.ListSeparator I get ;.

I also have the same delimiter in Regional and language settings in Control Panel.

If I take the same Excel and do a save as manually the resultant csv is delimited with ";".

Have you tried using [Export-Csv](https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.utility/export-csv)? It allows for different delimiters? — rrirower, Jun 20 '17 at 13:32
@rrirower I did but I think I have to do Import-csv first. Import-csv .\myfile.csv -Delimiter ',' it doesn't give me the right content. — Brank Victoria, Jun 20 '17 at 13:43

David Anderson · Accepted Answer · 2017-06-20T14:47:44.903

4

You need to pass $True for the Local parameter of SaveAs for it to respect the Control Panel settings.

true saves files against the language of Excel (including control panel settings). false (default) saves files against the language of Visual Basic for Applications (VBA).

https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.saveas.aspx

In order to skip the optional parameters to this method, you can use [Type]::Missing as described in this answer.

edited Jun 20 '17 at 14:47

answered Jun 20 '17 at 13:46

David Anderson

8,306
2
27
27

I would like to try this, can you help me in use the SaveAs function without defined any other parameter (such as Password) except for nameFile, Format and Local? – Brank Victoria Jun 20 '17 at 13:58

score 0 · Answer 2 · answered Jun 20 '17 at 13:33

0

Try:

$WorkSheet.SaveAs($ArchivoCorto+".csv", 6)

Edit - explanation

23 is the xlCSVWindows enumeration. It sounds like you're after xlCSV.

answered Jun 20 '17 at 13:33

G42

9,791
2
19
34

Jeff Zeitlin · Answer 3 · 2017-06-20T13:40:51.397

0

~~Since you can't change the delimiter in Excel (you have to change it system-wide in the Regional and Language settings in the Control Panel),~~ The system list delimiter is irrelevant; you need to work around ~~that restriction~~ Excel's limitations to get a different delimiter for a specific file. Once you've saved the file as a "CSV" using your code above, you need to do the following:

$Worksheet = Import-CSV -delimiter ','
Export-CSV -InputObject $Worksheet -Path =path-to-csv-file= -Delimiter ';' -NoTypeInformation

edited Jun 20 '17 at 13:40

answered Jun 20 '17 at 13:34

Jeff Zeitlin

9,773
2
21
33

When I do Import-csv -delimiter ',' it doesn't print the correct content, it seems that it is printing the first word before "," – Brank Victoria Jun 20 '17 at 13:42
You'll need to provide more information about the content of your file, then. Edit your question to show some of the data in your file. Also, why are you printing anything? All you should need to do is read it in and write it out with the new delimiter, as I showed above. – Jeff Zeitlin Jun 20 '17 at 13:44

Saving excel as csv with comma delimiter instead of semicolon

3 Answers3