-1

I have seen numerous examples of how to combine CSV files, but none quite fit what I am trying to accomplish. I have multiple CSV files that only contain 1 row, but multiple columns. Each header may differ in each CSV, but they do have a common column name between them. The output I want to accomplish is to add all headers from each CSV file (regardless of whether the shown value is null or not) to row 1 of my output CSV file. Then I want to align each CSV's single row output to a row in the output CSV file, and populate each column accordingly, and leave those empty that have no values assigned.

CSV1: 

Name,Date,Year,Country
Bill,May 2018,1962,Canada

CSV2:

Profile,Prov,Size,Name
1,ON,14,Steve

CSV Final:

Name,Profile,Size,Date,Year,Prov,Country
Bill,,,May 2018,1962,,Canada
Steve,1,14,,,ON,,
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
SteveB
  • 3
  • 1
  • look for one of the several `Join-Object` cmdlets mimicking the sql join. –  Dec 08 '18 at 15:56
  • Using this [`Join-Object`](https://stackoverflow.com/a/45483110/1701026) from the [PowerShell Gallery](https://www.powershellgallery.com/packages/Join/2.3.0): `$Csv1 | FullJoin $Csv2 Name` – iRon Dec 08 '18 at 18:42

2 Answers2

0

While there will be another option to do it in a one liner with Join-Object, You can get this cmdlet from PSGallery.

Find-Script join-object | Install-Script -Verbose -Scope CurrentUser

below works,

(Join-Object -Left $CSV1 -Right $CSv2 -LeftJoinProperty Name -RightJoinProperty Name ), (Join-Object -Left $CSV2 -Right $CSV1 -LeftJoinProperty Name -RightJoinProperty Name -Type AllInLeft) | Export-Csv -Path c:\CombinedCsv.csv -NoTypeInformation
Prasoon Karunan V
  • 2,916
  • 2
  • 12
  • 26
0

Import, extract, construct and export...

$files = 'csv1.csv','csv2.csv'
$outputcsv = 'final.csv'

# import all files
$csv = $files | Import-Csv 

# extract columns from all files
$columns = $csv | ForEach {
    $_ | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name
}

# construct an object (from file 1) with all the columns
# this will build the initial csv
# (SilentlyContinue on members that already exist)

$columns | ForEach {
    Add-Member -InputObject $csv[0] -Name $_ -MemberType NoteProperty -Value '' -ErrorAction SilentlyContinue
}

# export first object - and build all columns
$csv | Select -First 1 | Export-Csv -Path $outputcsv -NoTypeInformation

# export the rest
# -force is needed, because only 1st object has all the columns
$csv | Select -Skip 1 | Export-Csv -Path $outputcsv -NoTypeInformation -Force -Append

# show files
($files+$outputcsv) | ForEach { "$_`:"; (Get-Content -LiteralPath $_); '---' }

Edit: No need to overthink the export. A single line is enough: $csv | Export-Csv -Path $outputcsv -NoTypeInformation

Palansen
  • 311
  • 2
  • 7