Skip to content

Instantly share code, notes, and snippets.

@greister

greister/Export-ExcelCSV.ps1

Created June 26, 2017 13:32
Show Gist options
  • Save greister/1e085e3f85efaee66be78902474edfa5 to your computer and use it in GitHub Desktop.
Save greister/1e085e3f85efaee66be78902474edfa5 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @majkinetor majkinetor revised this gist Jul 6, 2016. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions Export-ExcelCSV.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,7 @@
    # Last Change: 12-Feb-2016.

    param(
    # Path to Excel file
    [string] $Path
    )

  2. @majkinetor majkinetor revised this gist Jul 6, 2016. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions Export-ExcelCSV.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,6 @@
    # Author: Miodrag Milic <[email protected]>
    # Last Change: 12-Feb-2016.

    param(
    [string] $Path
    )
  3. @majkinetor majkinetor created this gist Jul 6, 2016.
    36 changes: 36 additions & 0 deletions Export-ExcelCSV.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,36 @@
    param(
    [string] $Path
    )

    if (!(Test-Path $Path)) { throw Path not found: $Path }
    ps excel -ea 0| kill

    $Path = Resolve-Path $Path
    $excel = New-Object -COM "Excel.Application"
    if (!($excel)) {throw "Can not create Excel COM object" }

    $workbook = $excel.Workbooks.Open($Path)
    $worksheets = $workbook.Worksheets

    $base_name = $path -replace '.xlsx$'
    $worksheets | % {
    $sheet = $_
    $csv_name = $base_name + '_' + $sheet.name + '.csv'
    if (Test-Path $csv_name) { rm $csv_name }
    $sheet.SaveAs($csv_name, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlUnicodeText) ; # xlCSVWindows, xlCSV, xlUnicodeText
    }

    $workbook.Saved = $true
    $workbook.close()
    $excel.quit()
    #[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) > $null
    #[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) > $null

    ps excel | kill #for some reason Excel stays
    ls "$(Split-Path $Path)\*.csv" | % { (Get-Content $_) -replace '\t',',' | Set-Content $_ -Encoding utf8 }

    #bug: https://support.microsoft.com/en-us/kb/320369
    #$ci = [System.Globalization.CultureInfo]'en-US'
    #$workbook = $excel.Workbooks.PSBase.GetType().InvokeMember('Open', [Reflection.BindingFlags]::InvokeMethod, $null, $excel.Workbooks, $Path, $ci)
    #[void]$workbook.PSBase.GetType().InvokeMember( 'SaveAs', [Reflection.BindingFlags]::InvokeMethod, $null, $workbook, ($res, $fmt), $ci)
    #[void]$workbook.PSBase.GetType().InvokeMember( 'Close', [Reflection.BindingFlags]::InvokeMethod, $null, $workbook, 0, $ci)