excel - 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:UsersabcDesktopestcsv"
$CSVfiles = Get-ChildItem -Path $root -Filter *.csv

$xlsx = "C:UsersabcDesktopestxl.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:pathofolder" #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