Concatenate multiple .CSVs

This topic contains 9 replies, has 4 voices, and was last updated by Profile photo of Olaf Soyk Olaf Soyk 3 weeks, 2 days ago.

  • Author
    Posts
  • #57664
    Profile photo of Colter
    Colter
    Participant

    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

  • #57673
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    you could use nested foreach loops

    Something like this Combine Arrays ... just with some more arrays. 😉

  • #57691
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    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. 😉

  • #57718
    Profile photo of Ron
    Ron
    Participant

    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.

  • #57719
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

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

    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.

  • #57722
    Profile photo of Ron
    Ron
    Participant

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

  • #57782
    Profile photo of Colter
    Colter
    Participant

    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
    }
    
  • #57811
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    Great. Glad it was helpful.

  • #57835
    Profile photo of Colter
    Colter
    Participant

    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.

  • #57856
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    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.

You must be logged in to reply to this topic.