Combine arrays

This topic contains 13 replies, has 6 voices, and was last updated by Profile photo of Olaf Soyk Olaf Soyk 7 months, 2 weeks ago.

  • Author
    Posts
  • #54871
    Profile photo of lyn wood
    lyn wood
    Participant

    I am at the end of a project and have been unable to find a appropriate way to combine arrays and leave it in the desired format. Both arrays are in the format

    $array1
    First Name : Fred
    Surname : Brown
    Unique ID : 123456

    First Name : John
    Surname : Brown
    Unique ID : 234567

    $array2
    Student ID : 987654
    First Name : Fred
    Surname : Brown
    Unique ID : 123456

    Student ID : 876543
    First Name : John
    Surname : Brown
    Unique ID : 234567

    When I view them via command prompt, I have to match them by the Unique ID and then add the Student ID to either $array1 or create a separate array with Student ID and all the details in $array1.

    Thanks for any suggestions

  • #54888
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Hi Lyn,
    Not completely sure about your dataset. What you have provided looks like hash tables. Are they arrays of hash tables?

  • #54891
    Profile photo of lyn wood
    lyn wood
    Participant

    Hi Curtis,

    It could well be as I am still getting my head around the difference between them.

  • #54897
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    Lyn,
    Compare-Object could be helpful for you. You've already asked once about matching this "Unique ID" from csv files.

  • #54943
    Profile photo of lyn wood
    lyn wood
    Participant

    Hi Olaf,

    I will give Compare-Object another go if it will let me pick the different details from the 2 Hash table to create a third Hash table, I just had not considered it again as I had already sorted the differences.

  • #54944
    Profile photo of lyn wood
    lyn wood
    Participant

    Hi Olaf,

    I just got the Compare-Object to partially work(some of the details from 1 file did not come through at all which is a common field in both files but displayed differently which could explain why, it was a date field) and I am still left with the issue that I have with my current solution for comparing the file which is that I have not been able to use the $_.'Unique ID' data as it is being piped so I can grab the data as it is being sorted and combine it with other data from the other file. Which bring me back to combining the files again.

  • #54945
    Profile photo of Joshua Wortz
    Joshua Wortz
    Participant

    Lyn,

    I've had great success with -match to search multivalued objects in an array.

    $array1 -match "123465"

    Josh

  • #54948
    Profile photo of lyn wood
    lyn wood
    Participant

    Hi Josh,

    Thank you for the suggestion, I tried a couple of variations of -match and could get part of the results that I need but could not get a fully working solution. All the attempts I made I would get one line from the searched line and then the whole column from the other, is there a way to sort the 2 files and access them a line at a time so I can pull out the details into a new file? I have already got the script to the point that the files match via the Unique ID I just need different details from each file for the final file to be of use.

    Thanks again for any help

  • #54959
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    try to look into these topics

    comparing-two-multi-dimensional-arrays/

    compare-excel

    • This reply was modified 7 months, 3 weeks ago by Profile photo of Max Kozlov Max Kozlov.
    • This reply was modified 7 months, 3 weeks ago by Profile photo of Max Kozlov Max Kozlov.
  • #54965
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    I'm not entirely sure what you're trying to do. If you're just trying to combine them, then a nested foreach loop should do the trick:

    $array1 = Import-Csv C:\_mb\csv1.csv
    $array2 = Import-Csv C:\_mb\csv2.csv
    
    foreach ($a in $array1) {
    
        foreach ($b in $array2) {
    
            if ($a.'Unique ID' -eq $b.'Unique ID') {
    
                $obj = [PSCustomObject] @{
                  'First Name' = $a.'First Name'
                  'Surname' = $a.Surname
                  'Unique ID' = $a.'Unique ID'
                  'Student ID' = $b.'Student ID'        
                }
            }
    
        }
    
        Write-Output $obj
    }
    
  • #54986
    Profile photo of lyn wood
    lyn wood
    Participant

    Hi Matt,

    You have given me the last piece of the puzzle I needed to make my Script usable and I thank you and everyone very much as this has been a very good learning curve on what powershell is capable. My initial goal at the start of this project was to create a script that would ask for 3 things Oldstudents.xls, CurrentStudent.csv and School, it would then sort them and produce 3 files on the results.
    1.New Students
    2.Old Students
    3.Current Students
    Which all 3 files where uploaded to the service that needed the details updated they only accepted the file in a specific format which is why the final piece of the puzzle looks as it is. I used your initial bases for nested loop and modified it to produce the needed outcome as your format did not produce the desired file format compared to what I needed(this is probably due to me needing to get a better understanding on how array and hash files can be formatted, I will need more work on that later)
    This is what it ended up as in the working script

    ###############################################################################
    # Updates Current Students ID DOB Tag and Year #
    ###############################################################################

    $CurrentStu = @()

    ForEach ($lineCurr in $SasFile)

    {
    If ($AcerFile | Where-Object {$_.'Unique ID' -eq $lineCurr.'Unique ID'})

    {
    $CurrentStu += $lineCurr
    }
    }

    $New1 = "Student ID,Family name,Given name,Middle names,Username,Passwords,Date of birth,Gender,Tags,Unique ID,Year level,School Year"

    $Curr = @()
    $Curr += $New1

    ForEach ($Obj in $AcerFile){

    ForEach ($line2 in $CurrentStu)

    {
    If ($Line2.gender -eq "F")
    {
    $StudentID = $Obj.'Student ID'
    $Fname1 = $Line2.'Family Name'
    $Gname1 = $Line2.'Given Name'
    $Mname1 = "
    $Username1 = $Line2.Username
    $Password1 = $Line2.Password
    $Birth1 = $Line2.DOB
    $BirthY1 = $Birth1.Substring(0,4)
    $BirthM1 = $Birth1.Substring(4,2)
    $BirthD1 = $Birth1.SubString(6,2)
    $BirthFixed1 = $BirthD1 + "-" + $BirthM1 +"-" + $BirthY1
    $Gender1 = "Female"
    $Tags1 = $Line2.Tags
    $ID1 = $Line2.'Unique ID'
    $YearLevel1 = 'Year' + " " + $Line2.'Year Level'
    $Year1 = '2016'

    $CurrentStudents = $StudentID + "," + $Fname1 + "," + $Gname1 + "," + $Mname1 + "," + $Username1 + "," + $Password1 + "," + $BirthFixed1 + "," +$Gender1 + "," + $Tags1 + "," + $ID1 + "," + $YearLevel1 + "," + $Year1
    }
    Else
    {
    $StudentID = $Obj.'Student ID'
    $Fname1 = $Line2.'Family Name'
    $Gname1 = $Line2.'Given Name'
    $Mname1 = "
    $Username1 = $Line2.Username
    $Password1 = $Line2.Password
    $Birth1 = $Line2.DOB
    $BirthY1 = $Birth1.Substring(0,4)
    $BirthM1 = $Birth1.Substring(4,2)
    $BirthD1 = $Birth1.SubString(6,2)
    $BirthFixed1 = $BirthD1 + "-" + $BirthM1 +"-" + $BirthY1
    $Gender1 = "Male"
    $Tags1 = $Line2.Tags
    $ID1 = $Line2.'Unique ID'
    $YearLevel1 = 'Year' + " " + $Line2.'Year Level'
    $Year1 = '2016'

    $CurrentStudents = $StudentID + "," + $Fname1 + "," + $Gname1 + "," + $Mname1 + "," + $Username1 + "," + $Password1 + "," + $BirthFixed1 + "," +$Gender1 + "," + $Tags1 + "," + $ID1 + "," + $YearLevel1 + "," + $Year1
    }
    }
    $Curr += $CurrentStudents
    }
    $filename1 = "CurrentStu"
    $Curr | out-file $FileName1 -Append
    $Current = Import-Csv .\CurrentStu
    $Current | Export-Csv .\CurrentStudents.csv -NoTypeInformation

    Thanks again to everyone who help contribute(even if I did not exactly use your script or method) it has helped me to create a script which will be useful.

    • #54995
      Profile photo of Olaf Soyk
      Olaf Soyk
      Participant

      Lyn,

      great that you finaly got what you need. But you could make yours and our lives easier when you format your code here as code and indent your code. It makes reading and understanding the code much easier.

      Thanks

  • #55028
    Profile photo of lyn wood
    lyn wood
    Participant

    Latest update on the Combine arrays script

    When I went through the final testing I realised that it still was not right and after referring back to Matt's suggestion I have now finally got a workable script which is below

    $CurrentStu = @()
    foreach ($LineCurr in $SASFile) {

    foreach ($AcerFile1 in $AcerFile) {

    if ($LineCurr.'Unique ID' -eq $AcerFile1.'Unique ID'){
    $obj = [PSCustomObject] @{
    'Student ID' = $AcerFile1.'Student ID'
    'Family Name' = $LineCurr.'Family Name'
    'Given name' = $LineCurr.'Given name'
    'Middle names' = $AcerFile1.'Middle names'
    'Username' = $LineCurr.Username
    'Passwords' = $LineCurr.'Password'
    'Date of birth' = $AcerFile1.'Date of birth'
    'Gender' = $AcerFile1.Gender
    'Tags' = $LineCurr.Tags
    'Unique ID' = $LineCurr.'Unique ID'
    'Year Level' = $LineCurr.'Year Level'
    'School Year' = '2016'
    }
    $CurrentStu += $Obj
    }
    }
    }

    $CurrentStu | Export-Csv .\CurrentStudents.csv -NoTypeInformation

    Thanks again for the help and I hope that the formatting is better

You must be logged in to reply to this topic.