Concatenate multiple .CSVs

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

you could use nested foreach loops

Something like this Combine Arrays … just with some more arrays. :wink:

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. :wink:

Just a thought off the top of my head, there’s a script to convert a PSObject to a DataTable. Once converted, I would assume you can use SQL or SQL-like functions to merge your tables.

@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/

I’ve also used this function by CookieMonster (Warren): 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.

@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 examples on the MSDN page.

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
}

Great. Glad it was helpful.

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.

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

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.