I am trying to merge two csv files with different columns in powershell, receiving error

Wendigo 0
Viewed 45

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.

powershell
1 Answers

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-Automating0
Related