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

$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

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 🙂 :

      # 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