我找到了一个部分解决问题的解决方案：

# variables from config file
$main_path = $conf_values.main_path
$tmp_path = $conf_values.tmp_path
$reports_path = $conf_values.reports_path
$system_name = $conf_values.system_name

$tmp_dir = $main_path + $tmp_path + "*"
$csvs = Get-ChildItem $tmp_dir -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) + "_" + $system_name + "_report.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 = $main_path + $reports_path + $outputfilename
$xlsx.SaveAs($output)
$excelapp.quit()

这样就解决了多对一的问题，但是在工作表中所有的数据都存储在一列中。2此时我得到：csv档案：

data1;data2;data3;...
data1;data2;data3;...

报告如下：

|            A          | B |
|-----------------------|---|
| data1;data2;data3;... |   |
|-----------------------|---|
| data1;data2;data3;... |   |

我需要帮助将数据拆分为如下列：

|   A   |   B   |   C   | ... |
|-------|-------|-------|-----|
| data1 | data2 | data3 | ... |
|-------|-------|-------|-----|
| data1 | data2 | data3 | ... |

请找到使用ImportExcel模块的github链接。https://github.com/dfinke/ImportExcel/tree/master/Examples

$outputFile = "C:\Temp\OutputExcelFile.xlsx" #Output File
$csvFiles = Get-childItem -Filter *.csv # Filtering CSV file in my present working dir
foreach ($csvFile in $csvFiles) {
#Import csv file and export it contents to Output excel file and rename the sheet.
Import-csv $csvFile | Export-Excel $outputFile -WorksheetName $csvFile.BaseName
}

希望能有所帮助。

最后我的代码看起来像：

Clear-Host
# config file
$conf_file = "C:\SEARCHLIGHT\config\searchlight_conf.conf"
$conf_values = Get-Content $conf_file | Out-String | ConvertFrom-StringData

# variables from config file
$main_path = $conf_values.main_path
$tmp_path = $conf_values.tmp_path
$reports_path = $conf_values.reports_path
$system_name = $conf_values.system_name

$tmp_dir = $main_path + $tmp_path + "*" # source file
$outputfilename = $(get-date -f yyyyMMdd) + "_" + $system_name + "_report.xlsx" # destination file with date
    
# get list of csvs files
$csvs = Get-ChildItem $tmp_dir -Include *.csv
$y = $csvs.Count
Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs) {
    Write-Host " "$csv.Name
}

Write-Host Creating: $outputfilename
# Create a new Excel workbook
$excelapp = new-object -comobject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet=1
$delimiter = ";" # delimiter used in the csv file
foreach ($csv in $csvs) {
    #$row=1
    #$column=1
    $worksheet = $xlsx.Worksheets.Item($sheet)
    $worksheet.Name = $csv.Name
    # Build the QueryTables.Add command and reformat the data
    $TxtConnector = ("TEXT;" + $csv)
    $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
    $query = $worksheet.QueryTables.item($Connector.name)
    $query.TextFileOtherDelimiter = $delimiter
    $query.TextFileParseType = 1
    $query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
    $query.AdjustColumnWidth = 1
    # Execute & delete the import query
    $query.Refresh()
    $query.Delete()
    $sheet++
} # end foreach ($csv in $csvs)
# Save & close the Workbook as XLSX
$output = $main_path + $reports_path + $outputfilename
$xlsx.SaveAs($output)
$excelapp.quit()