In CourseSales.com there is integrated support for importing enrolments (documents, and document topics) and clients (contacts) using spreadsheets. There is also support for importing configuration data (units of competency, etc) using other non-integrated software ie Selenium, a browser automation tool.

When adopting a new Student Management System (SMS) the biggest uncertainty is migration of existing data. Each SMS uses complex data models, they determine data relationships by using business rules at the point of data entry. Importing from another system may effectively by-pass these rules by mapping directly in the database. Often a subset of data and data relationships is sufficient and even then some manual data entry is required. It pays to be pragmatic about what data to migrate. Whether this information comes from spreadsheets, paper or an existing student management system the data will need to be validated and manipulated to be ready to be imported.

To ensure the connection between the records and the ability to run activities such as contact creation, sending emails and other items additional fields in each case are required. There are common features in the spreadsheet formats. The file must be in CSV format, encoded in UTF-8, and must include a header row, outlined below.

To export to UTF-8 from Excel, Google spreadsheets etc please follow these guidelines.

The Form fields should be in the format Fld1896-4-3428 where:

• 1896 is the Form Id

• 4 is a sequential number indicating the position on the form

• 3428 is the Field Id.

These formats can be found in the source HTML of the form, or request us to create a header file that includes the field name and the Formatted field ids.

Where there are multiple options on a field eg Checkbox list each is represented in the import in the format Fld3714-11-16040-410 which follows the standard form fields format but in addition includes the encoded value appended to the field, ie 410. These field names can be found in the source HTML of the form.

For all spreadsheets the field values to import should be the underlying data - not the names eg when importing the Outcome Identifier - National field the value ‘20’ represents the label ‘Competency achieved/pass’. the value ‘20’ should be used, not the label.

To test the import but not actually import any records include the phrase “testonly” anywhere in the file name. e.g. “docstestonly.csv”.

The volume of records that can be imported at any one time depends on the load on the server. Upon successful upload a file will be downloadable called ‘skippedDocument.csv’ this will provide the data and reasons why the row could not be imported. You will also be able to download a succeededDocument.csv file with all successful rows included. It is recommended that 1000 records are imported at one time.

Document import

1. Prepare the document spreadsheet header

The spreadsheet used to import documents contains fields from the form and some special fields that enable the linkages between documents, topics (units of competency) and actions to be carried out, eg send emails or create contacts.

The spreadsheet must include a header row with these fields:

• DocumentCourseDateId - the id of the Course Date that the Document should be added to

• DocumentFormId - the Form ID of the form that has the fields to be imported into

• DocumentProcessPathId - the Process Path that includes the Process Step (Status Step) that has the Form and should be actioned if StatusProcessStage is set to on

• StatusStepId - the Process Step that includes the form to import into and links to the Process Rules to be applied if StatusProcessStage is set to on

Optional fields that can be included:

• DocumentSourceId - this is the unique ID that links the documents to an external source eg Moodle, or used to map Document Topics (units of competency)

• StatusProcessStage - when set with a value of “on” it will trigger Process Rules eg: emails to be sent and/or contacts created.

2. Create the document import spreadsheet

This assumes you already have a header row as mentioned above. If supplied by CourseSales.com you will also have a top header row of the field names to make mapping easier. This top header row with field names (included in the example below) should be removed prior to import.

For all spreadsheets the field values to import should be the underlying data - not the names eg when importing the Outcome Identifier - National field the value ‘20’ represents the label ‘Competency achieved/pass’. the value ‘20’ should be used, not the label.

Download a import document example now if you wish

If you are importing Topics it is important that a DocumentSourceId is included in the spreadsheet to match that on the document.

3. Import the document spreadsheet

The form should have all validation removed unless you need to validate the data you are importing, prior to the final import. Importing documents at the following URL:

https://<shortname>.coursesales.com/import/document

When importing do so firstly by adding ‘testonly’ into the file name - this will mean that the records are not actually imported however any error messages will be recorded for correction.

Import Documents before importing Document Topics, as the topics need to be added to documents. Contacts can be added after documents or before documents - they are not dependent on documents so could be imported even if no documents remained.

Document Topic import

4. Prepare the document topic spreadsheet header

The Documents, Modules and the Course Dates must already exist, and the Course Formats must have the information required to generate Course Topics, or the Course Topics must already exist. If the Course Topics do not exist then this process will create them. The file must include a header row with these fields:

• DocumentExternalId OR DocumentSourceId. The DocumentSourceId is used to link documents to source systems like Moodle.

• docTpcFrmId - the Document Topic Form Id

• OptionId - the Module Id

5. Create the document import spreadsheet

This assumes you already have a header row as mentioned above. If supplied by CourseSales.com you will also have a top header row of the field names to make mapping easier. This top header row with field names (included in the example below) should be removed prior to import.

For all spreadsheets the field values to import should be the underlying data - not the names eg when importing the Outcome Identifier - National field the value ‘20’ represents the label ‘Competency achieved/pass’. the value ‘20’ should be used, not the label.

Notice that it may be necessary to import more than one document topic per document. This is because there are often more than one topic per document.

Download this import Topic example if you wish:

6. Import the document topic spreadsheet

The form should have all validation removed unless you need to validate the data you are importing, prior to the final import. Importing documents, Document Topics and contacts can be performed at the following URLs:

https://<shortname>.coursesales.com/import/documenttopic
https://<shortname>.coursesales.com/import/contact

When importing do so firstly by adding ‘testonly’ into the file name - this will mean that the records are not actually imported however any error messages will be recorded for correction.

Import Documents before importing Document Topics, as the topics need to be added to documents. Contacts can be added after documents or before documents - they are not dependent on documents so could be imported even if no documents remained.

Contact import

7. Prepare the document topic spreadsheet header

The file must include a header row with these fields:

• ContactTypeId - defined as an option

• ContactExternalId1 - depends on how you are identifying contacts

• lfeCyc - this is the status id of the contact, ie Active, Draft or Inactive

• ContactFormId - the form id of the form that has the fields to be imported into

8. Import the contact spreadsheet

The form should have all validation removed unless you need to validate the data you are importing, prior to the final import. Importing documents, Document Topics and contacts can be performed at the following URLs:

https://<shortname>.coursesales.com/import/documenttopic
https://<shortname>.coursesales.com/import/contact

When importing do so firstly by adding ‘testonly’ into the file name - this will mean that the records are not actually imported however any error messages will be recorded for correction.

Import Documents before importing Document Topics, as the topics need to be added to documents. Contacts can be added after documents or before documents - they are not dependent on documents so could be imported even if no documents remained.

Hints and tips

AVETMISS data imports

AVETMISS is a standard data import used by NCVER, the Australian organization tasked to gather regulatory data for the government. The following tips will help when importing this data

Here are some tips:

• You need to use the underlying data for drop downs and radio buttons, eg Male or Female require the values M or F

• You may import more than one Form Id in a file, but you will need to make sure the set of fields covers both forms. In practice it would probably make sense to do one Form Id at a time.

• DocumentSourceId; You can use this to keep a historic id. However it is meant to be used with DocumentSource - currently the only valid external system in this field is Moodle.

• Validation such as email address format, minimum and maximum values and the mandatory nature of fields is used as part of the internal import feature. For this reason if the fields on the form are mandatory the import will not import those rows that do not include those fields.

• Including the phrase testonly in the file name will simulate an import and check the structure of the data, ie are the correct fields included, do they match etc. It will also check if the data will be accepted by the form fields (validation). It will NOT check if contacts will be created, these might be stopped by validation issues - you are best to confirm that the contact fields match those on the document form, eg field type ‘text’ where possible.

• Be aware that contacts must have the same validation as the document form being imported, changing from a autopopulate may lose data if transfered to a lookup field (as the lookup does not have a ‘blank’ option), a text field used to receive dates may lose data if imported into a date field, ie when @@@@@@@ (see below)

Field specific tips (AVETMISS et al)

• State Identifier - although the identifiers are padded with ‘0’ the import requires single digits, eg 1 for New South Wales, not 01

• USI - the same validation applies when adding the values via a form, so 10 digits, capitalised and no use of 0,1,I or O. If absent leave blank. Should you have the USI as a mandatory field you may wish to add a dummy set of values (assuming you have the given and last names): Date: 1/1/1900 and USI with GGGGGGGGGG

• Dates - The date must be in one of these formats: 5 Dec 2015 or 2015-12-05, if you enter @@@@@@@@ (the ‘non-specified’ AVETMISS value) and that value is then copied across to a contact record it may error (and the contact will not be created) as @@@@@@@@ is not a valid date value. You could either make the date field a text type (and republish the form) or import no value for those that a not specified (ensure the date field is not mandatory on the contact or document forms).

• Importing checkboxes - each checkbox is treated as a separate field, meaning that each requires its separate column and should be the value on or off, for example the field ‘Prior Educational Achievement Identifier’ should have the following fields (only the first three shown for brevity), note that the increment in the example does NOT change, ie 15 for each checkbox relevant to the field:

  • 0 None eg. column heading: Fld2714-15-12540-0
  • 008 Bachelor degree or higher degree level (defined for AVETMISS use only) eg. column heading: Fld2714-15-12540-008
  • 410 Advanced diploma or associate degree level, eg. column heading: Fld2714-15-12540-410
  • 420 Diploma level, eg. column heading: Fld2714-15-12540-420

This import will process a step and therefore rules, including the creation of a contact and sending of emails, if the configuration work like that. Keep in mind that this imports a lot of records all at once, and the processing of steps will occur over time, as the system get to them - depending on the number of records it could take hours to complete the full processing although the import is likely to be just minutes.

Importing price

If the price is 0 importing will default to the CourseDate price at the time of import (if you wish to import a price of 0, this must be the price on the course date).