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 »4 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

Time limit is exhausted. Please reload CAPTCHA.

Original Theme by Schiy · Powered by WordPress