Spreadsheet to CSV Powershell

Question

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

Doug Finke has an excellent module for dealing with XLS files. It includes ConvertFrom-CSV powershellgallery.com/packages/ImportExcel/4.0.10 — Thom Schumacher
– Thom Schumacher, Commented Mar 21, 2018 at 22:37

boxdog · Accepted Answer · 2018-03-21 22:41:40Z

4

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"

answered Mar 21, 2018 at 22:41

boxdog

8,5023 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheMadTechnician Over a year ago

Or just $home\desktop\CACL.csv

Collectives™ on Stack Overflow

Spreadsheet to CSV Powershell

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related