Powershell

Powershell: Export a Excel Worksheet as csv file

7 Comments

Hi,

with a few lines Powershell code it is possible to export a Excel Workbook as a csv file.
Define your excel list

1
2
$sExcelFile="D:\temp\ExcelFile.xlsx"
$sCSVFile="D:\temp\list.csv"

Open Excel using DCOM

1
2
3
4
5
6
# Get COM Object
$oExcel = New-Object -ComObject "Excel.Application"
# Should Excel be visible?
$oExcel.Visible = $true
# and open excel file
$oExcelDoc = $oExcel.Workbooks.Open($sExcelFile)

Now you have to select and activate the worksheet you want to export. Index is starting by 1

1
2
3
4
# Open 1st Worksheet
$oWorksheet = $oExcelDoc.Worksheets.item(1)
# Activate, show it
$oWorksheet.Activate()

And use the SaveAs method to export the worksheet as (windows) csv

1
$oExcelDoc.SaveAs($sCSVFile,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows)

To get a list of all possible export formats  see my my previous post.

Finally close Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$oExcelDoc.Close($false)
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWorksheet)|out-null
$oWorksheet=$null
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcelDoc)|out-null
$oExcelDoc=$null
# Close Excel
$oExcel.Quit()
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null
$oExcel=$null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

Michael

7 thoughts on “Powershell: Export a Excel Worksheet as csv file”

  1. Thanks for this post. Question: In order to get this to work, I had to add the following:
    try {
    Add-Type -ASSEMBLY “Microsoft.Office.Interop.Excel” | out-null
    }catch {
    #If the assembly can’t be found this will load the most recent version in the GAC
    [Reflection.Assembly]::LoadWithPartialname(“Microsoft.Office.Interop.Excel”) | out-null
    }
    As the script wasn’t finding the interop assembly. Is there some other way to do this?

    1. Hi Adam,

      on my Windows (7) PC the Excel/Office 2010 assemblies were loaded by

      [Reflection.Assembly]::LoadWithPartialname(“Microsoft.Office.Interop.Excel”)

      may be this behaviour is changed in new Versions of Office. Which Version do use?

      Michael

    1. Hi saurabh,
      Something like this 🙂 :

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      # Get COM Object
      $oExcel = New-Object -ComObject "Excel.Application"
      # Should Excel be visible?
      $oExcel.Visible = $true
       
      $sXMLInputFolder="D:\myInputPath"
      $sCSVOutputFolder="D:\myOutPath"
       
      if(!(Test-Path $sCSVOutputFolder))
      {
          mkdir $sCSVOutputFolder
      }
       
      get-item ($sXMLInputFolder+"\*.xml") | foreach  {
          $sOutputFile=[System.IO.Path]::Combine($sCSVOutputFolder,($_.Basename+".csv"))
          # open excel file
          $oExcelDoc = $oExcel.Workbooks.Open($_.FullName)
          # Open 1st Worksheet
          $oWorksheet = $oExcelDoc.Worksheets.item(1)
          # Activate, show it
          $oWorksheet.Activate() 
          write-host "Save" $sOutputFile
          $oExcelDoc.SaveAs($sOutputFile,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows)
          $oExcelDoc.Close($false)
          Start-Sleep 1
          # Cleanup COM
          [System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWorksheet)|out-null
          $oWorksheet=$null
          Start-Sleep 1
          # Cleanup COM
          [System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcelDoc)|out-null
          $oExcelDoc=$null   
      }
      # Close Excel
      $oExcel.Quit()
      Start-Sleep 1
      # Cleanup COM
      [System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null
      $oExcel=$null
      [GC]::Collect()
      [GC]::WaitForPendingFinalizers()

      Michael

Leave a Reply