Updating SIS Data by matching names

[Alden Thompson]

Hi guys,

I'm in the process of syncronizing our SIS with our Google and AD environment. An issue I've encountered when syncing student data is that only about half the students have their email in the SIS, or have it entered incorrectly.

I contacted the SIS support and they said you can bulk update student emails by providing a csv with 2 columns, email and student ID. The problem is, the student ID is one of the fields I was initially trying to sync over.

Right now I have two sheets, one downloaded from the SIS with First Name, Last Name, Full Name, and Student ID. Another downloaded with GAM from our domain's student OUs, with primaryEmail, First Name, Last Name, and Full Name.

I can't do a direct match across cells, because there's a different number of rows (my guess is because some students are not suspended even though they have been exited in the SIS).

Any ideas on how to find a match with the name, then copy the student ID over to that sheet and row?

[Gabriel Clifton]

Personally what I do is I have created a script that puts both CSV files data into variables and creates a third CSV combining the two.

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/104ffa76-5f95-42b2-8852-38ad05850c6fn%40googlegroups.com.

--

Gabriel Clifton | Network Administrator

Fort Stockton ISD | Technology Center
gabriel...@fsisd.net | http://www.fsisd.net
Office (432) 336-4055 ext 2

Fax (432) 336-4050
1204 W. Second St., Fort Stockton, TX 79735

Please note: Although we may sometimes respond to email, text, and phone calls instantly at all hours of the day, our regular support hours are 8:00 AM - 5:00 PM, Monday through Friday. We may need to wait until the next school day to attend to your issue. All issues are worked on a first-come, first-served basis depending on severity, and issues with proper work orders submitted are handled first.

Confidentiality notice: The contents of this email message and any attachments are intended solely for the addressee(s) and may contain confidential and/or privileged information and may be legally protected from disclosure. If you are not the intended recipient of this message or their agent, or if this message has been addressed to you in error, please immediately alert the sender by reply email and delete this message and any attachments. If you are not the intended recipient, you are hereby notified that any use, dissemination, copying, or storage of this message or its attachments is strictly prohibited.
"You must always be willing to work without applause."
— Ernest Hemingway

"You just have to find that thing that's special about you that distinguishes you from all the others, and through true talent, hard work, and passion, anything can happen."
— Dr. Dre

[Alden Thompson]

How do you get the data to match up? The sheet from our SIS has 526 rows and the sheet from our Google domain has 630 rows.

I would think that if I were to place the data in variables the student ID's wouldn't match up because of that discrepancy.

[Gabriel Clifton]

1. Read SIS CSV file and assign all needed columns into variables.

2. Take the variable or variable combination that would be used in Google CSV and search the second CSV for that variable.

3. Output needed data into a third CSV

Find one common variable that both CSV files will have that will uniquely identify each student.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/de1de247-e0b9-4323-9e04-1336d836735dn%40googlegroups.com.

[Alden Thompson]

I think I have it worked out, thanks for your help.

In powershell:

$object1 = Import-CSV -path C:\SISimport.csv -Header 'FullName', 'StudentDataID'
$object2 = Import-CSV -path C:\Googleimport.csv -Header 'primaryEmail', 'FullName'

$listOfNames = $object1.FullName + $object2.FullName | Sort-Object -Unique

$combinedObject = Foreach ($Name in $listOfNames){
    $object1Values = $object1 | Where-Object {$_.FullName -eq $Name} | Select-Object StudentDataID
    $object2Values = $object2 | Where-Object {$_.FullName -eq $Name} | Select-Object primaryEmail
    [PSCustomObject]@{
        FullName = $Name
        StudentDataID = $object1Values.StudentDataID
        primaryEmail = $object2Values.primaryEmail
    }
}

$combinedObject | Export-CSV -Path C:\csv3.csv

[Gabriel Clifton]

Great! Just set a log or something that will catch any students with duplicate names. We actually have a set of twins that have the same name. They have the same first, middle, and last names. They are twins so they have the same birthday. I had to create an exception for those students as they have different second middle names. They gave me hell for about two years for me to try to get them to work in all of my automation scripts.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/d0a1cfff-f82c-40e2-aa19-432d8bd7fc08n%40googlegroups.com.

[Paul McGuire]

I would take the export from your SIS and create their email address by using formulas to match your username standard.  This will work for 99% of your users.  Then fix the ones that are an exception to your username standard and import them into your sis to correct the issue.

[Alden Thompson]

That's the eventual plan, I need to work with them on getting an SFTP transfer set up so we don't have to manually download it each time.