Convert multiple xls to csv using powershell
You can just wrap it in a loop that iterates over all the files and change the xls
extension to csv
:
foreach($file in (Get-ChildItem "C:\temp")) { $newname = $file.FullName -replace '\.xls$', '.csv' $ExcelWB = new-object -comobject excel.application $Workbook = $ExcelWB.Workbooks.Open($file.FullName) $Workbook.SaveAs($newname,6) $Workbook.Close($false) $ExcelWB.quit()}
There are caveats with this untested code but it should help wrap your head around your issue
$ExcelWB = new-object -comobject excel.applicationGet-ChildItem -Path c:\folder -Filter "*.xls" | ForEach-Object{ $Workbook = $ExcelWB.Workbooks.Open($_.Fullname) $newName = ($_.Fullname).Replace($_.Extension,".csv") $Workbook.SaveAs($newName,6) $Workbook.Close($false)}$ExcelWB.quit()
Take the lines in between the first and last and build a loop. Use Get-ChildItem
to grab your xls files and then build a new name by replacing the extension if the FullName
of the file
The conversion from xlsx files to csv can be done far quicker and without COM Objects - so without Excel installed - using the ImportExcel module developped by Doug Finke:
Install-Module -Name ImportExcel -RequiredVersion 5.4.2 gci *.xlsx | %{Import-Excel $_ | Export-Csv ($_.basename + ".csv")}
Or the other way around:
gci *.csv | %{Import-Csv $_ | Export-Excel ($_.basename + ".xlsx")}
Parameters available for the Import-Excel cmdlet:
WorksheetName
Specifies the name of the worksheet in the Excel workbook to import. By default, if no name is provided, the first worksheet will be imported.
DataOnly
Import only rows and columns that contain data, empty rows and empty columns are not imported.
HeaderName
Specifies custom property names to use, instead of the values defined in the column headers of the TopRow.
NoHeader
Automatically generate property names (P1, P2, P3, ..) instead of the ones defined in the column headers of the TopRow.
StartRow
The row from where we start to import data, all rows above the StartRow are disregarded. By default this is the first row.
EndRow
By default all rows up to the last cell in the sheet will be imported. If specified, import stops at this row.
StartColumn
The number of the first column to read data from (1 by default).
EndColumn
By default the import reads up to the last populated column, -EndColumn tells the import to stop at an earlier number.
Password
Accepts a string that will be used to open a password protected Excel file.