所以我整天都在攻击互联网,寻找一种结合多个CSV文件的方法 . 无论我查找过哪种30种PowerShell方法,我都会遇到问题 .
我正在尝试将多个CSV文件合并为一个,基本上是"full join"样式 . 我需要最终得到所有CSV组合的所有行和所有列,除了我想根据公共标识符组合行 . 这个讨论:“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 "名称” . 我'd like to keep the row even if it'不在两个CSV中,只是创建空白条目,其他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
Desired combined result:
Name,Attrib1,Attrib2,AttribA,AttribB
VM1,111,True,AAA,
VM2,222,False,,YYY
VM3,333,True,CCC,ZZZ
有人对这个有任何想法吗?如果您需要我的更多信息,请告诉我 .
更新:这是我目前使用SQLite shell的代码尝试:
$db = Join-Path $env:TEMP 'temp.db'
$dir = "C:\Users\UserName\Downloads\CSV 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:\Users\UserName\Downloads\CSV Combination\sqlite3.exe" $db >$outfile
Remove-Item $db
这当前返回以下错误消息:
sqlite3.exe:错误:C:\ Users \ brandon.andritsch \ Downloads \ CSV Combination \ csv1.csv第1行:预计5列数据但找到6
1 回答
试试这个:
您需要SQLite命令行shell .