This way，将第一行更改为存储这20个CSV文件的文件夹，然后

$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

https://stackoverflow.com/a/51094040/5995160的答案是太慢时，处理csv的数据吨，我修改了这个解决方案使用https://github.com/dfinke/ImportExcel。这大大提高了这项任务的性能，至少对我来说。

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
}

此解决方案假定用户已经将目录更改为所有csv所在的目录。

有关使用OpenText方法的解决方案，请参见下文。
至少要注意两点：

• 我假设你的工作簿默认只创建了一个工作表。如果创建的工作表多于这个工作表，你需要修改脚本，这样这些额外的工作表就会从最终结果中删除。
• 指定TextFileColumnDataTypes的方法非常巧妙。您需要修改它并将数组提供给下面的FieldInfo参数。请参阅上面链接的文档以了解它所期望的数组类型。

$CSVfiles = Get-ChildItem -Path $root -Filter *.csv

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

#Create a excel
$xl = New-Object -ComObject Excel.Application

$xl.Visible=$true

#add a workbook
$wb = $xl.WorkBooks.add(1)

# how many worksheets do you have in your original workbook? Assuming one:
$ws = $wb.Worksheets.Item(1)

ForEach ($csv in $CSVfiles){

    # OpenText method does not work well with csv files
    Copy-Item -Path $csv.FullName -Destination ($csv.FullName).Replace(".csv",".txt") -Force

    # Use OpenText method. FieldInfo will need to be amended to suit your needs
    $xl.WorkBooks.OpenText(`
                    ($file.FullName).Replace(".csv",".txt"),    # Filename
                    2,                 # Origin
                    1,                 # StartRow
                    1,                 # DataType
                    1,                 # TextQualifier
                    $false,            # ConsecutiveDelimiter
                    $false,            # Tab
                    $false,            # Semicolon
                    $true,             # Comma
                    $false,            # Space
                    $false,            # Other
                    $false,            # OtherChar
                    @()                # FieldInfo
    )

    $tempBook   = $xl.ActiveWorkbook

    $tempBook.worksheets.Item(1).Range("A1").Select()         | Out-Null
    $tempBook.worksheets.Item(1).Move($wb.Worksheets.Item(1)) | Out-Null

    # name  the worksheet
    $xl.ActiveSheet.Name = $csv.BaseName

    Remove-Item -Path ($csv.FullName).Replace(".csv",".txt")  -Force

}

$ws.Delete()

# Save & close the Workbook as XLSX.
$wb.SaveAs($xlsx,51)
$wb.Close()

$xl.Quit()