While Data Services typically helps you to extract your data from Infinite Campus, you can dramatically speed up your implementation time by extracting Illuminate required data yourself. In this article, we'll use the Infinite Campus Data Extract Utility to create and send the files we require over to our servers. If you’re uncomfortable with the following steps, Illuminate’s Data Services team is always here to help.
In addition, click here to view a video available for all these steps.
To begin, you’ll need three things.
First, you’ll need administrative rights in Infinite Campus, and therefore access to the Data Extract Utility. Search for this link under your left pane. If you don’t have it, ask Infinite Campus or your administrator for rights to view this page.
Second, you’ll need your Illuminate SFTP credentials. We will typically send these to you via email shortly after you purchase the software. Otherwise, contact us to send them to you.
Third, you’ll need to download the Infinite Campus courtesy scripts here.
Extracting Core Data Files
We will be creating 9 extracts of Illuminate core data files using the Data Extract Utility tool. Unzip the courtesy scripts you downloaded above, then open up the "Core" file inside to see the 9 scripts.
Entering Extract Details
To begin we'll start with courses.sql. Find the Data Extract Utility page above. Click on the "New" button on that page to create a new extract.
- In the "Extract Instance Name" box, type in "IEcourses".
- In the "File Name" box, type in "courses"
- In "File Extension" type "txt"
- In "Column Delimiter" choose "Tab"
- In "Character Set" choose "UTF-8"
- In "Username" and "Password" type the Illuminate credentials we've sent you
- In "Server" type "sftp.illuminateed.com"
- In "Path", type in a forward slash and then your username. For instance, if your username is "ie_ic_sftp", type in "/ie_ic_sftp"
- Under "Delivery Mode" select "SFTP"
- Click the box next to "Include Header Row"
If you've typed in everything correct, your screens should look like the following:
Next, open up the courses.sql file in any text editor. Copy the entire text, and then paste it into the "SQL/Prism" box. This is the query that will pull the appropriate data from Infinite Campus.
Saving Extract Details
It is now important that you hit the "save" button. While not obvious in the user interface, the following steps won't work unless the Save button is clicked. After saving, your extract will show at the top. While it indicates a "Last Run" of "Export ran successfully", this is an error and we have not yet run the extract.
Validating Details and Testing Connection
Click back into the the extract by clicking on "IEcourses", then scroll down until you see the "Test Extract" and other buttons. Click on "Test Extract" and download and open the resulting text file. If you pasted in the query correctly, this file should be fine, so you're only doing a sanity check. Does the file have a header that resembles information about courses? Does the file have data about courses? If not, check your query again.
Go back to Infinite Campus and choose "Test Connection". When you do so, you should get a message saying "Connected OK". If not, check your SFTP credentials.
Finally, choose "Run Extract". This will push the data over to Illuminate. You should get a "Extract Transmitted Successfully" message. If not, check your "Path" above, and make sure it starts with a forward slash and then your username.
Setting Up Automation
Next, under the section "Data Extract Schedule", choose a frequency of "daily". Choose a start date of today, and a time of "19:00", which will set the extract to run at 7pm local time. This assures that the daily data extractions are occurring at non-peak times. Hit save, then review the extraction. It should now have a scheduled frequency and look like the screen below.
Repeat the same steps for all of the other files in the Core folder. The steps are exactly the same, but just in case, below are screenshots of the settings for each file.
If you've entered all of the core files correctly, you should see the following nine files on the the extract list:
Now, we need to obtain historical data using a one-time extract. You'll find the appropriate courtesy scripts in the "Historical" folder. Unlike Core, there are only seven files here. courses.sql and student_portal_accounts.sql do not require accompanying historical data.
These extracts will be much like our Core data extracts, with a few changes:
- Adding "Historical" to the end of the "Extract Instance Name" to distinguish it from the core extracts.
- Adding "/INITIAL_SETUP" to the end of the "Path" to place them into a specific folder.
- Not setting up automation. The one push of "Run Extract" is enough to send over the historical data one time.
As noted above, you should complete the same steps for core to set up extract details, but add "Historical" to the end of the extract name and "/INITIAL_SETUP" to the end of the "Path". See below for a screenshot with the first file, enrollment_historical.sql. Note that the "File Name" does not change from before.
Then, be sure to save at this point or else the testing below will not work.
Validating Details and Running the Extract
Just like with core files, click back into the extract after saving, click "Test Extract" to sanity check and "Test Connection" to check the connection. Once again, you'll click "Run Extract". For these historical files, just clicking this option once will populate all the data we need. Note that these actions may take a while to process so be patient while they execute.
Save to go back to the extraction list, and you should see your historical extract without a frequency.
Do the same process for the other 6 historical files. For your convenience, we've placed screenshots below.
If you've completed all the steps above, you should now have 9 core and 7 historical files available in your data extracts. The historical files should not have a frequency scheduled. (IEcourses and IEenrollment are cut off in screenshot shown below).
That's it! If you've made it this far, nice work. Illuminate will review your imports and begin ingesting them into your site.