convert xlsx to csv using powershell

Question

Hi,

I have looked around a lot to find a script that converts xlsx-files in a folder to csv-files.

I have found script that does this but only for one file and with a certain file name.
Below works fine for one file with a certain name "list_of_names". Anyone who can rewrite this to accept all xlsx-files in a folder independent on file names?

'
Function ExcelToCsv ($File) {
$myDir = "D:\Excel"
$excelFile = "$myDir\" + $File + ".xlsx"
$Excel = New-Object -ComObject Excel.Application
$wb = $Excel.Workbooks.Open($excelFile)

foreach ($ws in $wb.Worksheets) {
    $ws.SaveAs("$myDir\" + $File + ".csv", 6)
}
$Excel.Quit()

}

$FileName = "list_of_names"
ExcelToCsv -File $FileName

Thanks!

Fredrik

Answer 1

To import an Excel spreadsheet and export each workbook as a separate CSV is pretty easy once you've installed the ImportExcel module:

$infile = "c:\junk\OneTwoThreeFour.xlsx"  
$outdir = "c:\junk\"  
(Open-ExcelPackage -Path $infile).psobject.properties |  
    Where-Object {$_.MemberType -eq 'ScriptProperty'} |  
        Select-Object -ExpandProperty Name |  
            ForEach-Object{  
                $outfile = "{0}{1}.csv" -f $outdir,$_  
                Import-Excel -Path $infile -WorksheetName $_ |  
                    Export-Csv $outfile -NoTypeInformation  
            }  

Answer 2

Try this (adjusting the path, of course):

Function ExcelToCsv ($File) {
    $Excel = New-Object -ComObject Excel.Application
    $wb = $Excel.Workbooks.Open($File)

    $x = $File | Select-Object Directory, BaseName
    $n = [System.IO.Path]::Combine($x.Directory, (($x.BaseName, 'csv') -join "."))

    foreach ($ws in $wb.Worksheets) {
        $ws.SaveAs($n, 6)
    }
    $Excel.Quit()
}

Get-ChildItem C:\Junk\*.xlsx |
    ForEach-Object{
        ExcelToCsv -File $_
    }

As long as the files have only a single workbook it should work. I didn't try it with more than one workbook.

Answer 3

Hi! Many thanks. I can execute your script in the PowerShell editor and it works fine. However I cannot make it work in SSIS.
Is that something you are familiar with? The package I have created executes without errors, however nothing actually happens. It feels like its not actually running the script.

Attached figure shows how I have configured the Process Task. I have also tried "unrestricted". It also executes without error, however nothing happens.

Answer 4

I think it would be unusual to find a Microsoft Office application installed on a SQL server! I don't even know if that's a supported situation, but it'd give me the heebie-jeebies if it was.

If the absence of the necessary COM object for Excel is your problem you can try the ImportExcel module. I don't think there's a direct-to-csv cmdlet, but you should be able to export rows by piping them into the regulate PowerShell Export-CSV cmdlet. The upside of this is that you don't need Excel.

Answer 5

Thanks Rich for taking your time to answer a newbie like me =)