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.pngPlease pay careful attention to the formatting of numbers and dates in Excel before saving and importing the CSV file. Excel will do things such as reformat ID numbers, drop the leading 0s from phone numbers and so on. It is strongly suggested that, once you have downloaded your export, that you make a copy of this file before starting to edit the contents. In this way, you can always revert changes by importing the file you exported.

Structuring your import file

A CSV import file is very quite easy to structure. There two features that every import file must have.

The first row of the import file must contain field names so that ADAM knows which fields are being updated in the file.

The first column of the import file must contain an “id” field so that ADAM knows which record you are updating. For example, if you are updating a pupil record, the first column will be pupil_id.

More information about the fields can be found below. It is often a good idea to start with an export of the records that you want to update. The export will allow you to start with the correct structure and the correct field names.

Updating vs Adding vs Deleting: Learning about the identifier column

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

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 any data. By omitting the column from your import you are ensuring that ADAM makes no changes to any values in that column.

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.

It is advisable to remove any columns (except the first “id” column) that you are not making changes to in order to minimise the amount of damage that can be caused by an import. For more information on this, see Excel and CSV Imports to learn how Excel can “damage” your data in its quest to be “clever”.

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


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.