Using the Core>Import Screen

This screen lets you import or update data in Genesis from a Microsoft Excel File. Not all tables in the system are available on this screen.

The tables that are available are tables that we felt were safe to load data into; and where a mistake would not affect overall system stability.

If there is a table that you would really like to have available; open a JIRA and let the developers know about it.

Process Overview

Loading data into Genesis is an easy 5 step process from Core>Import.

1. Select the table you wish to load data into.
2. Download an Excel template file.
3. Remove excess columns from the template Excel file.
4. Enter data to be updated / added to the table into the Excel file.
5. Upload the template file with the data filled in.

Getting a template Excel file

• On the Core>Import screen; select the table you wish to work with.
• Click the "Click here to download template XLS" button. An Excel file will be saved to your desktop with the filename "table-template.xls" where table is the name of the table you chose.
  
  

Working with the template Excel file

There are two tabs in the spread sheet.

The first tab called Template is where you will enter the data that will be loaded into Genesis.

The second tab called Columns describes in detail the database table.

Columns Tab:

• Primary Key: Any Column where this is "YES"; must exist on the spreadsheet when you upload it back to Genesis. Data in these columns are never changed; they are used to locate the row in the database that is to be updated.
• Column Name:This is the name of the column on the Template tab.
• Field Type:This is the kind of data that is expected to be in the column. STRING means text.
• Size:This is the maximum size in characters that this field contains. Extra data will cause the upload to fail.
• Required:This column MUST exist on the spreadsheet when you upload it back to Genesis, if you are updating that field. (If you are not updating that field, you can/should remove it entirely from the excel file.)
• Remove any columns from the spreadsheet (except the primary key columns) if you are not updating that field.

How the spreadsheet is processed when uploaded back into Genesis

• The primary key columns are used to locate an existing row in Genesis. 
  • If a row is found; the remaining columns in the spread sheet are assigned to that row in Genesis.
  • If you do not want data changed in a particular column; simply remove it from the spread sheet; and that column will be preserved in Genesis. Only columns that are not required can be removed.
• If the row is not found in Genesis; a new row will be created. There is an option on the screen to make sure that rows are never created if they are not found in Genesis. 
• A common issue when uploading data to existing data is to a "Cannot insert NULL into <column> message." This usually means that Genesis is trying to create a new row and all of the required information is not there.
• The entire upload is attempted in one database transaction. If any errors occur during the upload; all changes are rolled back as if nothing was uploaded. 
• 
  
  

Having trouble importing a spreadsheet with a Date?

If you are getting this error message, regarding one of the columns in your template that represents a DATE field:

"Cannot get a NUMERIC value from a STRING cell"

Please see the note above about formatting as 'Date' cells (ex: 31-Mar-1970).

If you cannot re-format the date in Excel, and it is not changing for you, please see this list of quick directions for use with Excel:

To Create New Rows or Not? How to Answer? 

There is a “Update existing rows only; do not create new rows.” that appears as part of the “upload spreadsheet” process.  

This basically means: Consider the table to which you are uploading data, are you just fixing data that is already in that table

Or are you adding completely new data to the table?

Completely new data usually requires new rows. Fixing existing data does not.

For example, consider Addresses:

• When you are adding addresses to a student’s record you are creating new rows in the Address table (do NOT check the checkbox).
• When you change address information (e.g. update Street Name) you are changing information in existing rows of the Address table (DO check the checkbox).

Should you check the checkbox or not? This depends on whether you are adding new data to Genesis or simply updating, correcting data that is already there:

• If you are adding new “rows” to the database, then DO NOT check the “Update existing rows only; do not create new rows.” checkbox. 
• If you do not want to add “rows” to the database, but want to change data in existing rows, then DO check the “Update existing rows only; do not create new rows.” checkbox.