Combining Multiple CSV Files

Question

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.

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

Desired combined result:

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.

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 : Error: C:\Users\brandon.andritsch\Downloads\CSV Combination\csv1.csv line 1: expected 5 columns of data but found 6

Answer 1

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

Result:

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

Answer 2

you could use a great commandline tool: Miller (http://johnkerl.org/miller/doc/).

With

mlr --csv reshape -r "^A" -o item,value then reshape -s item,value \
then unsparsify --fill-with ""  *.csv

You have

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

As input I have used these 3 files

Name,Attrib1,Attrib2
VM1,111,True
VM2,222,False

Name,Attrib2,AttribB
VM2,False,YYY
VM3,True,ZZZ

Name,AttribA,Attrib1
VM1,AAA,111
VM3,CCC,333

There is also the win exe https://github.com/johnkerl/miller/releases

Some notes about the command:

• reshape -r "^A" -o item,value, to transform the input CSVs from wide to long, applying this to all the fields whose name begins with "A";
• reshape -s item,value, to transform the previous output from long to wide;
• unsparsify --fill-with "", to manage field names over all input records. For field names absent in a given record but present in others, fills in the value "".

Answer 3

Try this:

$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

You need the SQLite command-line shell for this.