0

I have two csv files. Let's say I have three columns in the first file that are Name, ID, and Phone, and the second file is Address and Location. I am trying to merge them using the following script:

$dir = ".\csvs"

foreach ($csv in (Get-ChildItem "$dir\*.csv"))
{
    Import-Csv $csv | Export-Csv .\merged.csv -NoTypeInformation -Append
}

But am receving the following error:

Export-Csv : Cannot append CSV content to the following file: .\merged.csv. The appended object does not have a
property that corresponds to the following column: . To continue with mismatched properties, add the -Force parameter,
and then retry the command.

What is this error trying to say? The new file is empty.

mklement0
  • 382,024
  • 64
  • 607
  • 775
Wendigo
  • 63
  • 1
  • 3
  • 1
    so you're attempting to merge the CSVs horizontally while your code is attempting to merge them vertically (hence why you receive the error, CSVs must have the same headers for that to work). Leaving that aside, how do you know which `Address` and `Location` corresponds with each entry on of the other CSV? – Santiago Squarzon Sep 10 '22 at 19:29
  • Using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Import-Csv .\file1.csv |Join (Import-Csv .\file2.csv) |Export-Csv .\merged.csv` – iRon Sep 10 '22 at 20:07

1 Answers1

1

What you're hoping to do it basically a 'join'. Be it CSV, CLIXML, JSON, or any set of objects, you can do build a basic join in a couple of ways.

Assuming that there is not a "key" row you could join on, and you're just joining each row in order.

# Create a hashtable or [Ordered] hashtable to keep track of each file
$fileData  = [Ordered]@{}
# We'll need to know the maximum number of rows, so set it to zero.
$MaxRowCount = 0 
# Walk over each file
foreach ($csvFile in (Get-ChildItem "$dir\*.csv")) {
   # Import the CSV, using the array operator force it into a list.
   $csvData = @(Import-Csv $csvFile)
   # Put it into our table
   $fileData[$csvFile] = $csvData
   if ($fileData[$csvFile].Length -gt $MaxRowCount) {
      $maxRowCount = $fileData[$csvFile].Length 
   }   
}

# Now, we need to go row-by-row and join our data
# In PowerShell, you can assign the results of a for statement.
# So we can just walk thru our loop and let it populate the data
# (though, once again, we're using to array operator to force it into an array)
$joinedData =
@(for ($rowNumber = 0 ; $rowNumber -lt $maxRowCount; $rowNumber++) {
    # Create an ordered hashtable for the new data
    $newRow = [Ordered]@{}
    # Walk thru each file
    foreach ($filePath in $fileData.Keys) {
       # and get the row from the file.
       $fileRow = $fileData[$filePath][$rowNumber]
       # Every object in PowerShell wrapped in a .PSObject
       # this lets us walk the properties of each row
       foreach ($prop in $fileRow.PSObject.Properties) {
           # copy the data into the new output
           # (the last file will 'win' when property names collide)
           $newRow[$prop.Name] = $prop.Value       
       }
    }

    # now, we turn our row into an object
    [PSCustomObject]$newRow
})

$joinedData | Export-Csv .\Joined.csv

If you wanted to use a key property to join rows instead, please clarify the question and I can provide a similar answer (you'll be going thru key value by key value, not row by row).

Also, again, the object's don't have to have come from a CSV file. You could easily swap out Import-CSV for Import-CliXml, or, say, Import-Excel (from the module ImportExcel).

Start-Automating
  • 8,067
  • 2
  • 28
  • 47