Convert all files in a folder from XLSX to CSV or from CSV to XSLX

convert2.ps1

# May 4, 2024
# convert all files in a folder from xlsx to csv or the other way around
# run like ".\convert2.ps1 xlsx csv" or ".\convert2.ps1 csv xlsx"

param (
    [string]$src,
    [string]$dst
)

$excel = New-Object -ComObject excel.application
$excel.visible = $false
$folderpath = $PSScriptRoot

$srcWildCard=  "*.$src"

# Perform different actions based on the format
if ($dst -eq 'csv') {
    $format = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV
}
elseif ($dst -eq 'xlsx') {
    $format = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
}
else {
    Write-Host "Unsupported format: $dst"
    # Handle unsupported formats or show an error message
    return
}
Write-Host "converting $src to $dst"

# count the number of files that match the pattern *.$src
$num = (Get-ChildItem -Path $folderPath -Filter $srcWildCard -File).Count
"converting $num file(s)"

Get-ChildItem -Path $folderpath -Filter $srcWildCard | ForEach-Object {
    $path = ($_.fullname).substring(0, ($_.FullName).LastIndexOf("."))
    Write-Host "Converting $path"
    try {
        $workbook = $excel.workbooks.open($_.fullname)
        $path += "." + $dst
        $workbook.saveas($path, $format)
        $workbook.close()
        # remove-item $_.fullname # this is optional, but I like to keep a backup
    }
    catch {
        Write-Host "Error processing file: $($_.Exception.Message)"
    }
}

$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()