# Concatenate multiple .CSVs

**URL:** <https://forums.powershell.org/t/concatenate-multiple-csvs/7528>\
**Category:** PowerShell Help\
**Created:** [November 14, 2016, 5:23pm UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528 "2016-11-14T17:23:05Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![coltpale](https://avatars.discourse-cdn.com/v4/letter/c/b77776/32.png) [@coltpale](https://forums.powershell.org/u/coltpale)\
**Post date:** [November 14, 2016, 5:23pm UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/1 "2016-11-14T17:23:05Z")

</div>

Hi Folks,

I need to concatenate the data from 4 .csv files by comparing the CERT\_ID, USER\_ID, and ROLE\_ID from the “table.csv” to the matching fields in the other 3 files. Then output a .csv file that contains everything. How could I accomplish this?

```
$table = Import-Csv C:\table.csv -Header CERT_ID,USER_ID,ROLE_ID,CERTIFIED,CERTIFIED_BY,REMEDIATION_STATUS,COMMENTS
$certs = Import-Csv C:\certs.csv -Header CERT_ID,NAME,PERIOD,END_DATE,UPDATEUSER,UPDATEDATE
$roles = Import-Csv C:\roles.csv -Header ROLE_ID,ROLENAME,ROLEDESCRIPTION,CUSTOMPROPERTY1
$users = Import-Csv C:\users.csv -Header USER_ID,USERNAME,FIRSTNAME,LASTNAME,TITLE,PRIMARYEMAIL,OFFICENAME,MANAGER,LOCATION
```

-Cheers

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [November 14, 2016, 6:03pm UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/2 "2016-11-14T18:03:47Z")

</div>

you could use nested foreach loops

Something like this [Combine Arrays](https://powershell.org/forums/topic/combine-arrays/#post-54965) … just with some more arrays. 😉

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [November 15, 2016, 3:47am UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/3 "2016-11-15T03:47:25Z")

</div>

You could even do it in a cascade. First you merge $table and $certs. The result of that you merge with $roles and the result of that you merge with $users … done. 😉

---

<div class="post-metadata">

**Author:** ![skalizar](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@skalizar](https://forums.powershell.org/u/skalizar)\
**Post date:** [November 15, 2016, 8:06am UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/4 "2016-11-15T08:06:36Z")

</div>

Just a thought off the top of my head, there’s a [script](https://gist.github.com/gravejester/3052e0e54e35bebc3257) to convert a PSObject to a DataTable. Once converted, I would assume you can use SQL or SQL-like functions to merge your tables.

---

<div class="post-metadata">

**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\
**Post date:** [November 15, 2016, 8:22am UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/5 "2016-11-15T08:22:45Z")

</div>

@Ron, I wish it was that easy, but there isn’t any built in method to do a JOIN on a datatable either.

There are a couple of folks that have written a function to JOIN two PSObjects on a common property. Dave Wyatt has written one:  
[https://powershell.org/2012/07/13/join-object/](https://powershell.org/2012/07/13/join-object/)

I’ve also used this function by CookieMonster (Warren): [Merging data in PowerShell – Rambling Cookie Monster](http://ramblingcookiemonster.github.io/Join-Object/)

If you don’t use one of these pre-built function, the next best method is to put the data in a SQL table where you do actual JOINs on the tables.

---

<div class="post-metadata">

**Author:** ![skalizar](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@skalizar](https://forums.powershell.org/u/skalizar)\
**Post date:** [November 15, 2016, 8:47am UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/6 "2016-11-15T08:47:20Z")

</div>

@Rob - Who creates an in-memory database and doesn’t provide typical database operations? Apparently Microsoft…

It appears you can use DataRelations to perform a rudamentary join under the right conditions, but you have to write a lot of extra code to navigate it. I couldn’t find a PS example with a quick search, but they have the standard C# and [VB.Net](http://VB.Net) examples on the MSDN [page](https://msdn.microsoft.com/en-us/library/d6s958d6(v=vs.110).aspx).

---

<div class="post-metadata">

**Author:** ![coltpale](https://avatars.discourse-cdn.com/v4/letter/c/b77776/32.png) [@coltpale](https://forums.powershell.org/u/coltpale)\
**Post date:** [November 15, 2016, 1:24pm UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/7 "2016-11-15T13:24:49Z")

</div>

Hi Folks,

Thanks Olaf. The Combine Arrays method worked for this. The following is producing the output I need.

```
foreach ($Key in $Keys) {

    foreach ($Cert in $Certs) {

        if ($Key.'CERT_ID' -eq $Cert.'CERT_ID') {

            $obj1 = [PSCustomObject] @{
              'CERT_ID' = $Key.'CERT_ID'
              'USER_ID' = $Key.'USER_ID'
              'ROLE_ID' = $Key.'ROLE_ID'
              'CERTIFIED' = $Key.'CERTIFIED'
              'CERTIFIED_BY' = $Key.'CERTIFIED_BY'
              'REMEDIATION_STATUS' = $Key.'REMEDIATION_STATUS'
              'COMMENTS' = $Key.'COMMENTS'
              'NAME' = $Cert.'NAME'
              'PERIOD' = $Cert.'PERIOD'
              'END_DATE' = $Cert.'END_DATE'
              'UPDATEUSER' = $Cert.'UPDATEUSER'
              'UPDATEDATE' = $Cert.'UPDATEDATE'     
            }
        }
    }
    foreach ($Role in $Roles) {

        if ($Key.'ROLE_ID' -eq $Role.'ROLE_ID') {

            $obj2 = [PSCustomObject] @{
              'CERT_ID' = $Key.'CERT_ID'
              'USER_ID' = $Key.'USER_ID'
              'ROLE_ID' = $Key.'ROLE_ID'
              'CERTIFIED' = $Key.'CERTIFIED'
              'CERTIFIED_BY' = $Key.'CERTIFIED_BY'
              'REMEDIATION_STATUS' = $Key.'REMEDIATION_STATUS'
              'COMMENTS' = $Key.'COMMENTS'
              'NAME' = $Cert.'NAME'
              'PERIOD' = $Cert.'PERIOD'
              'END_DATE' = $Cert.'END_DATE'
              'UPDATEUSER' = $Cert.'UPDATEUSER'
              'UPDATEDATE' = $Cert.'UPDATEDATE'
              'ROLENAME' = $Role.'ROLENAME'
              'ROLEDESCRIPTION' = $Role.'ROLEDESCRIPTION'
              'CUSTOMPROPERTY1' = $Role.'CUSTOMPROPERTY1'   
            }
        }
    }
    foreach ($User in $Users) {

        if ($Key.'USER_ID' -eq $User.'USER_ID') {

            $obj3 = [PSCustomObject] @{
              'CERT_ID' = $Key.'CERT_ID'
              'USER_ID' = $Key.'USER_ID'
              'ROLE_ID' = $Key.'ROLE_ID'
              'CERTIFIED' = $Key.'CERTIFIED'
              'CERTIFIED_BY' = $Key.'CERTIFIED_BY'
              'REMEDIATION_STATUS' = $Key.'REMEDIATION_STATUS'
              'COMMENTS' = $Key.'COMMENTS'
              'NAME' = $Cert.'NAME'
              'PERIOD' = $Cert.'PERIOD'
              'END_DATE' = $Cert.'END_DATE'
              'UPDATEUSER' = $Cert.'UPDATEUSER'
              'UPDATEDATE' = $Cert.'UPDATEDATE'
              'ROLENAME' = $Role.'ROLENAME'
              'ROLEDESCRIPTION' = $Role.'ROLEDESCRIPTION'
              'CUSTOMPROPERTY1' = $Role.'CUSTOMPROPERTY1'
              'USERNAME' = $User.'USERNAME'
              'FIRSTNAME' = $User.'FIRSTNAME'
              'LASTNAME' = $User.'LASTNAME'
              'TITLE' = $User.'TITLE'
              'PRIMARYEMAIL' = $User.'PRIMARYEMAIL'
              'OFFICENAME' = $User.'OFFICENAME'
              'MANAGER' = $User.'MANAGER'
              'LOCATION' = $User.'LOCATION'
            }
        }
    }
    Write-Output $obj3
}
```

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [November 15, 2016, 3:48pm UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/8 "2016-11-15T15:48:41Z")

</div>

Great. Glad it was helpful.

---

<div class="post-metadata">

**Author:** ![coltpale](https://avatars.discourse-cdn.com/v4/letter/c/b77776/32.png) [@coltpale](https://forums.powershell.org/u/coltpale)\
**Post date:** [November 15, 2016, 6:10pm UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/9 "2016-11-15T18:10:36Z")

</div>

I guess I spoke too soon on this. $obj3 is only outputting the correct data for $Key and $User objects. It looks like it’s just grabbing the first item in the list for the $Cert and $Role objects and copying them to the rest of the rows.

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [November 16, 2016, 4:27am UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/10 "2016-11-16T04:27:51Z")

</div>

Hmmm … shame on me. I didn’t pay attention to the code you posted. I just read the ‘thanks’ and was glad. 😉

I created some test data to play with and I think I got what you need.

```
$table = Import-Csv C:\table.csv -Header CERT_ID,USER_ID,ROLE_ID,CERTIFIED,CERTIFIED_BY,REMEDIATION_STATUS,COMMENTS
$certs = Import-Csv C:\certs.csv -Header CERT_ID,NAME,PERIOD,END_DATE,UPDATEUSER,UPDATEDATE
$roles = Import-Csv C:\roles.csv -Header ROLE_ID,ROLENAME,ROLEDESCRIPTION,CUSTOMPROPERTY1
$users = Import-Csv C:\users.csv -Header USER_ID,USERNAME,FIRSTNAME,LASTNAME,TITLE,PRIMARYEMAIL,OFFICENAME,MANAGER,LOCATION

$TableCerts = Foreach($Item in $table){  
Foreach($cert in $certs){  
If($Item.‘CERT\_ID’ -eq $cert.‘CERT\_ID’){  
[PSCustomObject] @{  
‘CERT\_ID’ = $Item.‘CERT\_ID’  
‘USER\_ID’ = $Item.‘USER\_ID’  
‘ROLE\_ID’ = $Item.‘ROLE\_ID’  
‘CERTIFIED’ = $Item.‘CERTIFIED’  
‘CERTIFIED\_BY’ = $Item.‘CERTIFIED\_BY’  
‘REMEDIATION\_STATUS’ = $Item.‘REMEDIATION\_STATUS’  
‘COMMENTS’ = $Item.‘COMMENTS’  
‘NAME’ = $cert.‘NAME’  
‘PERIOD’ = $cert.‘PERIOD’  
‘END\_DATE’ = $cert.‘END\_DATE’  
‘UPDATEUSER’ = $cert.‘UPDATEUSER’  
‘UPDATEDATE’ = $cert.‘UPDATEDATE’  
}  
}  
}  
}

$TableCertRoles = foreach ($TableCert in $TableCerts) {  
foreach ($role in $roles) {  
if ($TableCert.‘ROLE\_ID’ -eq $role.‘ROLE\_ID’) {  
[PSCustomObject] @{  
‘CERT\_ID’ = $TableCert.‘CERT\_ID’  
‘USER\_ID’ = $TableCert.‘USER\_ID’  
‘ROLE\_ID’ = $TableCert.‘ROLE\_ID’  
‘CERTIFIED’ = $TableCert.‘CERTIFIED’  
‘CERTIFIED\_BY’ = $TableCert.‘CERTIFIED\_BY’  
‘REMEDIATION\_STATUS’ = $TableCert.‘REMEDIATION\_STATUS’  
‘COMMENTS’ = $TableCert.‘COMMENTS’  
‘NAME’ = $TableCert.‘NAME’  
‘PERIOD’ = $TableCert.‘PERIOD’  
‘END\_DATE’ = $TableCert.‘END\_DATE’  
‘UPDATEUSER’ = $TableCert.‘UPDATEUSER’  
‘UPDATEDATE’ = $TableCert.‘UPDATEDATE’  
‘ROLENAME’ = $role.‘ROLENAME’  
‘ROLEDESCRIPTION’ = $role.‘ROLEDESCRIPTION’  
‘CUSTOMPROPERTY1’ = $role.‘CUSTOMPROPERTY1’  
}  
}  
}  
}

$TableCertRoleUsers = foreach ($TableCertRole in $TableCertRoles) {  
foreach ($user in $users) {  
if ($TableCertRole.‘USER\_ID’ -eq $user.‘USER\_ID’) {  
[PSCustomObject] @{  
‘CERT\_ID’ = $TableCertRole.‘CERT\_ID’  
‘USER\_ID’ = $TableCertRole.‘USER\_ID’  
‘ROLE\_ID’ = $TableCertRole.‘ROLE\_ID’  
‘CERTIFIED’ = $TableCertRole.‘CERTIFIED’  
‘CERTIFIED\_BY’ = $TableCertRole.‘CERTIFIED\_BY’  
‘REMEDIATION\_STATUS’ = $TableCertRole.‘REMEDIATION\_STATUS’  
‘COMMENTS’ = $TableCertRole.‘COMMENTS’  
‘NAME’ = $TableCertRole.‘NAME’  
‘PERIOD’ = $TableCertRole.‘PERIOD’  
‘END\_DATE’ = $TableCertRole.‘END\_DATE’  
‘UPDATEUSER’ = $TableCertRole.‘UPDATEUSER’  
‘UPDATEDATE’ = $TableCertRole.‘UPDATEDATE’  
‘ROLENAME’ = $TableCertRole.‘ROLENAME’  
‘ROLEDESCRIPTION’ = $TableCertRole.‘ROLEDESCRIPTION’  
‘CUSTOMPROPERTY1’ = $TableCertRole.‘CUSTOMPROPERTY1’  
‘USERNAME’ = $user.‘USERNAME’  
‘FIRSTNAME’ = $user.‘FIRSTNAME’  
‘LASTNAME’ = $user.‘LASTNAME’  
‘TITLE’ = $user.‘TITLE’  
‘PRIMARYEMAIL’ = $user.‘PRIMARYEMAIL’  
‘OFFICENAME’ = $user.‘OFFICENAME’  
‘MANAGER’ = $user.‘MANAGER’  
‘LOCATION’ = $user.‘LOCATION’  
}  
}  
}  
}

$TableCertRoleUsers | Export-Csv -Path C:\results.csv -NoTypeInformation
```
  
I took your original posting as reference. Probably there is a more sofisticated way to do that - there always is one - but it does the job for now.

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:40pm UTC](https://forums.powershell.org/t/concatenate-multiple-csvs/7528/11 "2024-05-16T20:40:01Z")

</div>