CODEWITHSUNDEEP
excelcsvconvertersfile-extension
physlexic
physlexic

Reputation: 868

Create csv(s) from xlsx

How can I bulk convert excel files (xlsx) to csv? I tried 

$source = 'C:\path\to\file'

Get-ChildItem $source '*.xlsx' | Rename-Item -NewName { $_.Name -replace '\.xlsx','.csv' }

but the csvs were unreadable. Ideally, I'd like to be able to make a csv from each sheet (so I don't have to separate them manually before converting).

Thanks.

Upvotes: 1

Views: 258

Answers (1)

physlexic
physlexic

Reputation: 868

This is what I found/got:

$source = 'C:\path\to\file'

Function ExcelCSV ($File)
{
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $false
    $wb = $Excel.Workbooks.Open($File.FullName)
    foreach ($ws in $wb.Worksheets)
        {
        $ws.SaveAs(($Files.FullName -replace ".xlsx$","") + ".csv", 6)
        }
        $wb = $Excel.Workbooks.Close()
        $Excel.Quit()
}

Foreach ($Files in (Get-ChildItem -Path $source -Filter "*.xlsx"))
{
    ExcelCSV($Files)
}

Upvotes: 1

Related Questions