合并多个 CSV 文件 [英] Combining Multiple CSV Files

问题描述

所以我整天都在上网寻找一种组合多个 CSV 文件的方法.无论我查阅了 30 多种 PowerShell 方法中的哪一种，我都会遇到一个问题.

So I've been assaulting the internet all day looking for a way to combine multiple CSV files. I keep running into an issue, no matter which of the 30+ PowerShell approaches I've looked up.

我正在尝试将多个 CSV 文件合并为一个文件，基本上采用完全连接"样式.我需要将所有 CSV 中的所有行和所有列组合在一起，但我想根据公共标识符组合行除外.本次讨论:通过共享列合并两个 CSV 文件"，除了两个例外，这正是我想要做的.首先，它只为两个 CSV 构建，其次，如果两个 CSV 都不包含名称"，它会删除行.即使它不在两个 CSV 中，我也想保留该行，并且只需在另一个 CSV 中没有数据的地方创建空白条目.

I'm trying to combine multiple CSV files into one, essentially in "full join" style. I need to end up with all rows and all columns from all CSVs combined, with the exception that I want to combine rows based on a common identifier. This discussion: "Merging two CSV files by shared column", does exactly what I'm looking to do with two exceptions. First it's only built for two CSVs and second it drops rows if both CSVs don't contain the "Name". I'd like to keep the row even if it's not in both CSVs and simply create blank entries where there is no data in the other CSV.

CSV1.csv

Name,Attrib1,Attrib2

VM1,111,True
VM2,222,False

CSV2.csv

Name,AttribA,Attrib1

VM1,AAA,111
VM3,CCC,333

CSV3.csv

Name,Attrib2,AttribB

VM2,False,YYY
VM3,True,ZZZ

期望的组合结果:

Name,Attrib1,Attrib2,AttribA,AttribB

VM1,111,True,AAA,
VM2,222,False,,YYY
VM3,333,True,CCC,ZZZ

有人对此有任何想法吗?如果您需要我提供的更多信息，请告诉我.

Anyone have any ideas on this one? If you need more info from my end just let me know.

更新:这是我当前使用 SQLite shell 的代码尝试:

Update: Here's my current code attempt with the SQLite shell:

$db  = Join-Path $env:TEMP 'temp.db'
$dir = "C:UsersUserNameDownloadsCSV Combination"
$outfile = Join-Path $dir 'combined.csv'

@"
CREATE TABLE a (Name varchar(20),OS varchar(20),IP varchar(20),Contact varchar(20),Application varchar(20));
CREATE TABLE b (Name varchar(20));
CREATE TABLE c (Name varchar(20),Quiesce varchar(20));
CREATE TABLE d (Name varchar(20),NoQuiesce varchar(20));
.mode csv
.import '$((Join-Path $dir csv1.csv) -replace '\', '\')' a
.import '$((Join-Path $dir csv2.csv) -replace '\', '\')' b
.import '$((Join-Path $dir csv3.csv) -replace '\', '\')' c
.import '$((Join-Path $dir csv4.csv) -replace '\', '\')' d
SELECT a.Name,a.OS,a.IP,a.Contact,a.Application,c.Quiesce,d.NoQuiesce
FROM a
  LEFT OUTER JOIN b ON a.Name = b.Name
  LEFT OUTER JOIN c ON a.Name = c.Name
  LEFT OUTER JOIN d ON a.Name = d.Name
UNION
SELECT b.Name,a.OS,a.IP,a.Contact,a.Application,c.Quiesce,d.NoQuiesce
FROM b
  LEFT OUTER JOIN a ON a.Name = b.Name
  LEFT OUTER JOIN c ON b.Name = c.Name
  LEFT OUTER JOIN d ON c.Name = d.Name
UNION
SELECT c.Name,a.OS,a.IP,a.Contact,a.Application,c.Quiesce,d.NoQuiesce
FROM c
  LEFT OUTER JOIN a ON a.Name = c.Name
  LEFT OUTER JOIN b ON b.Name = c.Name
  LEFT OUTER JOIN d ON c.Name = d.Name;
"@ | filesystem::"C:UsersUserNameDownloadsCSV Combinationsqlite3.exe" $db >$outfile

Remove-Item $db

这当前返回以下错误消息:

This currently returns the following error message:

sqlite3.exe:错误:C:Usersrandon.andritschDownloadsCSV Combinationcsv1.csv 第 1 行:预期 5 列数据，但发现 6

sqlite3.exe : Error: C:Usersrandon.andritschDownloadsCSV Combinationcsv1.csv line 1: expected 5 columns of data but found 6

推荐答案

我已经创建了一个 Join-Object 代理命令称为Merge-Object(别名Merge)，因为看起来合并对象有点类似于SQLMERGE 语句经常被使用.Merge-Object 命令的默认参数设置为:JoinType = 'Full' 和 Property= {{If ($Null -ne $RightIndex) {$Right.$_} 否则{$Left.$_}}}}.这意味着所有左侧对象都使用右侧属性值更新，并且左侧对象列表中不存在的右侧对象将添加到结果中:

I have created a Join-Object proxy command called Merge-Object (alias Merge) as it appeared that merging objects slightly similar to the SQL MERGE statement is often used. The default parameters for the Merge-Object command are set to: JoinType = 'Full' and Property= {{If ($Null -ne $RightIndex) {$Right.$_} Else {$Left.$_}}}}. Meaning that all the left objects are updated with the right property values and right objects that do not exist in left object list are added to the result:

Import-Csv CSV1.csv | 
Merge (Import-Csv CSV2.csv) -On Name | 
Merge (Import-Csv CSV3.csv) -On Name |
Format-Table

结果:

Name Attrib1 Attrib2 AttribA AttribB
---- ------- ------- ------- -------
VM1  111     True    AAA
VM2  222     False           YYY
VM3  333     True    CCC     ZZZ

这篇关于合并多个 CSV 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！