首页 文章

组合多个CSV文件

提问于
浏览
2

所以我整天都在攻击互联网,寻找一种结合多个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 回答

  • 0

    试试这个:

    $db  = Join-Path $env:TEMP 'temp.db'
    $dir = "C:\some\folder"
    $outfile = Join-Path $dir 'combined.csv'
    
    @"
    CREATE TABLE a (Name varchar(20),Attrib1 varchar(20),Attrib2 varchar(20));
    CREATE TABLE b (Name varchar(20),AttribA varchar(20),Attrib1 varchar(20));
    CREATE TABLE c (Name varchar(20),Attrib2 varchar(20),AttribB 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
    SELECT a.Name,a.Attrib1,a.Attrib2,b.AttribA,c.AttribB
    FROM a
      LEFT OUTER JOIN b ON a.Name = b.Name
      LEFT OUTER JOIN c ON a.Name = c.Name
    UNION
    SELECT b.Name,a.Attrib1,a.Attrib2,b.AttribA,c.AttribB
    FROM b
      LEFT OUTER JOIN a ON a.Name = b.Name
      LEFT OUTER JOIN c ON b.Name = c.Name
    UNION
    SELECT c.Name,a.Attrib1,a.Attrib2,b.AttribA,c.AttribB
    FROM c
      LEFT OUTER JOIN a ON a.Name = c.Name
      LEFT OUTER JOIN b ON b.Name = c.Name;
    "@ | sqlite3 $db >$outfile
    
    Remove-Item $db
    

    您需要SQLite命令行shell .

相关问题