none
Powershell Export From Excel to CSV having ; delimiter RRS feed

  • Question

  • I have a powershell script that I need to have ; as the delimiter.  While it says this is configurable within the OS I have not found it to work.

    I have a script that loops through a directory tree converting from Excel to CSV.  This works like a charm but so far the ability to save as while using a different delimiter I have read about but I have not been able to do.

    Wednesday, May 15, 2013 10:26 AM

Answers

  • Here is a much easier way to do this:

    $oleDbConn = New-Object System.Data.OleDb.OleDbConnection
    $oleDbCmd = New-Object System.Data.OleDb.OleDbCommand
    $oleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
    $dataTable = New-Object System.Data.DataTable
    $oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\projects\dss\pse&g.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"
    $oleDbConn.Open()
    $oleDbCmd.Connection = $OleDbConn
    $oleDbCmd.commandtext = “Select * from [Sheet1$]”
    $oleDbAdapter.SelectCommand = $OleDbCmd
    $ret=$oleDbAdapter.Fill($dataTable)
    Write-Host	"Rows returned:$ret" -ForegroundColor green
    $dataTable | Export-Csv file.csv -Delimiter ';'
    $oleDbConn.Close()

    That is for Excel 2007.  If you have a differnt version teh Provider will be different.

    2007 - 12
    2010 - 14
    2013 - 15


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, May 15, 2013 2:09 PM
    • Marked as answer by Bill_StewartModerator Thursday, August 15, 2013 9:29 PM
    Wednesday, May 15, 2013 2:08 PM

All replies

  • Export-Csv file.csv -delimiter ";"

    see:

    HELP EXPORT-CSV -FULL


    ¯\_(ツ)_/¯

    Wednesday, May 15, 2013 10:36 AM
  • This is my script

    $thisThread = [System.Threading.Thread]::CurrentThread
    $originalCulture = $thisThread.CurrentCulture
    $thisThread.CurrentCulture = New-Object System.Globalization.CultureInfo('en-US')

    Get-ChildItem $DPRPath -Recurse -filter *.xlsx | foreach{
     $xl = New-Object -com Excel.Application
     $xl.Visible=$false
     $xl.displayalerts=$false
     
     $wb = $xl.Workbooks.Open($_.fullname)
      
     #$content = Get-Content $wb
     #$content | foreach {$_ -replace ",", "."} | Set-Content $wb 
     
     $csv = $OUTPath + [io.path]::GetFileNameWithoutExtension($_.fullname) + ".csv"
        $wb.SaveAs($csv,24)
        $xl.Workbooks.Close()
     
     $xl.quit()
     }

    I dont see any threads where the workbook output to csv has a change of delimiter.  Previous threads I have looked at suggest save as in this format but it doesnt work

    $wb.SaveAs($csv.Arguments.Item(1), 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, local);

    Wednesday, May 15, 2013 10:56 AM
  • The delimiter for a CSV in the US is a comma.  In Europe it is a semi-colon in some countries.  If you try to do this via setting the culture then you need to set it to fr-FR or so.  I suspect CAnada uses a semi-colon as they are also kind of lame as to the meaning of COMMA-SEPARATED-VALUES. 


    ¯\_(ツ)_/¯

    Wednesday, May 15, 2013 11:28 AM
  • YOu cannot asve a workbook as a CSV because it has a structure. YOu can save a Worksheet. YOU cannot set the delimiter.  The delimiter is set by the culture. If you are in a culture that uses the semi-colon then do not set teh culture to english and you should get a semi-colon.  If you don't then ther is something wrong with you Excel.  Did you install teh US only version of Excel or is it a MUI version.

    You can Export to a CSV and inmport it into PowerShell and re-export with a semicolon.

    Pick your method.


    ¯\_(ツ)_/¯

    Wednesday, May 15, 2013 11:36 AM
  • I just set my region to Norway and did an Excel export. to CSV.  The result was delimited by semi-colons as expected.


    ¯\_(ツ)_/¯

    Wednesday, May 15, 2013 11:44 AM
  • Yes I have changed the delimiter string in the list operator but en-US seemed to override that.  I changed the string to be

    $thisThread.CurrentCulture = New-Object System.Globalization.CultureInfo('nb-NO')

    This is for Bøkmal Norwegian I believe, I also installed french and used fr-FR and get the same error below for both languages

    PS C:\Users\pcooley.EMGS> C:\Users\pcooley.EMGS\Documents\ConvertXlsTo.ps1
    Exception setting "DisplayAlerts": "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
    At C:\Users\pcooley.EMGS\Documents\ConvertXlsTo.ps1:13 char:6
    +     $xl. <<<< displayalerts=$false
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : PropertyAssignmentException

    Exception calling "Open" with "1" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
    At C:\Users\pcooley.EMGS\Documents\ConvertXlsTo.ps1:15 char:26
    +     $wb = $xl.Workbooks.Open <<<< ($_.fullname)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : ComMethodTargetInvocation

     
    Wednesday, May 15, 2013 11:47 AM
  • You asked about delimiters qand now you say it is really because you are getting an error.

    Look at the error message.  It is pretty explicit.

    Exception setting "DisplayAlerts": "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"

    You cannot save a workbook as CSV.  You cannot set alerts when converting to a CSV.  Thre is no method to tell Excel that you want your request for a workbook to really be a Worksheet.  Export the Worksheets.


    ¯\_(ツ)_/¯



    • Edited by jrv Wednesday, May 15, 2013 11:56 AM
    Wednesday, May 15, 2013 11:55 AM
  • I was not getting the error until I changed the cultureInfo from en-US to nb-NO.

    As per your earlier comment you seemed to indicate if I changed the culture it would change the delimiter.  At least that is what I thought you meant.  I asked about delimiters as that is what my problem was.  When I changed the culture then it gives this error so back to my initial question and script, is there a way to do this and change the delimiter or do I have to take a different way to do it ?

    Wednesday, May 15, 2013 12:09 PM
  • There are significant errors in your overall script.

    Start by telling me what language your desktop is set to.  If you are in Norway then I would expect it is set to one of the Norwegian cultures.


    ¯\_(ツ)_/¯

    Wednesday, May 15, 2013 12:23 PM
  • The issue you are having is that you cannot cahnge teh culture for the system from PowerSHell that way.  Exel is an external program.  You must change its culture context which is set by the system.  YOU could spawn a process with separate culture settings and then attach to the Excel process or you can just change your desktop to the target culture.


    ¯\_(ツ)_/¯

    Wednesday, May 15, 2013 12:31 PM
  • Here is how to set the delimiter for Excel and all other programs that use the list separator.

    http://www.howtogeek.com/howto/21456/export-or-save-excel-files-with-pipe-or-other-delimiters-instead-of-commas/


    ¯\_(ツ)_/¯

    Wednesday, May 15, 2013 12:34 PM
  • I have two languages installed English-Us and Norwegian Bøkmal

    The default language in Excel is Norwegian for Editing and  Display

    The spreadsheets come in from other machines though that most likely do no have Norwegian as there default just in case that is important.  Easiest thing for me to do is to change the desktop to be non-Us culture. 

    Wednesday, May 15, 2013 12:34 PM
  • I have changed the delimiter like you specified.  I did that about two weeks ago.  It works if I do a file save as in Excel.

    It does not work from Powershell where the delimiter is a comma again.

    So I know excel is an external programme and when in excel everything works its when calling it from Powershell I have the problem.

    Wednesday, May 15, 2013 12:55 PM
  • I have changed the delimiter like you specified.  I did that about two weeks ago.  It works if I do a file save as in Excel.

    It does not work from Powershell where the delimiter is a comma again.

    So I know excel is an external programme and when in excel everything works its when calling it from Powershell I have the problem.

    Report that to Microsoft.  It is not fixable with a script.

    Export all to CSV then import into Excel and re-export with a differnt delimiter.


    ¯\_(ツ)_/¯

    Wednesday, May 15, 2013 1:17 PM
  • Here is a much easier way to do this:

    $oleDbConn = New-Object System.Data.OleDb.OleDbConnection
    $oleDbCmd = New-Object System.Data.OleDb.OleDbCommand
    $oleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
    $dataTable = New-Object System.Data.DataTable
    $oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\projects\dss\pse&g.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"
    $oleDbConn.Open()
    $oleDbCmd.Connection = $OleDbConn
    $oleDbCmd.commandtext = “Select * from [Sheet1$]”
    $oleDbAdapter.SelectCommand = $OleDbCmd
    $ret=$oleDbAdapter.Fill($dataTable)
    Write-Host	"Rows returned:$ret" -ForegroundColor green
    $dataTable | Export-Csv file.csv -Delimiter ';'
    $oleDbConn.Close()

    That is for Excel 2007.  If you have a differnt version teh Provider will be different.

    2007 - 12
    2010 - 14
    2013 - 15


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, May 15, 2013 2:09 PM
    • Marked as answer by Bill_StewartModerator Thursday, August 15, 2013 9:29 PM
    Wednesday, May 15, 2013 2:08 PM
  • Try this

    $oleDbConn = New-Object System.Data.OleDb.OleDbConnection
    $oleDbCmd = New-Object System.Data.OleDb.OleDbCommand
    $oleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
    $dataTable = New-Object System.Data.DataTable
    $oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Powershell\book1.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"
    $oleDbConn.Open()
    $oleDbCmd.Connection = $OleDbConn
    $oleDbCmd.commandtext = “Select * from [Sheet1$]”
    $oleDbAdapter.SelectCommand = $OleDbCmd
    $ret=$oleDbAdapter.Fill($dataTable)

    $dataTable | Export-Csv   'D:\Powershell\qtemp.csv' -Delimiter '|'
    $path = 'D:\Powershell\qtemp.csv'
    $outPath = $path -replace ".csv",".txt"
    Get-Content -path $path |
    ForEach-Object {$_ -replace '"','' } | 
    Out-File -filepath $outPath

    $oleDbConn.Close()

    Thanks & Regards

    Jayant Kumar Dass


    Jayant Kumar Das MS SQL Server DBA

    Thursday, April 28, 2016 7:31 PM
  • Try this

    $oleDbConn = New-Object System.Data.OleDb.OleDbConnection
    $oleDbCmd = New-Object System.Data.OleDb.OleDbCommand
    $oleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
    $dataTable = New-Object System.Data.DataTable
    $oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Powershell\book1.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"
    $oleDbConn.Open()
    $oleDbCmd.Connection = $OleDbConn
    $oleDbCmd.commandtext = “Select * from [Sheet1$]”
    $oleDbAdapter.SelectCommand = $OleDbCmd
    $ret=$oleDbAdapter.Fill($dataTable)

    $dataTable | Export-Csv   'D:\Powershell\qtemp.csv' -Delimiter '|'
    $path = 'D:\Powershell\qtemp.csv'
    $outPath = $path -replace ".csv",".txt"
    Get-Content -path $path |
    ForEach-Object {$_ -replace '"','' } | 
    Out-File -filepath $outPath

    $oleDbConn.Close()

    Thanks & Regards

    Jayant Kumar Dass


    Jayant Kumar Das MS SQL Server DBA

    Thursday, April 28, 2016 7:31 PM