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

Hello @Fredrik Söderlind

I would use the next to process all items in the folder:

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()  
  
}  

Hope this helps with your query,

--------
--If the reply is helpful, please Upvote and Accept as answer--