组合多个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中，只是创建空白条目，其他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 b
$ b

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:\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

目前返回以下错误消息：

This currently returns the following error message:

sqlite3.exe：错误：C：\Users\brandon.andritsch\Downloads\CSV Combination\csv1.csv第1行：预期5列数据，但找到6

sqlite3.exe : Error: C:\Users\brandon.andritsch\Downloads\CSV Combination\csv1.csv line 1: expected 5 columns of data but found 6

推荐答案

尝试：

$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

b $ b

您需要使用 SQLite 命令行shell。

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