Multiple csv files into a xlsx file but different sheets using powershell

Question

I have 20 csv files. Each are unrelated. How do I combine them together into one xlsx file with 20 sheets, each named after the csv files.

$root = "C:\Users\abc\Desktop\testcsv"
$CSVfiles = Get-ChildItem -Path $root -Filter *.csv

$xlsx = "C:\Users\abc\Desktop\testxl.xlsx" #output location
$delimiter = "," #delimiter

#Create a excel
$xl=New-Object -ComObject Excel.Application
$xl.Visible=$true
#add a workbook
$wb=$xl.WorkBooks.add(1)

ForEach ($csv in $CSVfiles){

    #name  the worksheet
    $ws=$wb.WorkSheets.item(1)
    $ws.Name = [io.path]::GetFileNameWithoutExtension($csv)

    $TxtConnector = ("TEXT;" + $csv)
    $Connector = $ws.QueryTables.add($TxtConnector,$ws.Range("A1"))
    $query = $ws.QueryTables.item($Connector.name)
    $query.TextFileOtherDelimiter = $delimiter
    $query.TextFileParseType  = 1
    $query.TextFileColumnDataTypes = ,1 * $ws.Cells.Columns.Count
    $query.AdjustColumnWidth = 1

    # Execute & delete the import query
    $query.Refresh()
    $query.Delete()
    $wb.SaveAs($xlsx,51)
}
# Save & close the Workbook as XLSX.
$xl.Quit()

Answer 1

This way, change the first line to the folder where you store those 20 CSV files and then

$path="c:\path\to\folder" #target folder
cd $path;

$csvs = Get-ChildItem .\* -Include *.csv
$y=$csvs.Count
Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs)
{
Write-Host " "$csv.Name
}
$outputfilename = $(get-date -f yyyyMMdd) + "_" + $env:USERNAME + "_combined-data.xlsx" #creates file name with date/username
Write-Host Creating: $outputfilename
$excelapp = new-object -comobject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet=1

foreach ($csv in $csvs)
{
$row=1
$column=1
$worksheet = $xlsx.Worksheets.Item($sheet)
$worksheet.Name = $csv.Name
$file = (Get-Content $csv)
foreach($line in $file)
{
$linecontents=$line -split ',(?!\s*\w+")'
foreach($cell in $linecontents)
{
$worksheet.Cells.Item($row,$column) = $cell
$column++
}
$column=1
$row++
}
$sheet++
}
$output = $path + "\" + $outputfilename
$xlsx.SaveAs($output)
$excelapp.quit()
cd \ #returns to drive root

Answer 2

https://stackoverflow.com/a/51094040/5995160 answer is too slow when dealing with csv's with a ton of data, I modified this solution to use https://github.com/dfinke/ImportExcel. This has greatly improved the performance of this task, at least for me.

Install-Module ImportExcel -scope CurrentUser
$csvs = Get-ChildItem .\* -Include *.csv
$csvCount = $csvs.Count
Write-Host "Detected the following CSV files: ($csvCount)"
foreach ($csv in $csvs) {
    Write-Host " -"$csv.Name
}

$excelFileName = $(get-date -f yyyyMMdd) + "_" + $env:USERNAME + "_combined-data.xlsx"
Write-Host "Creating: $excelFileName"

foreach ($csv in $csvs) {
    $csvPath = ".\" + $csv.Name
    $worksheetName = $csv.Name.Replace(".csv","")
    Write-Host " - Adding $worksheetName to $excelFileName"
    Import-Csv -Path $csvPath | Export-Excel -Path $excelFileName -WorkSheetname $worksheetName
}

This solution assumes that the user has already changed directories to where all the csv's live.