We are working with our local school district and setting up all students with a student library card. Our bookmobile will be visiting the schools so the students can check out books and use our electronic resources with these cards. I have the Importing Student Records as Patron Records guide but would like to know more about the format. Specifically, what date elements look like. If anyone has a sample I would love to see it!
Thank you!!!
Hi Trish -
ConnectED Polaris trn data format 2.9.docx (37.3 KB)
I’ve uploaded the instructions that I have for my local school district to create .trn files for Polaris 7.0. Some of the info. is specific to my system or the local school district, but you should be able to get the gist.
Let me know if that’s not what you’re asking for…
Jon
3 Likes
This is exactly what I am looking for, Thanks so much Jon!
Hi, Trish -
Jon’s description of the data structure is very complete. In case you’re interested in a library’s actual process, here’s what I do.
Use FTP to get an Excel file from the school system with the data for each student - name, address, email, age, etc. We use their school ID (with a 4-letter prefix) as the barcode on their account, so that’s in the school file as well.
Clean up the data: if the city name and zip code in the address of the student record don’t match an authorized pair in your Polaris postal codes table, there will be an error in the import, although it seems like Polaris usually creates the patron address anyway. Also, check the student data for any commas before you import it - when you save it as a comma delimited file (see below) actual commas in the data will mess everything up. I usually just replace any commas with spaces before importing.
I have an Excel spreadsheet (attached) with a couple macros. The columns of the spreadsheet match the data elements that Polaris requires. The first macro copies the data from the columns of the school file into the corresponding columns of the spreadsheet. The first row of the spreadsheet already contains the data that’s the same for every student. The second macro copies this data from the first row of the spreadsheet down each column. One twist with this latter process: The copy-down function in Excel will look for the last entry in a column and copy down that far - otherwise it copies thousands of rows. So in order to give each column a “last” entry, I create a dummy row after the last real row and just put an X in every cell, then run the second macro. Talk about kludgy! Anyway, once the spreadsheet is fully populated, I save it as a CSV, edit that with Notepad++ to change the commas to pipes and add the Polaris code in the first line, as described in Jon’s document. Then I change the file name as required by Polaris, and import it with the upload function, which Polaris has under “Bookmobile.” (!) I always choose to ignore errors and warnings, and get a report at the end of the import.
Once a year in September, I get a complete roster from the school system and import that, setting their expiration dates for 1 year hence. Then I find any student accounts whose expiration date is not a year from now and delete those. After that, updates can be just any changes or additions.
One thing to note - the very first element in every row is a code telling Polaris what kind of record this is - new, revised, etc. If you always use a 7 in this column (as Jon’s documentation points out) then Polaris will look for a matching record for each line - if it finds a match already in the database, it updates that record; if it finds no match, it creates a new record. This is helpful if you want to do periodic updates. The school system can send you a single file with all new or modified student records since the last update, and you can import them in one batch. Deletes have to be handled separately - I get the student ID numbers of student who have left the system, use those as barcodes with a SQL search to populate a record set, and then delete the records in a batch.
One last twist - Polaris (at least in our case) imports the new records with a “registered offline” block. Like with the self-registration block, this gets cleared when a staff member edits the record, but this obviously isn’t happening with these school accounts. The block will prevent the students from using some of our online resources, so I run a quick SQL after each import to fix that. The code is:
BEGIN TRAN
UPDATE Polaris.Polaris.Patrons
SET SystemBlocks = 0
WHERE
PatronCodeID = --<<-- put your patron code ID for the student accounts here
and
SystemBlocks = 512 -- this code number means "registered offline"
-- after running the above, check that the number of records modified is correct -
-- it should match the number of new patron records created by your import
-- then highlight one of these and run it:
-- rollback -- to cancel the changes
-- commit -- to make the changes permanent
Whew - it sounds insane, doesn’t it? It actually takes me about half an hour once a year for the complete import in September, then 15 minutes once a week for the updates, so it’s not too bad. If anyone has a better approach, I’d love to hear it!
Meanwhile, there is a suggestion in the Idea Lab that Innovative should really come up with a sensible way to import student data, which we should all vote for: https://idealab.iii.com/alwaysopen-polaris/Page/ViewIdea?ideaid=26159.
Bill
Bill Taylor
ILS Administrator
Western Maryland Regional Library
wtaylor@wmrl.info
student data - template - with macros.xlsm (508.8 KB)
2 Likes
Thanks Bill! This is really helpful. I am not a tech type person, but I do have resources! I agree there should be a more seamless process for loading patron files.
I’m fortunate in that I only have one school district to work with, and the district was able to take my .trn format instructions and come up with an export process that generates the .trn file for me. Once a week an update file is generated, and my contact drops it on an FTP server for me to download (and then delete).
For the yearly load-in my process is exactly the same as Bill’s – import a full file, and delete any student accounts that weren’t updated with a new Expiration Date.
For students who leave the district during the year, we decided to leave those alone. Those accounts get deleted after the next yearly import.
My Polaris is hosted and I don’t have write access via SSMS. We have to deal with the Offline Registration block differently. For the annual load-in, I open a ticket and have my Polaris Site Manager remove them via SQL. For the weekly updates, I have a scheduled report that generates a list of barcodes for student accounts that have the block. The report is emailed to a clerk who manually removes the block from each account. It’s usually fewer than 20 accounts to process. My contact at the district usually FTPs the file first thing Tuesday morning, and the scheduled report runs at 2pm, so I have about a 6-hour window to import the file if don’t want to have to manually run the report.
When everything is running right this takes me 5 minutes each week, and the clerk also probably 5 to 10 minutes.
2 Likes
Hi Bill,
I have a question concerning this (I work with Trish btw):
"One thing to note - the very first element in every row is a code telling Polaris what kind of record this is - new, revised, etc. If you always use a 7 in this column (as Jon’s documentation points out) then Polaris will look for a matching record for each line - if it finds a match already in the database, it updates that record; if it finds no match, it creates a new record. "
We have students who have both regular library cards with our normal patron barcode prefix and student library cards. If we use a 7, will that overwrite all students who have regular library cards with us rendering them invalid?
Hi, Pam -
That’s a good question! How does Polaris identify a matching record to update, as opposed to creating a new one? We also have a lot of students with regular library cards, and we’ve never had a problem, but I don’t actually know how Polaris defines a match. If it used just name and birth date, like the duplication detection when you register a new patron manually, it would be causing the problem you’re talking about, so I’m guessing it looks at barcode also, or maybe other things. I wasn’t able to find any information in the documentation, but like I say, it hasn’t been a problem for us.
Bill
We have been loading records for some time now.
The match is on the Barcode field.
When there is a match there is an overlay.
No match will generate a new record.
I don’t see the logs, but there may be an entry for duplicate detection.
I look for duplicates, we have one in our system that was not generated by a load.
1 Like
Just a thought if you haven’t explored Get & Transform in Excel, it is pretty magical once you wrap your head around it.
We had a member do a nice intro: IUG 2021 Virtual Presentation: Excel: Love it or Hate it? Tips & Tricks using Excel's Get & Transform
But there are also lots of other tutorials out there on the Internet.
Not that you want to reinvent the wheel, but if someone else is looking and thinking about it, it might be a good alternative option for them.