PowerShell - Import multiple CSV files to Excel file in multiple worksheets

1
votes

I have the below code (from elsewhere in SO) which is importing 20 odd CSV files into different tabs within one Excel file. I need the XLSX tabs to be called the CSV file names.

The code runs but on each run at least one CSV file isn’t imported. All tabs are present but the data isn’t brought through. It is also a different CSV file/s that are missed on each run.

Is there a way to ensure all of the CSV files and data are imported into the XLSX? I thought if I pause the script on each run it would give the code a chance to reset but ‘Start-Sleep -s 1’ hasn’t helped.

Errors include:

"Microsoft Excel can convert only one column at a time. The range can be many rows tall but no more than one column wide. Try again by selecting cells in one column only."

"Unable to get the PasteSpecial property of the Range class"

Is there also a way for this script to ignore the CSV import if there is no data in one of the CSV files please?

# import multiple CSV files into separate Excel worksheets
$inputfolder='C:\somefolder’
$mergedwb='C:\somefolder\importtemplate.xlsx'

$xl = New-Object -ComObject Excel.Application
$xl.Visible=$true

$wb = $xl.WorkBooks.add()

Get-ChildItem $inputfolder\*.csv |
    ForEach-Object{
        Try{
            Write-Host "Moving $_" -ForegroundColor green
            $sheet = $wb.Sheets.Add()
            $sheet.Name = $_.BaseName
            $data = Get-Content $_ -Raw
            Set-Clipboard $data
            $sheet.UsedRange.PasteSpecial(
                [Microsoft.Office.Interop.Excel.XlPasteType]::xlPasteAll,
                [Microsoft.Office.Interop.Excel.XlPasteSpecialOperation]::xlPasteSpecialOperationAdd
            )
            $sheet.UsedRange.TextToColumns(
                $sheet.UsedRange,
                [Microsoft.Office.Interop.Excel.XlTextParsingType]::xlDelimited,
                [Microsoft.Office.Interop.Excel.XlTextQualifier]::xlTextQualifierDoubleQuote,
                $false, $false, $false, $true
            )
        }
        Catch{
            Write-Host $_ -ForegroundColor Red
        } Start-Sleep -s 1
    }
$wb.Sheets.Item('sheet1').Delete()
$wb.Sheets.Item('sheet2').Delete()

$wb.SaveAs($mergedwb)
$wb.Close()
$xl.Quit()
2
excelpowershellcsv
Check the failing CSV files. Do they use a different delimiter character than the ones that succeed? Also, it would be nice if you add a link to the original code you have found elsewhere in SO - Theo
CSV files all use the same delimiter. It's strange as I say as each run is a different file that fails to be loaded into the XLSX. The error appears for the random one that fails to load - 'Unable to get the PasteSpecial property of the Range class'. There's only 2 columns in CSV and no formulas. Do you think I should use Paste instead of PasteSpecial? - Paul1384
Hard to say.. Perhaps if you try xlPasteValues instead of xlPasteAll and perhaps also use xlPasteSpecialOperationNone instead of xlPasteSpecialOperationAdd since you're pasting into a blanc worksheet. Also, it is important to release the used COM objects from memory when finished with them: $null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet); $null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb); $null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl); [System.GC]::Collect(); [System.GC]::WaitForPendingFinalizers() - Theo

2 Answers

0
votes

Try this - it'll open the CSV files in a new Workbook and then copy the sheet content to a new sheet in the Excel XLSX file, bonus PS will check if CSV is empty.

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$wb = $excel.Workbooks.Add()

Get-ChildItem $CSVPath\*.csv | ForEach-Object {
    if ((Import-Csv $_.FullName).Length -gt 0) {
        $csvBook = $excel.Workbooks.Open($_.FullName)
        $csvBook.ActiveSheet.Copy($wb.Worksheets($wb.Worksheets.Count))
        $csvBook.Close()
    }
}
0
votes

Thanks to @kshkarin and @Theo the errors aren't shown now and all data is being copied into the XLSX. Finished code in case useful:

#import multiple CSV files into separate Excel worksheets
$inputfolder='C:\somefolder’
$mergedwb='C:\somefolder\importtemplate.xlsx'

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$wb = $excel.Workbooks.Add()

Get-ChildItem $inputfolder\*.csv | ForEach-Object {
    if ((Import-Csv $_.FullName).Length -gt 0) {
        $csvBook = $excel.Workbooks.Open($_.FullName)
        $csvBook.ActiveSheet.Copy($wb.Worksheets($wb.Worksheets.Count))
        $csvBook.Close()
    }
}

$wb.Sheets.Item('sheet1').Delete()
$wb.Sheets.Item('sheet2').Delete()

$wb.SaveAs($mergedwb)
$wb.Close()
$excel.Quit()

$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet); 
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb); 
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl); 
[System.GC]::Collect(); [System.GC]::WaitForPendingFinalizers()