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

You might use the [Join-Object] cmdlet from the PowerShell Gallery for this: Import-CSV .\CSV1.csv | Join (Import-CSV .\CSV2.csv) Hosts {$Right.$_} | Join (Import-CSV .\CSV3.csv) Hosts {$Right.$_} | Export-CSV .\combined.csv — iRon

iRon iRon · Accepted Answer · 2019-02-24T19:05:00

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

Combining Multiple CSV Files

3 Answers