ADAM

Importing and Exporting Data

ADAM can import and export data directly from a number of tables in the database using CSV (Comma Separated Variable) files. These files are easily edited and manipulated in Excel which can then be imported into ADAM.

CSV Export

The CSV Export is a straightforward process to empty the contents of the table into a file that can be edited locally. To export the contents, find the export feature under Administration → Database Administration → Export Data to CSV.

In the page that follows, choose from the tables that are available to you:

You can also tick the box which includes old (deleted) records for editing. In the case of pupils or staff, for example, ticking this box would include alumni or past staff members in the export. Otherwise, ADAM exports only current pupils and staff members.

A:\Users\Philip\AppData\Local\Microsoft\Windows\INetCache\Content.Word\big_warning_11.pngIt is important to realise that using the export and import features has the potential to corrupt your data very easily. Please ensure that you have a backup first to restore your data if something goes wrong.

CSV Import

Importing data from CSV can be a great time saver is there is lots of information that needs to be changed. ADAM can import into any of the tables that are mentioned above. To import data, navigate to Administration → Database Administration → Import Data from CSV.

A:\Users\Philip\AppData\Local\Microsoft\Windows\INetCache\Content.Word\big_warning_11.pngNote that while working on data, perhaps from an export from ADAM, it is sensible to save this file in Excel format (Excel provides a warning when you attempt to save the file) until such time as you are ready to re-import the information into ADAM. At this point, save a copy of the file as a CSV file. This will enable you to revert to editing the file, if you need to, without losing number formatting and so on.

A:\Users\Philip\AppData\Local\Microsoft\Windows\INetCache\Content.Word\big_warning_11.pngPlease also be aware that “Regional” settings in your operating system may affect how the CSV files are saved. Please take special note of this. If your computer is saving the CSV files by separating fields with a semicolon instead of a comma, please use the appropriate setting on the import page.

Updating vs Adding vs Deleting: Learning about the identifier column

In each of the imports, please note the value of the first column carefully! This column usually ends in “_id” and represents an internal identifying number for each row. This first column must always be present in your import.

ADAM uses this first column to determine whether it should update an existing record (with the matching internal identifier) or create a new one.

Generally speaking, these identifiers must not be changed. Changing a value inthe first column may result in loss of information. The internal identifier cannot be changed, regardless of whether it is a pupil, parent or staff member.

If the identifier column is left blank, ADAM will assume that the record is a new on and will add the new record to the database. For this reason, import files that contain new information should only ever be imported once. To make further changes to the data after an import, you will need to do an export first which will contain the new identifiers for the records you’ve just added.

A:\Users\Philip\AppData\Local\Microsoft\Windows\INetCache\Content.Word\big_error_03.pngDo not attempt an import of the same file more than once if it contains new records – you will end up with duplicated data!

With the exception of the first field which must always be included in every import, all columns can be safely deleted from the import file. If you do not plan on making changes to the data in a specific column, it is advisable to delete it before you import the data. If you are updating email addresses, for example, you should remove all columns except for the first column and the appropriate email column.

Omitting a column will not remove data.

You can also savely leave out any rows that you don’t want to change. If you only need to update information about the Grade 8s, for example, you can filter and delete all other pupils from the CSV import file.

Excel and CSV Imports

Please be aware that Excel attempts to automatically assign appropriate data formatting to the CSV file if you open it normally. This is problematic!

Specifically, please take special note of any telephone numbers which will be interpreted as being numeric values and thus will have any leading zeros and “+” modifiers stripped from them. ID numbers, because of their length, are represented in floating point notation (“7.911E12”) and various other problems. The dates will also be represented using your system date format. If this is American (MM/DD/YYYY), then you will certainly run into errors on the import as the dates will not be properly undersood by ADAM.

Mostly, we advise users to delete these columns from the import file and avoid the problems they might cause that way. However, from time to time, it is necessary to modify these columns. Luckily Excel does have feature that allows us to specify how it should treat the data.

The following instructions and screenshots use Excel 2013, but the general principals still hold for newer versions of Excel. In fact, the internal workings haven’t changed visibly in the last 15 years.

At this point, Excel will add the data to the spreadsheet for your manipulation.

Note that Excel will reformat all dates to appear in your computer’s configured date format. When importing back to ADAM, it is always safest to have your dates formatted in the ISO standard format: YYYY-MM-DD.

To do this in Excel, click on the column header that contains the dates, then right-click on the selected column and choose “Format Cells”:

In the options that appear, click on “Date” and then find the “YYYY-MM-DD” option in the list:

Finally, click on “OK”. The dates should now show the new formatting:

Notes on Pupil Dates ← VERY IMPORTANT!

One of the most confusing thing when importing data into the database, particularly when adding new pupils, are three very important dates:

If any of these fields are omitted or pose problems to the enrolment procedure, ADAM will make the following assumptions, in the following order (since some omissions will have run-on implications!)

  1. If a pupil_entry date is omitted, ADAM will assume that the pupils will start on January 1 of the next calendar year. Note that if the server is in roll-over mode when the import is done, it assumes that the year is already the next year and thus the pupils will be assumed to arrive in the year following that one.
  2. If a pupil’s matric year (pupil_final) is omitted, ADAM will calculate a matric year based on the assumption that they are entering the school in the lowest grade that the school offers.
  3. If a pupil_exit date is omitted and the pupil’s current grade indicates that they are no longer eligible to be in the school, ADAM will calculate the exit date to be on 31 December of the year that they were in the school’s highest offered grade.
  4. Finally, a check is done to ensure that the pupil does not enrol into the school into a grade that is not offered by the school (e.g. the pupil is currently in Grade 1, has an entry date for next year (into Grade 2), but the school only starts from Grade 8). In this case, the enrolment date is adjusted to reflect the date that the pupil would be in Grade 8.

Without fully understanding these implications, ADAM’s import procedure may seem unpredictable and erratic!

Field Information

Covered here are the three most important tables that require imported data, and an explanation of each of their fields. Some of these fields require “codes” to be entered in instead of actual values. Where appropriate, these codes are provided later in this document in The Appendices.

In each case, we indicate which fields are required and which are optional. These are for NEW entries and to ensure data consistency in the database.

Pupil Information

Absentees

Absentee data can be imported via CSV. The following fields must be provided in the import file:

The following is an example of a file used for import:

Finding the reason_id values

Navigate to Administration → Absentee Administration → Edit the absentee reasons. Click on the edit option next to each and notice the address bar end with the text &id=.... The digits that are shown there represent the id number of the reason. This is illustrated below, where the reason_id value is set to 1.