Importing and exporting

Contents

Exporting data
onCourse Export files
Copying and pasting into a spreadsheet application
Exporting from the list view
Examples of default Exports and their uses
Customising export templates
Creating a new export in onCourse
Other export types and options
AVETMISS export
Exporting to Adobe InDesign
OLGR CSV Export
Exporting to MYOB
Importing
Standard Imports available
Payment In Import
Contact Update Subscriptions CSV import

onCourse offers you a variety of ways to get data into and out of the system. The onCourse DSL (Domain Specific Language) that outlines the database objects and enumerations is available at https://www.ish.com.au/onCourse/doc/latest/dsl/

This chapter is recommended for Intermediate to Advanced users of onCourse who have a good general understanding of structured data and the onCourse schema.

Exporting data

onCourse Export files

Go to File > Preferences > Export templates to see the list of export files available in your onCourse application.

The list of exports available to you will include those supplied as part of the default onCourse application configuration and any additional custom exports you have created or have commissioned ish to create on your behalf.

Generally, export files that end with CSV (comma separated files) are designed to be opened, viewed and edited in a plain text editor or spreadsheet application. These are flat files containing columns and rows of data. Export files that end with XML (eXtensible Markup Language) are designed to be imported into other software applications and represent a relational data structure.

Each export displayed in this list has a Name, Entity, Code and Version Number.

The Name is a descriptor of the export and usually includes information about the export format type.

The Entity is the object in the database that the export is joined to, and will indicate which list view you will be able to find this export.

The Code is a unique identifier for the export and when supplied by ish as part of the default onCourse application will begin with 'ish'. When the export is custom, is should begin with an identifying acronym for the organisation. The last part of the code defines the export file type, for example, CSV or XML.

The Version number allows ish to automatically update the export, for example, to add additional fields as they are added to the database structure, when new versions of the onCourse application are deployed.

You can double click on the export record in the list to see the structure of the export file, what fields are included and what formatting is applied.

List view showing the exports available in onCourse

Copying and pasting into a spreadsheet application

In all onCourse list views, you can highlight a range of records to copy and paste into a spreadsheet application like Excel or Google sheets.

Begin by selecting a range of records in a list view, then choose copy from the edit menu and paste the data into your spreadsheet application.

The data you copy and then paste will come out in the same column layout and sort order as the window you copied it from.

If you have opened a very large list of data, please note that it may take some time for this list to load into your onCourse client memory. This is a process that runs in the background of onCourse, and not something immediately visible to you as a user. If you try to copy and paste from a list before the load process has completed, the copy process will not be successful.

Export Icon being shown in the contacts list view

Exporting from the list view

Like print reports, the list of available exports in the list view are those exports linked to the entity, or list view, you are viewing. In the example below the Contact window is open so the exports available are those linked to the Contact entity.

Each list view in onCourse has the ability to export data in a variety of formats. You can either highlight the records you wish to export and choose File > Import/Export > Export (tablename) or click on the Export (Share) Icon in the top right hand corner of the list view.

Export Icon being shown in the contacts list view

A dialog will appear giving you some choices:

  • Export template: This list allows you to choose from one of the exports joined to this entity. There will usually be at least one CSV and one XML option to select from.

  • Based on:

    Listed records: every record in the list you were just looking at. This is the result of whatever search you had just performed.

    Highlighted records: only those records you had highlighted in the list. This is the quickest way to export a few selected records, particularly when you need to choose them by hand from your query results.

Examples of default Exports and their uses

Example 27.1. Exporting class information to create a print brochure

Begin by opening the Classes window and running a query or otherwise selecting the range of classes you wish to export for the brochure.

From the export options, select 'InDesign Brochure XML export' and save the resulting file to a location on your computer. This default layout includes the information from the course general tab 'Print brochure description' and combines it with the class summary information including location, cost, start date and time and tutor. The information is grouped by the Subject tag hierarchy.

The XML file is designed to be imported into a publishing product such as Adobe InDesign and then customised however you wish with the addition of styles and images. The XML tags in the onCourse export can be mapped to styles in InDesign, allowing the data to be imported and dropped into a brochure layout as many times as needed during the production cycle process. The XML file created by onCourse is not designed to be opened or edited in a product like Word - it is designed for import into a specialist layout and design software tools. If you do not have software that can work with these files installed on your machine, you should be able to email the file to your design staff for them to work with directly.


Example 27.2. Exporting from the Class - CSV data

The Class list includes a number of CSV exports, some with specific purposes. The 'CourseClass CSV export' is a generic export that includes most of the class fields.

Available exports accessed via Class List

  1. Highlight the Class records you wish to export within the Class window.

  2. Go to File > Import/Export > Export Classes or select the export icon in the top right hand corner of the list window

  3. You then have the following export options to select from the drop down menu:

    • CourseClass CSV export - standard export of Class information

      Excerpt of standard Class export viewed via excel

    • Class Budget Summary CSV export - provides a Class per line summary of budgeted income and expenses against projected and actual costs.

      Excerpt of Class budget summary export viewed via excel

    • Class Outcomes CSV export - Report for student outcome results for date range so Colleges can conduct their own reporting and easily identify students with outcomes that are not set.

      Excerpt of student outcomes export viewed via excel

    • Extended Outcomes CSV - A report to show data like the AVETMISS NAT00120, but in a human readable format

      Excerpt of extended outcomes export viewed via numbers

    • CourseClass Sessions CSV - Export for class session information including enrolments, room allocation, seats available and tutors assigned

      Excerpt of Class sessions export viewed via numbers

    • Attendance CSV - Export for class session attendance showing who marked the roll (online or via the office) and when the attendance was last modified information

      Excerpt of Class attendance export viewed via Excel


Tip

When exporting the above class exports, the resulting CSV output file will have the same name in all instances: it will be labelled as 'CourseClassExport.csv'. You can then re label these export files to whatever you need.

Example 27.3. Exporting Invoice Information

onCourse enables users to export not only the invoice information, with each invoice reflected as one line within the resulting output file, you can also export the invoice line detail.

Available exports via the Invoice list

  1. Highlight the invoice records you wish to export information for from within the Invoice window.

  2. Go to File > Import/Export > Export Invoices/Credit notes or select the export icon in the top right hand corner of the list window

  3. You can then export either the invoices as a standard Invoice CSV (one line per invoice) or alternatively you can export the invoice line detail.

The following excerpts of the exported information as shown within excel, provides a comparison between the kind of information that each export generates within the resulting output file.

Excerpt of invoice export viewed via excel

Excerpt of invoice line export viewed via excel


Tip

When exporting the above two invoice exports, the resulting CSV output file will have the same name in all instances, it will be labelled as 'InvoiceExport.csv'. You can then re label these export files to whatever you need.

Customising export templates

There are a range of options for creating custom exports in onCourse. What you choose depends on your familiarity with database structures, queries and the Groovy language.

  1. Copy an existing export to make a new export file, and then make changes to the copy

  2. Create a new export from scratch

  3. Create a standalone export that is run manually, or an export that is triggered by a script to pass it certain pre-defined parameters

  4. Engage ish or Groovy developer of your choice to create a custom export for your business

onCourse exports are powered by a groovy based scripting language. These scripts run on the server and call the same groovy DSL that you'll find in the scripting chapter and available here: https://www.ish.com.au/onCourse/doc/latest/dsl/. You can use the full power and expression of the groovy language, and have access the entire onCourse schema. In short, you access the data to export from the variable "records" and just return from the script the formatted text to export.

There is a built in a CSV toolkit, so exporting to CSV is the most simple export type to create or edit without a great deal of prior experience. By default the delimited is a comma, but you can define a different delimiter like a tab as per the example below:

csv.delimiter = '\t'

records.each { AccountTransaction t ->
	csv << [
			"Date"          : t.transactionDate?.format("D/M/Y"),
			"Memo"          : t.transactionDescription,
			"Account Number": t.account.accountCode,
			"Debit Amount"  : t.amount.compareTo(Money.ZERO) > 1 ? t.amount.toPlainString() : Money.ZERO.toPlainString(),
			"Credit Amount" : t.amount.compareTo(Money.ZERO) > 1 ? Money.ZERO.toPlainString() : t.amount.toPlainString()
	]
}

Creating a new export in onCourse

The simplest way to begin this process is to find an existing export from the entity to copy and use as the basis of your new export.

For example, if I wanted a new export for Tutor data that started from the Contact entity, I'd choose the default 'Contact CSV export' as my starting point.

  1. Go to File > Preferences > Export templates and double click on export 'Contact CSV export'

  2. Highlight the text in the script field and copy and paste this into a plain text editor like Sublime Text (In a tool like Sublime you can specify the text is Groovy, so it will add colour coding to assist with the editing process).

    Viewing the export text in Sublime

  3. The first part of the export code

    records.each { Contact c ->  
    csv << [ 

    means that in this file, the letter 'c' will be used as shorthand for the 'Contact' object and the export will create a CSV file. For your export to work correctly, do not modify this header information.

  4. Go to the DSL documentation for the entity so you can see the fields, or instance methods names and values. For the contact entity, this information is available at https://www.ish.com.au/onCourse/doc/latest/dsl/ish/oncourse/server/cayenne/Contact.html

  5. Each row that follows defines the column name in the CSV export and where that data comes from in onCourse, and how it is formatted. For example:

    "gender"     : c.isMale ? "M" : (c.isMale == null) ? "" : "F",

    Means create a column labelled 'gender' and from the contact record (referred to in the shorthand 'c') take the value from the boolean isMale. If the value = true, export the character 'M', if the value is null (not set) then export a blank space, else export "F".

    In this example, the question mark character is the ternary conditional operator which allows for three arguments to be defined and is a shortcut way of writing an if...else statement in Groovy.

    For example, if you wanted to change these export values to Male, Female and Not supplied you can change this row to:

    "gender"     : c.isMale ? "Male" : (c.isMale == null) ? "Not supplied" : "Female",

    If the code was just

    "gender"     : c.isMale,

    Then export value would be TRUE when the contact was set to Male, FALSE when the contact was set as female, and blank, or null, when no value was set in onCourse.

  6. You can add additional rows to the export, or delete or re-order rows. Note that each row ends with the comma delimiter "," except the last row in the export file. For example, the contact export ends with the resume field value, so there is no comma after this row.

    			"workingWithChildrenCheckDate"	: c.tutor?.wwChildrenCheckedOn?.format("yyyy-MM-dd"),
    			"workingWithChildrenCheckRef"	 : c.tutor?.wwChildrenRef,
    			"workingWithChildrenCheckExpiry"    : c.tutor?.wwChildrenExpiry?.format("yyyy-MM-dd"),
    			"tutorResume"                    : c.tutor?.resume
    		]
    }
    					

    You can also see above that the export file ends with the bracket closure characters "]" and "}". It is important you don't remove these.

  7. If you want to access data from other objects than the entity defined in the export, you need to add joins to the export and structure your query in a way that matches the onCourse DSL and schema. For example, in the contact CSV file, some of the fields are from the student or tutor object. These joins are represented by the full stop character. For example, the Working With Children Check Reference number is part of the tutor object, so the join from the contact to the tutor is 'c.tutor.wwChildrenRef' which means the same as 'contact.tutor.wwChildrenRef'.

    			"workingWithChildrenCheckRef"	 : c.tutor?.wwChildrenRef,
    					

    In this example, the addition of the question mark character is a safe-navigation operator to avoid the export throwing a null pointer exception. Null pointer exceptions occur when the export can not follow the join you have defined because it doesn't exist (i.e. not every contact selected for export will be a tutor, so if there is a non-tutor contact included, adding the safe-navigation operator just means the export will skip past this field for that record.)

  8. If you have custom fields you wish to include in your export, you can use the custom field keycode to add these fields. You won't find these fields defined in the onCourse DSL documentation as they are custom to your database. For example, if you have a contact custom field with the keycode 'howDidYouHearAboutUs' you could add it your export as follows:

    "How Did You Hear About Us?"  :  c.howDidYouHearAboutUs?.value

    Refer to the Custom Field documentation for more information about creating custom fields in onCourse.

Creating and testing your export in onCourse

When you are ready to test your new export in onCourse, you can create a new export and copy your export code.

  1. Go to File > Preferences > Export Templates and click the + in the top right hand corner to create a new export.

  2. Name your export. This is what appears in the drop down when you run the export, so make it clear.

  3. Provide a unique keycode for your export. This should be in the format companyName.onCourse.exportName.exportType

  4. Join your export to the correct entity. The name must be spelt exactly as it appears in the DSL documentation with the correct capitalisation.

  5. Copy and paste your code into the body 'script' and save your export.

    Creating a new export in onCourse

  6. Once you have saved your export, it will now be available for you to test in the Entity you defined.

    Testing the new export 'Working with Children Check' in the contact window

  7. If you attempt to run the report and instead of generating the expected output, it shows an error in the window, then you need to adjust your export code to resolve the error, save the changes and try again. This may be a process you repeat a few times if you are new to creating and editing export files.

  8. When your export runs successfully, check the output is formatted as expected, and make adjustments to your export as needed. Please note: When opening CSV files in a spreadsheet application like Excel, Excel may reformat some of your data based on it's own rules. For example, data it thinks are numbers will often be reformatted, so the leading zeros from mobile phones numbers may be stripped . You should run your formatting checks in a text editor like Sublime or Notepad.

Other export types and options

AVETMISS export

To export your AVETMISS go to the file menu, select import/export and choose Export AVETMISS. Select the date range you wish to export and where you wish to export the files to, e.g. your desktop, and select proceed. The 10 txt files will then be created ready to be submitted to the government reporting agency of your choice. Easy as!

For more information about how the onCourse data collection and various AVETMISS export options work, see the AVETMISS chapter.

Exporting to Adobe InDesign

onCourse already comes with a template for exporting to Adobe InDesign.

  1. Open the class list view

  2. Use the searching feature to find the classes you want in your brochure. This might be one or a thousand classes.

  3. Choose "Import/Export" from the File menu and select "Export classes". Select the "InDesign Brochure" output format. Export this data and save it to disk somewhere. It will be an XML file, ready for InDesign.

Now fire up Adobe InDesign and import this file.

  1. FileImport XML

  2. Choose your XML file which you just exported from onCourse.

  3. Ticking "Link" will keep the InDesign document linked to your source XML so that changes to the XML flow through

  4. The structure of the data will now appear in the left column. You can drag any element into a text box in your layout and have the text automatically flow through.

  5. Use the "map tags to style..." menu item to map the tags against your paragraph and character style sheets. Once this is done you can save the mapping so that next time you create this document with fresh data all the styles are linked quickly and simply.

If you now spend a bit of time tidying up your stylesheets (both character styles and paragraph styles) in InDesign you can set up the whole process to be almost completely automated. Next time you export from onCourse, the data will flow right into InDesign complete with all the formatting you need. Just paginate your brochure, place in some pictures and you are done.

If you need to customise your export further, you can do this yourself if you have the skills in house, or engage ish to make the changes for you.

OLGR CSV Export

onCourse has an export that allows you to export student data into OLGR for the state of NSW.

Below are the steps you need to follow to do this:

  1. Open the 'Class' window and locate the RSA class you want to create the file for

  2. Single click on the chosen class

  3. Go to the cog wheel and select 'Find related > enrolled students'

  4. This will open the students from this class in a new contact window

  5. With the contact window at the front of your onCourse view, select File > Import/Export > Export contacts

  6. Choose the option 'NSW OLGR CSV export' and ensure the radio button listed records is shown

  7. Click export and choose a location to save the file and give the file a name that makes sense to you about which class group this data belongs to

  8. Open the CSV file in excel and use your current process to upload the data into OLGR

Exporting to MYOB

If your organisation utilizes MYOB as the key financial system, you can generate an export that is specifically designed for importing data directly into MYOB. There are 2 different pathways within the onCourse Client to access and generate the financial data export for MYOB.

Generating a MYOB Export via the Financial Summary Extract Menu

The steps involved in running this export are as follows:

  1. Go to Financial > Summary Extracts and select MYOB Export

    Selecting MYOB Export via Summary Extract menu

  2. Set the 'from' and 'to' date range for the next MYOB Export

    Select date range for MYOB Export

    Note

    The benefit of utilizing the Summary Extract path when exporting financial data, is that onCourse enables you to set the date range for the data that is to be exported. As well as remembering the date up to when the last data export was run.

  3. Click on the Exportbutton to run the export. Note that the resulting file will be generated as a text file.

    Extract of MYOB Export viewed via Microsoft Excel

Generating a MYOB Export via the Transaction Window

Unlike the approach of generating the MYOB Export via the Summary Extract menu of onCourse, by running the export via the Transactions window, you can define exactly which transactions you wish to export to the resulting text file. The steps involved in running this export via the Transactions window are as follows:

  1. Filter and highlight the transaction records you wish to export within the Transaction window

  2. Go to File > Import/Export > Export Transactions to bring up the export dialog box

  3. Select MYOB from the drop down list of export formats

    Selecting MYOB Export from Import/Export dialog box

  4. Click on the Exportbutton to run the export. Note that the resulting file will be generated as a text file.

    Extract of MYOB Export viewed via Microsoft Excel

Importing

Using the onCourse Groovy scripting engine, powerful import process can be created in onCourse for either once off events or a regular data transfer between onCourse and a third party software service.

For once off type data imports, you may like to read our chapter on Migrating to onCourse.

For regular data transfers, you may like to read our chapter on External Integrations.

Custom import processes and integration scripts can also be created on request. Please send you request to support@ish.com.au

Accessing the imports from the file menu

Standard Imports available

  1. onCourse AVETMISS outcome import - Using data from an import set of NAT00120, NAT00060, NAT00080 and NAT00085 file, create new students in onCourse with their existing AVETMISS file student number. Import their contact record into onCourse and update their record with their outcomes in the onCourse prior learning record. The prior learning records are grouped by qualification, where the qualification data is supplied in the NAT00120. This import is most commonly used during system migration to import data from another AVETMISS reporting tool into onCourse. Running this import on the same data set twice will create duplicate records for each student.

  2. onCourse AVETMISS outcome update import - Using data from an import set of NAT00120, NAT00060, NAT00080 and NAT00085 files, update existing students in onCourse with prior learning data. This process does not create new contact records, only adds prior learning data to existing contacts.

  3. onCourse AVETMISS student import - Using a data from an import set of NAT00080 & NAT000085 files, create new student contact records in onCourse. Running this import on the same data set twice will create duplicate records for each student.

  4. onCourse AVETMISS student update import - Using a data from an import set of NAT00080 & NAT000085 files, update existing student contact records in onCourse. This process does not create new contact records, only adds prior learning data to existing contacts.

  5. onCourse contact CSV import - Import contact data from CSV file to create new contacts, students, tutors and/or companies in the database. Running this import on the same data set twice will create duplicate records for each student.

  6. onCourse paymentIn CSV import - Using CSV data, import paymentIn information in order to automatically match paymentIn records with invoices.

  7. onCourse Contact Update Subscriptions CSV import- Search for contacts based on the provided email address and update their marketing permissions in bulk.

Payment In Import

If you receive bulk payment data from an outside source, you can use the Payment In import process to automatically create payment ins against the appropriate invoices. You will just need to ensure the import file is correctly formatted and onCourse will do the rest for you. The import process relies on the onCourse invoice number being associated with the payments, and if your onCourse invoice number is not included in the information then the payment in import will not work.

If the invoice number is included in the data, then your next step is to determine how you will create the import file. If your data is being sent from a supplier, internal department other businesses you are working with, you may be able to get them to format the data when they export from their system. If you are not able to get the export form changed, such as with internet banking, you many need download the data, edit it in a spreadsheet program, and save it as a CSV format.

Selecting the Payment In Import from Import/Export dialog box

To import the CSV, go to File, Import/Export, Import... and select 'onCourse payment in csv import' as the import type.

Payment In Import file set up

Please see below for the import file specification. You will need to ensure that each line in the CSV relates to an invoice, or the import will fail. To make sure this is the case, please deleted any lines in the spreadsheet that appear after the last invoice record.

Table 27.1. Payment In Import file format

payment.invoicepayment.dateBanked payment.amountpaymentIn.paymentMethod.namepayment.chequeBankpayment.chequeBranchpayment.chequeDrawer
the onCourse invoice numbers date the payment was banked here, in date format DD/MM/YYYYthe payment amount as numbers. Do not use dollar symbols or commas the payment type. the name must be the same as the payment type in your onCourse databaseif payment was by cheque, the bank issuing the chequeif the payment method was by cheque, the name of the branch issuing the chequeif the payment method was by cheque, the name of the drawer on the cheque
1204531/10/20171295.50other   
1289230/10/20171000ChequeCommonwealth BankBendigoA J Spencer

Contact Update Subscriptions CSV import

This import will allow you to update, in bulk, the email, post and sms subscription preferences of a contact record, as identified by its email address. Below is an example of how your CSV file should be laid out. When you format your CSV, be sure to allow the table headers otherwise the import will fail.

Table 27.2. Contact Subscription Update CSV format

emailallowEmailallowPostallowSms
alice@email.com.auTRUETRUEFALSE
bob@email.com.auTRUEFALSEFALSE
charlie@email.com.auFALSEFALSEFALSE