Powershell: Export a Excel Worksheet as csv file

Hi,

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

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

Open Excel using DCOM

# 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

# 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

$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

$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

Advertisment to support michlstechblog.info

Add a comment »6 comments to this article

  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?

    Reply

    • 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

      Reply

  2. I’m using the same thing – I didn’t see this in your script

    Reply

  3. Finally close Excel…..whole lot of of stuff. I’ve been using just $excel.close($true) why the rest?

    Reply

  4. How to automate the above script to retrieve any files from directory and convert to csv

    Reply

    • Hi saurabh,
      Something like this 🙂 :

      # 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

      Reply

Time limit is exhausted. Please reload CAPTCHA.

Original Theme by Schiy · Powered by WordPress