Search notes:

PowerShell: Convert Excel worksheets of workbooks in a directory to CSV files

The following PowerShell script (or function) originated in the StackOverflow question How can I extract a particular worksheet in an xlsx spreadsheet as a text file?.

It iterates over all *.xls* workbooks in a given directory (parameter $dir), opens each of them, then tries to find a worksheet with a given name (parameter $sheetName) and then calls the workbook's method saveAs() with the value 6 (which is the number for the file format xlCSV).

The function sets the displayAlerts property to $false to prevent Excel from showing the confirmation message box when trying to export a file.

add-type -path 'C:\Program Files (x86)\Microsoft Office\Office16\DCF\Microsoft.Office.Interop.Excel.dll'

function export-CSV {

   param (
       [string] $dir,       # Must not end in a (back-)slash
       [string] $sheetName
   )

   $dir = resolve-path $dir

   $xls = new-object Microsoft.Office.Interop.Excel.ApplicationClass
   $xls.visible       = $true
   $xls.displayAlerts = $false # don't display message box when exporting a worksheet as CSV.


   foreach ($wbFile in get-childItem $dir\*.xls*) {
      $wb = $xls.workbooks.open($wbFile.fullName)

      try {
         $sh = $wb.sheets($sheetName)
      }
      catch {
         if ($_.exception.message -match 'Invalid index.') {
            write-host "Expected sheet not found in $($wb.name)"
            $wb.close()
            continue
         }
         throw $_
      }
      $sh.select()
      $csvFile = "$dir\$($wbFile.basename).csv"
      $wb.saveAs($csvFile, 6, $false)
      write-host "$csvFile was saved"
      $wb.close()
   }

   $xls.quit()
}

export-CSV "~/ZZZ/Excel/Export-CSV" 'Approval_Logs'

#
# Make sure Excel process is stopped/terminated when
# sheets are exported
# The following two methods Collect() and WaitForPendingFinalizers()
#   must be called in the scope that called the scope
#   where interop is used
#   https://stackoverflow.com/a/25135685/180275
#
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Github repository about-Excel, path: /data-exchange_import-export/CSV/convert-worksheets-in-dir.ps1

PowerShell: Convert Excel worksheets of workbooks in a directory to CSV files

See also