从多个CSV文件创建Excel电子表格

Question

我有一个Excel工作簿，其中只有一个表格和三个csv文件。该工作表具有固定数量的列（带标题）和可变数量的行。一列包含“ID”字段，最后三列为空白。每个csv具有可变数量的列，但都有一个“ID”字段，应与Excel表中的“ID”字段匹配。虽然Excel表可能具有相同ID的多个实例，但CSV中没有重复的ID。例如：

Excel文件

ID: Name: Color: Location: Age: Siblings: 123 Bob Red 234 Sally Green 345 Donald Orange 123 Bob Black

CSV1

ID: Name: Place: Animal: Location: Car: 123 Bob Here Dog Up Ferarri 234 Sally There Cat Down Porsche 345 Donald Nowhere Squid Right Yugo

CSV2

ID: Name: Place: Age: 123 Bob Here 50 234 Sally There 45 345 Donald Nowhere 100

CSV3

ID: Siblings: 123 Five 234 Three 345 Eight

目标是根据匹配的ID将CSV文件中特定列的数据添加到Excel文件中。预期输出将是以下Excel文件：

ID: Name: Color: Location: Age: Siblings: 123 Bob Red Up 50 Five 234 Sally Green Down 45 Three 345 Donald Orange Right 100 Eight 123 Bob Black Up 50 Five 我已经花了很多时间尝试找到最有效（快速）的方法来做到这一点，并认为我已经遇到了瓶颈。到目前为止，我所拥有的是：

# Pull relevant data from csv files together #
$rtFile = $selectedDirectory + "\\" + "*RT*.csv"
$seFile = $selectedDirectory + "\\" + "*SE*.csv"
$lmFile = $selectedDirectory + "\\" + "*LM*.csv"

$rtCSV = Import-Csv $rtFile | select ID, LOCATION
$seCSV = Import-Csv $seFile | select ID, AGE
$lmCSV = Import-Csv $lmFile | select ID, SIBLINGS

$rtCSV | ForEach {$_ | Add-Member 'AGE' $null}
$rtCSV | ForEach {$_ | Add-Member 'SIBLINGS' $null}

foreach ($record in $rtCSV) {
    $record.'AGE' = $seCSV | Where {$_.ID -eq $record.ID} | Select -Expand 'AGE'
    $Record.'SIBLINGS' = $lmCSV | Where {$_.ID -eq $record.ID} | Select -Expand 'SIBLINGS'
}

# Add Data to Excel Sheet #
$WorkSheet.Activate()
$range = $WorkSheet.Range("C1").EntireColumn

foreach ($searchStr in $rtCSV.ID) {
    $search = $range.Find($searchStr)

    if ($search -ne $null) {
        $firstAdr = $search.Address(0, 0, 1, 0)

        do {
            $WorkSheet.Cells.Item($search.row,17).Value() = $rtCSV[$search.row].LOCATION
            $WorkSheet.Cells.Item($search.row,18).Value() = $rtCSV[$search.row].AGE
            $WorkSheet.Cells.Item($search.row,19).Value() = $rtCSV[$search.row].SIBLINGS

            $search = $range.FindNext($search)
        } while ($search -ne $null -and $search.Address(0, 0, 1, 0) -ne $firstAdr)
    }
}

我花了一些时间，但终于明白为什么上述方法不起作用了。虽然$search.row可以返回Excel文档中匹配的单元格（因此可用于确定在哪个单元格插入数据），但它并未返回相应的索引值（？）与$rtCSV中对应的值相匹配。那么，如何确保每次ID匹配时都插入正确的LOCATION、AGE和SIBLINGS值呢？

如果在当前结构下不可能实现，是否有另一种（或许更好、更高效）的方法呢？一般来说，Excel文件的行数不会超过1,000行。

- eton blue

---

我认为你可以使用这个脚本 Merging data in PowerShell – Rambling Cookie Monster — http://ramblingcookiemonster.github.io/Join-Object/ 在将文件导入Excel之前合并它们。你可能需要先合并前两个，然后再合并第三个，但看起来它能够满足你的需求。 - Lee_Dailey

Answer 1

如果您不介意安装额外的模块来简化工作，我强烈推荐dfinke的导入Excel模块。一旦安装完成，实现您的目标所需的代码就像这样简单：

# cd C:\SO\53529676
$rtCSV = Import-Csv .\csv1.csv | select ID, LOCATION
$seCSV = Import-Csv .\csv2.csv | select ID, AGE
$lmCSV = Import-Csv .\csv3.csv | select ID, SIBLINGS
$excel = Import-Excel .\Excel1.xlsx

foreach ($record in $excel) {
  $record.'LOCATION' = ($rtCSV | Where {$_.ID -eq $record.ID}).LOCATION
  $record.'AGE' = ($seCSV | Where {$_.ID -eq $record.ID}).AGE
  $Record.'SIBLINGS' = ($lmCSV | Where {$_.ID -eq $record.ID}).SIBLINGS
}

$excel | Export-Excel .\Excel2.xlsx

- Robert Dyjas

Answer 2

如果您的主要关注点是性能，请考虑以下两个方面：

将“查找表”（csv1、csv2、csv3）转换为哈希表，以便查找速度更快（这与数据库中的索引有些相似）

将所有数据保存在平面文件（csv）中，以避免依赖关系并使脚本简单高效。如果需要，您可以将数据链接到电子表格作为视图来应用格式（您可以在MS Access中从csv创建链接表和视图，然后将其提供给Excel中的数据表）

下面是一个示例（使用一些其他伪文件以简化）。您也可以在主循环中使用工作表对象（只需循环遍历行索引并更新单元格对象即可）。

# generate hashtables
$person = @{}
$location = @{}
Import-Csv location.csv | foreach {$location.Add($_.id, @{zip=$_.zip; city=$_.city})}
Import-Csv person.csv | foreach {$person.Add($_.id, @{name=$_.name; age=$_.age})}

# loop through the main file
Import-Csv main.csv | foreach {
    $id = $_.id
    $_.name = $person[$id].name
    $_.age = $person[$id].age
    $_.city = $location[$id].city
    $_.zip = $location[$id].zip
    Write-Output $_ } | Convertto-Csv | Out-File mainOut.csv

- Mike Twc