Apple School Manager - Roster.csv help

Here is what I have learned from our rep.

1) Courses
Course are the generic group: Algebra III - North High School

2) Classes
The specific class: Mr Smith Algebra III 5th period

3) Roster
Each student that attends each class: Roster ID - Class ID - Student ID
Since the Roster ID is unique, a student would have a row for each class they are taking. A student in high school may have 7-8 rows. A class may have 30 entries, one for each student.

Unlike the previous cvs files, think of these are more filling the tables of the relational database behind the scenes and not making entries in the database. If that makes sense.

Edit: Does not make full sense to me yet.
Edit Again: The support info on the Apple School Manager website its still for the old cvs files, the info has not been updated to the new method.

We're stuck at about the same spot and have a call in to PowerSchool about both the roster and the classes file. Mvillafuerte or anyone, how did you create the classes file from PowerSchool? We're also anxious to see how to create the roster file.

Hi there,

We're sitting here with Mark breaking our heads over why in the world we need this RosterID and what could we use as such. But before I go into our plan, here's a response from James Thomas (Apple rep) regarding our question on Roster file:

I got some more information regarding the upload CSV’s for ASM. In regards to the rosters.csv file, you do not have to upload information here. I was able to confirm that not all SIS systems include this info, if yours does not you need only remove the default information from the template before uploading. When doing this the header must remain on the file. In regards to the information shared between different CSV’s, if the field identifier matches, the information should also match. So in the case of Location ID or Person ID these should be the same for the records represented. I hope this answers your questions, however if you require more information please let me know and I’ll be sure to see what I can find.

Even though it seems like Roster ID is not required, it kind of is - judging from the file specifications and the template. In PowerSchool, the table containing Student-Classes links is called CC, and each record in there has an internal ID that's unique and used probably in a gazillion ERDs connecting who knows what to whatever else. That means, if we build the Roster file with [CC]ID as RosterID, we'll have this many unique RosterIDs:
[Number of Students] x [Number of Classes in each Student's schedule]
Hopefully that RosterID is not going to be used on anything interface-related as that stuff is going to be unreadable by Teachers - they're definitely not used to seeing internal IDs of anything.

The question still stands though: even if we can get a unique ID for each RosterID line, what purpose does it serve? It definitely does NOT connect students to classes, so what in the world DOES it do in Apple Universe?

Unless Roster_Id is just a primary key for Rosters table - it's simply required (maybe even automatically assigned if imported blank, or something Roster table can't exist without in this system), but not used for any connections.

@strider.knh - Thanks for that info. I have two template zip files from the ASM website now. An older templates.zip that only included 4 csv files and had all help info within the csv file itself. A more recent ASM_CSV_Templates.zip that has 6 csv files and a separate readme. Is there supposed to be a new method coming?

@DLarson - We have not yet exported a classes.csv file but we did find all the values needed from powerschool.

@eoleksishina - Thanks for all the help!

I don't have my classes.csv or roster.csv so I tried removing the data but leaving the headers in both and uploading as a zip file with all the other files and still no joy, received error that the two files were missing (even though they were there with headers and no data as indicated by the Apple rep above) so it still eludes me how to successfully upload without these being populated. I should have a valid classes.csv tomorrow but will still need to deal with roster.csv.

@mvillafuerte I've found this to be the current (updated) method
[https://support.apple.com/en-us/HT207029](link URL)

I used the "old" method a few weeks ago, and now seemed to have duplicated the students.

Edit: I realised why the students have been duplicated, it's because I altered the person_id field, rookie mistake. Now I have to wait 29 days for the initial entries to be deleted, All of them are being imported into JSS, so I'm creating an advanced search to filter out the old ones and work with the new ones.

@mvillafuerte
The Roster_ID is the unique key for that table. The reason it is there is that the next time you want to make changes it gives ASM something to compare against. If that Roster_ID is in the old table but not the new table it is removed and that student is no longer in that class.

This will get confusing I think because you will need to make sure you use the same numbers each time or just expect the whole class list to be rebuilt each time.

These six cvs files is the new method and now requires the use of a SFTP site. We have not actually uploaded anything using the new method yet, still working on building the cvs files properly.

@cdenesha - Thanks for that link! This was really helpful.

Thanks to everyone else for the links and helpful info! I think we are on the right track now and will be trying our first export next week.

@Wheelspinning How badly messed up are the Managed Apple IDs being created? Or are you not using them? What happened in the JSS for it to create new users when you had old ones with the same usernames? Did it change the username structure? Unless you are matching on Source System Identifier, and then I have no idea what it would do.

If you want to cleanup all the old users and retain the new, I have a cleanup suggestion for you if you are confident in your import matching.

In Casper delete all your Classes from your earlier imports. This will remove the dependencies for the old users and allow you to delete them.

Here's the big nugget I learned in another thread: when ASM syncs it only does automatic user updates for students who are in Classes. I suggest Importing your Classes at Mobile Devices -> Classes first. Then don't manually Import users on the User side at all but instead allow the ASM sync to create/match all users.

chris

I think the Roster_ID should be a user created entry, concatenated fields to build a unique name, that you can find later. When you look at the way information is shared amongst the CSV files, you notice some fields are not interconnected with the others.

I've been playing with a nomenclature as follows:

16-17_HPT-Davis-K-4HR-576-202-25343

or

SCHOOLYEAR_SCHOOLACRONYM_TEACHERLAST-TEACHERFIRSTINITIAL-COURSENUMBER-COURSEID-SECTIONID-SECTIONNUMBER

Probably will trim it up to:

SCHOOL_YEAR-SCHOOL_ACRONYM-TEACHER_LAST-TEACHER_FIRST_INITIAL-COURSE_NUMBER-COURSE_ID-SECTION_ID.

Looking like this: 16-17_HPT-Davis-K-4HR-576-202

Granted you have to a little extra work an export an extra field or two out of SIS.

Still a work in progress.

UPDATE: I updated my ASM to PS data flow Picto-gram, see below.

Whereabouts are you seeing the error message from ASM when you upload a file? I am uploading using cyberduck, and all I can see is error - but no reason why.

Thanks

@jgwatson I use cyberduck as well, the zip file has to go in the Dropbox folder. If there is an error after the upload, you'll get an email from ASM.

@cdenesha my account is actually very new, only activated in the past two weeks. I don't have any user data or classes setup. Don't even have any iPads setup yet but have 100 with DEP ready to setup once I get this part working. I've been mass deleting after a sync and then starting over for the time being. During the import, JSS shows the person_id field but then it doesn't show up for the students or anyone when I click on a contact.

The managed Apple IDs got screwed up in ASM, where each user has fullname@appleid.schoolname and fullname-1. The -1 users are the new ones via the new process and actually linked to classes and everything. I may need to manually change 650 original users apple ids to something bogus and then schedule them for deletion.

@jbutler135 Thanks again for your diagram!

Our PS person said that when a nightly export happens you cannot do a Where clause and that everything in the DB would be exported for those fields - including classes from previous years. Can you shed some light on how you'll be exporting? And do you expect to do some post processing of the data files?

thanks!

chris

Did my first upload today and got tons of error messages that the class_id in rosters.csv was not found in classes.csv.
Not for one particular line but every line in the rosters file. Yet, I can open each file and find various class_id's in both files. When I look at the formatting requirements it makes me wonder if I'm way off.
For example, here's the header and first line in rosters and classes.

roster_id,class_id,student_id
bbb3b58e-7d34-4700-92dd-6931148da8ea,500101,42200621

class_id,class_number,course_id,instructor_id,instructor_id_2,instructor_id_3,location_id
050501,050501,0505,ST100,,,100

Just reading https://support.apple.com/en-us/HT207029 and it says

"The Read Me file included with the templates says that values must be wrapped in quotation marks (“). This isn't necessary unless the value contains a comma (,) or semicolon (;)."

So maybe it's not formatting. I'm still not seeing why the class_id are not matching between rosters and classes.

Ugh

Your files look ok, you shouldn't need the quotes. Are you sure there is a class_id of 500101? I don't think it would be s leading zero issue.. did you try wrapping in quotes?

So let me ask this about the classes & roster and maybe one of you brilliant folks will have some insight: Do I REALLY have to list every class separately?

We are a K-8 school, so for K-5, I'm listing their one homeroom class (K1, 2A, etc). For middle school, can I just list their section? We split the kids in half, so we have 6-1, 6-2, 7-1, 7-2, etc. Kids in those sections go to the same classes together. Is there a reason I have to break it down into 6-1Math, 7-2English, etc?

I guess I'm not fully understanding the purpose of having to import all of this and how it will affect my students or my ability to manage devices. What am I missing?

You don't have import any of it if you don't want to or need to. We are just importing our students to create managed Apple IDs. When I spoke to an Apple Enterprise Support Engineer I found out that a # can be used to populate any required field in the csv file that you don't want to upload data for. So for example, our staff.csv file has the header row and then a # in row 2 for each of the required fields. The same is true for the rosters, courses, and classes files.

Seriously, @Ktrojano ? If that's true I am so darn thrilled. With all the time I spent on the phone with Apple tech support yesterday, that was never presented to me as an option. I am so glad to hear this. You just gave me back at least an hour of my life this afternoon. Gracias!

@cdenesha Yes, that's the odd thing I can open the two files and point to the class_id in each file. I'm going to bang away on it some more.

@Ktrojano That's a great tip with the #. Thanks!

Normally optional fields can be just empty, i.e. you'd see two commas in a row since there is no data. I did a test import where I did this for the password type and it wasn't rejected.

Finally got my students, staff and courses into ASM. I did get a suggestion that having my class_id and class_number the same value could cause the error. I changed the class_number this time it worked.

Oh yeah... importing it into the JSS. I nearly forgot. Tomorrow is another day. Thanks for everyone's help.

@Olson I thought I posted this last night... just take it one class at a time at first, from Mobile Devices -> Classes -> Import.