Spreadsheet to CSV Powershell

0
votes

I am trying to user powershell to convert an excel spreadsheet to CSV, below is the function I am using:

Function ConvertCSV{
    Write-Host "Converting the list to CSV..."
    $sExcelFile=$inputfile
    $sCSVFile="C:\Users\%username%\Desktop\CACL.csv"

    #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)

    # Open 1st Worksheet
    $oWorksheet = $oExcelDoc.Worksheets.item(1)
    #Activate, show it
    $oWorksheet.Activate()
    $oExcelDoc.SaveAs($sCSVFile,[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()
}
ConvertCSV

However when I run this and it loads the spreadsheet it says that it cannot open "CALC.csv" due to it being corrupted. Below is the error reported by powershell.

Exception calling "SaveAs" with "2" argument(s): "SaveAs method of Workbook class failed"

At line:17 char:5

$oExcelDoc.SaveAs($sCSVFile,[Microsoft.Office.Interop.Excel.XlFileFormat]::x ... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException

FullyQualifiedErrorId : ComMethodTargetInvocation

1
excelpowershellcsv
Doug Finke has an excellent module for dealing with XLS files. It includes ConvertFrom-CSV powershellgallery.com/packages/ImportExcel/4.0.10 – thom schumacher

1 Answers

4
votes

I think your problem is that PowerShell can't expand the %USERNAME% variable. PowerShell uses a different syntax for accessing environment variable. In your case, try changing the path from: 

$sCSVFile="C:\Users\%username%\Desktop\CACL.csv"

to:

$sCSVFile="$env:USERPROFILE\Desktop\CACL.csv"