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:
$ExcelWB = new-object -comobject excel.application
foreach($file in (Get-ChildItem "C:\temp" -filter "*.xls")) {
$newname = $file.FullName -replace '\.xls$', '.csv'
$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.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
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.
NoHeader option is quite counter-intuitive when you consider its name, vs the description of what it does! I wasted a few precious minutes on account of this!
To save multiple files you can surround your code with a loop and rename generically using the original xls name. To save on memory and processing time you should create and close the excel object outside the loop for a much more performant conversion.
$ExcelWB = new-object -comobject excel.application
foreach($file in (Get-ChildItem "C:\temp")) {
$newname = $file.FullName -replace '\.xls$', '.csv'
$Workbook = $ExcelWB.Workbooks.Open($file.FullName)
$Workbook.SaveAs($newname,6)
$Workbook.Close($false)
}
$ExcelWB.quit()
