Combine arrays

PowerShell Help
1

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

2

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

3

Hi Curtis,

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

4

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

5

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.

6

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.

7

Lyn,

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

$array1 -match “123465”

Josh

8

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

9

try to look into these topics

comparing-two-multi-dimensional-arrays/

compare-excel

10

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
}
11

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.

12

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

13

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

14

Lyn,

if you have a moment you should read this: How to Format Code in the Forums