Saving excel as csv with semicolon instead of semicolon

Question

I have a script that converts from xls to csv. It is very simple and simple to open xls with an Excel object and save 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 strange thing is that the result of csv is divided by ,when I want to be shared with ;.

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

I also have the same separator in the regional and language settings on the control panel.

If I take the same Excel and save manually, the resulting csv will be split into ";".

Answer 1

$True Local SaveAs, .

true Excel ( ). false ( ) Visual Basic (VBA).

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

, [Type]::Missing, .

Answer 2

Try:

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

-

23 xlCSVWindows. , xlCSV.

Answer 3

Since you cannot change the separator in Excel (you must change it in the system-wide area in the region and language settings in the control panel), the System List Separator does not matter; you need to get around the excel restriction to get a different delimiter for a specific file. After you save the file as "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