I'm trying to convert multiple excel files (xls) to csv which is located in a folder using powershell.
I can convert a single file but need help converting multiple files in a folder. But need advise on how to convert multiple files.
$ExcelWB = new-object -comobject excel.application
$Workbook = $ExcelWB.Workbooks.Open(c:\temp\temp.xls)
$Workbook.SaveAs("c:\temp\temp.csv",6)
$Workbook.Close($false)
$ExcelWB.quit()
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()
}
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:
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.
Import only rows and columns that contain data, empty rows and empty columns are not imported.
Specifies custom property names to use, instead of the values defined in the column headers of the TopRow.
Automatically generate property names (P1, P2, P3, ..) instead of the ones defined in the column headers of the TopRow.
The row from where we start to import data, all rows above the StartRow are disregarded. By default this is the first row.
By default all rows up to the last cell in the sheet will be imported. If specified, import stops at this row.
The number of the first column to read data from (1 by default).
By default the import reads up to the last populated column, -EndColumn tells the import to stop at an earlier number.
Accepts a string that will be used to open a password protected Excel file.
There are caveats with this untested code but it should help wrap your head around your issue
$ExcelWB = new-object -comobject excel.application
Get-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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With