Standard Import Details

This guide involves advanced concepts and assumes a basic knowledge of SQL and the Maytas database.

The Standard Import routine can use a control file which describes the format of the comma-separated import file being used. The control file allows each column in the import file to be mapped to fields of tables in the database.

Control File Format

The control file is a text file with file extension .con, comprising a list of instructions on how to map the data in the import file to the columns in the database.

For example:

ImportType=TRAINEE

Table1=TRAINEE

Table2=TRAINEEPOT

Field1=TRAINEE.NINUMBER

Field2=TRAINEE.LASTNAME

Field3=TRAINEE.FIRSTNAME

Field4=TRAINEE.ADDRESS1

Field8=TRAINEE.POSTCODE

Field9=TRAINEE.DOB

Field10=TRAINEE.GENDER{Male=M,Female=F}

Field11=TRAINEE.TELEPHONE

Field23=TRAINEEPOT.LASTSCHOOL

Set TRAINEEPOT.SCHEMEID=14

The first line in the file identifies the type of data being dealt with. ImportType=TRAINEE indicates that the import file will contain trainee / learner data.

The next lines define the tables (see the Tables section for details) and fields used in the import, which tell the import program where to find the data in the import file to populate the fields in the learner tables. A record in the import file corresponds to a learner record.

The Field command maps a field to the column position in the imported data. For example, if your imported data has three columns, Forename, Surname and Age, Forename would be Field1, Surname would be Field2 and Age would be Field3. In the above example, the first column maps to NINUMBER in the TRAINEE table.

Field10 demonstrates a lookup that maps a value of Male in the imported data to a value of M in the destination field and Female to F. If the field has a lookup set up in Maytas, the import routine can use it to map full descriptions to short descriptions by appending {} at the end of the field name, e.g. TRAINEE.GENDER{}.

The control file supports import to any tables in the database. The import assumes that the CSV file is in a flat format structure where all the information for all tables is on a single line. This is easy to achieve through SQL as you simply need to list all the fields in the select of a multi-table join statement.

Child tables will produce multiple lines with the parent table fields being repeated.

Set Commands

The Set command can be used to globally set all records to a particular value. For example:

Set TRAINEEPOT.SCHEMEID=14

will set all trainees to scheme 14.

The # character can be used to indicate that only blank fields should be set. For example:

#Set TRAINEEDETAILS.POT=1

A field can be set to another field by using the FIELD: prefix.

A field can be set to the current centre and organisation using the THIS: prefix. For example:

Field7=TRAINEEINFO.U_DATE_JOINED

Set TREMPLOYER.STARTDATE=FIELD:7

Set EMPLOYER.Q_ORGID=THIS:ORG

will set TREMPLOYER.STARTDATE to TRAINEEINFO.U_DATE_JOINED, as it references Field7. It will also set EMPLOYER.Q_ORGID to the current ORG value.

Import Actions

The ImportType can be set to a table name that will act as a master table for the import. For example:

ImportType=TRAINEE

By default, the import will only process new records, but other options can be achieved by appending a semi-colon and a letter to the end of the ImportType. For example, adding ;A will allow existing records to be modified as well as inserting new records:

ImportType=TRAINEE;A

Import Action	Comment
I or blank	Only insert new records
A	Insert new records and update existing ones
U	Only update existing records
E	Error on not finding records for updates so the changes can be rolled back
G	Ignore SQL errors from inserts so the import can continue

Tables

A simplified table tree structure can be set up for the import which will dictate the order in which data is transferred to the tables. This is important where tables have master details relationships and the master table must be transferred first. The example below shows a structure for a learner where the TRAINEE table is at the top level, followed by the TRAINEEPOT, TRAINEEINFO and TRAINEEDETAILS tables.

Table1=TRAINEE

Table2=TRAINEEPOT

Table2=TRAINEEINFO

Table3=TRAINEEDETAILS

Search Fields

Fields can be defined as search fields by prefixing each line with an asterisk. These fields will be used to determine if the record exists in the database. If a search field is not defined, the primary key of the table will be used to search for existing records.

*Field2=TRAINEE.USERREF

Functions

You can use SQL queries with the FUNC:RunSQL option. Substitution of field values is achieved with {} as shown below. For example:

SET GENERICEARNERTIMPORTS.ILR_RESTRICTEDUSE=FUNC:RunSQL("SELECT LEFT('{Field:22}',2) ")

will select the two left most characters from Field22.

Skip If Blank (@)

The @ command can used to tell the import not to update a record if the value of the search field is blank. For example:

*@Set TRASSESSOR.ASSESSORID=PARENT:

will not update TRASSESSOR if no assessor was found.

PARENT

The value PARENT: can be used to reference primary keys found in tables defined higher in the tree structure (see the Tables section).

For example:

Table1=TRAINEE

Table2=EMPLOYER

Table3=TREMLOYER

*Set TREMPLOYER.EMPLOYERID=PARENT:

*Set TREMPLOYER.TRAINEEID=PARENT:

will set the EMPLOYERID and TRAINEEID values on the TREMPLOYER table to EMPLOYER.EMPLOYERID and TRAINEE.TRAINEEID respectively.

Example Control File

ImportType=TRAINEE;A

Table1=TRAINEEINFO

Table2=TRAINEE

Table3=TRAINEEPOT

Table4=TRAINEEDETAILS

Table5=EMPLOYER

Table6=TREMPLOYER

Table7=ASSESSOR

Table8=TRASSESSOR

*Field1=TRAINEEINFO.U_PASSPORT_NO

Field2=TRAINEE.LASTNAME

Field3=TRAINEE.FIRSTNAME

Field4=TRAINEE.TITLE

*Field5=EMPLOYER.EMPLOYERREF

Field6=EMPLOYER.EMPLOYERNAME

Field7=TRAINEEINFO.U_DATE_JOINED

Field8=TRAINEE.TELEPHONE

Field9=TRAINEE.GENDER

Field10=TRAINEE.DOB

Set TRAINEE.ADMINCENTREID=THIS:CENTRE

Set TRAINEE.Q_ORGID=THIS:ORG

Set TRAINEE.Q_LASTPOSTED=NOW:DT

Set TRAINEE.Q_CREATED=NOW:DT

Set TRAINEEINFO.Q_LASTPOSTED=NOW:DT

Set TRAINEEINFO.Q_CREATED=NOW:DT

Set EMPLOYER.Q_ORGID=THIS:ORG

Set EMPLOYER.ADMINCENTREID=THIS:CENTRE

Set TREMPLOYER.EMPLOYMENTTYPE=E

*Set TREMPLOYER.EMPLOYERID=PARENT:

*Set TREMPLOYER.TRAINEEID=PARENT:

*Set TREMPLOYER.STARTDATE=FIELD:7

#Set TREMPLOYER.ENDDATE=31/12/2199

*@Set ASSESSOR.LASTNAME=FUNC:RunSQL("SELECT LEFT({Field:11},iif(CHARINDEX(',',{Field:11})=0,100,CHARINDEX(',', {Field:11})-1))")

*@Set ASSESSOR.FIRSTNAME=FUNC:RunSQL("SELECT substring({Field:11},CHARINDEX(',', {Field:11})+1,100) ")

Set ASSESSOR.Q_LASTPOSTED=NOW:DT

Set ASSESSOR.Q_CREATED=NOW:DT

Set ASSESSOR.Q_ORGID=THIS:ORG

Set ASSESSOR.ADMINCENTREID=THIS:CENTRE

*@Set TRASSESSOR.ASSESSORID=PARENT:

*Set TRASSESSOR.TRAINEEID=PARENT:

*Set TRASSESSOR.STARTDATE=FIELD:7

Set TRASSESSOR.ASSESSORTYPE=S

#Set TRASSESSOR.ENDDATE=31/12/2199

Set TRAINEEPOT.POT=1

Set TRAINEEPOT.EMPLOYEDATSTART=N

Set TRAINEEPOT.HADPREVYT=N

Set TRAINEEPOT.TRANSFERIN=N

Set TRAINEEPOT.Q_LASTPOSTED=NOW:DT

Set TRAINEEPOT.Q_CREATED=NOW:DT

#Set TRAINEEDETAILS.Q_START=FIELD:7

#Set TRAINEEDETAILS.POT=1

Set TRAINEEDETAILS.Q_END=31/12/2199

Set TRAINEEDETAILS.Q_LASTPOSTED=NOW:DT

Miscellaneous Features

Table and Field Tracking

Data imported via the Standard Import system is tracked and recorded in the same way that data updated manually in Maytas 5 is tracked. The tracking data can be viewed in the M32$_WEB_USERCHANGES view.

Multiple Recipients for Email Notifications

Email notifications for standard imports can be sent to multiple email addresses. This is set in a configuration option:

Click the M5 button at the top left and select Options.
Go to Standard Import on the left (in the Maytas 5 section).
Ensure the box for the Email To option is ticked, then enter all the email addresses to receive notifications, separated by a comma (e.g. user1@tribalgroup.com,user2@tribalgroup.com,user3@tribalgroup.com).
Click OK.

Special Characters

Standard imports can handle data with special characters (i.e. characters not compatible with UTF8 encoding) when an option is selected before the import:

Go to Exports on the toolbar and click Standard Import.
Tick the Use default encoding box. This will attempt to match the file encoding scheme for the imported CSV / Excel file.
The rest of the standard import can be completed as normal.