Simple Script to combine all SMAT CSV files into a single file

SMATCollector.ps1

$csvFiles = Get-ChildItem $PSScriptRoot -Include *.csv  -Recurse

$fileCount = $csvFiles.Count

Write-Host "Processing files: ($fileCount)"
foreach ($csv in $csvFiles) {
    Write-Host "Processing $($csv.BaseName)..."
}

$outputfilename = read-host "Please enter the output file name"
Write-Host Creating: $outputfilename
$excelapp = new-object -comobject Excel.Application
$excelapp.Visible = $true
$excelapp.DisplayAlerts = $false
$destWorkbook = $excelapp.Workbooks.Add()
$sheet = 1
foreach ($csv in $csvFiles) {
    Write-Host "Processing $($csv.BaseName)..."
    $sourceWorkbook = $excelapp.Workbooks.Open($csv)
    $sourceWorkbook.Sheets.Item(1).Copy($destWorkbook.Sheets.Item($sheet))
    $sourceWorkbook.Close()
    $sheet++  
}

$destWorkbook.SaveAs("$($PSScriptRoot)\$outputfilename")
$destWorkbook.Close()
$excelapp.quit()