[OPEN-ILS-GENERAL] student downloads in Evergreen

[Soulliere, Robert]

Hi Gordana,
Here is how we import students into Evergreen at Mohawk College:
1) Our IT department produces a CSV export file of currently enrolled students at Mohawk from an Oracle database.
2) We ftp the file to the server.
3) We run an SQL transaction script to import the data into our postgresql database. The transaction script does the following:
               1. Creates a staging table  (A) with the data sent from IT.
               2.  creates a second staging table (B) with records from the first staging table. Then the script deletes the records in B which already exist in evergreen.
               3. Adds new fields to  table B  and reformats the data in other fields to fit into the design and format of the evergreen tables.
               4. INSERTS the new records from B into the actor.usr table.
               5. INSERTS new records from B into actor.card table.
               6. UPDATES the actor.usr.card field to match the actor.card.id for the users.
               7.  INSERTS records for new users into the actor.usr.address table.
               8.  UPDATES addresses for all users.
               9.   UPDATES the actor.usr tables to link user to address.
This is probably not the most efficient way to accomplish this, so I expect the professional database administrators to chime in here.
Other important notes:
1. We have a strict one card for one user so this process may not work for organizations or consortiums which may have more than one card per user. Input from users in a consortium environment might be useful here.
2. We do the patron updates a few times at the beginning of each term  not on a daily basis. We try to time it so that we get them loaded after most have registered. Importing students on a daily basis seems like overkill.
3. We run this manually, but this process could be automated to automatically ftp the csv file to a specific location and then run the sql script in a night time cron job.
If you want more details let me know.
Robert






From: open-ils-general-bounces at list.georgialibraries.org [mailto:open-ils-general-bounces at list.georgialibraries.org] On Behalf Of Gordana Vitez
Sent: Tuesday, April 13, 2010 10:12 AM
To: open-ils-general at list.georgialibraries.org
Subject: [OPEN-ILS-GENERAL] student downloads in Evergreen

Hi everyone,

Niagara College is getting ready for migration and I have a question for other academic Evergreen sites. How do you handle student downloads? The College uses PeopleSoft and we have been doing daily downloads of student information.

Has anyone else figured out a way to do this with Evergreen?

Thanks!
Gordana

Gordana Vitez
Library Services & Systems Coordinator
Niagara College Libraries
Welland Campus
300 Woodlawn Rd
Welland Ontario
L3C 7L3
Phone: (905) 735 2211 ext 7404
Fax: (905) 736 6021
gvitez at niagaracollege.ca

________________________________
This E-mail contains privileged and confidential information intended
only for the individual or entity named in the message. If the reader
of this message is not the intended recipient, or the agent responsible
to deliver it to the intended recipient, you are hereby notified that
any review, dissemination, distribution or copying of this communication
is prohibited. If this communication was received in error, please
notify the sender by reply E-mail immediately, and delete and destroy
the original message.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20100413/cd8d66a3/attachment.htm