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

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