Exporting reports with a custom data format

If you want to export report data in a format other than CSV you do this using a Custom export format.

The format allows you to enter:

• the names of the fields you want to export
• the order of the fields
• any extra text or field delimiters
• padding on the fields

Being able to control the format is essential when using systems like SAP as they require very specific file formats.

Firstly, you will need to create a custom report by going to the Reports tab, then clicking the ‘Legacy - Create a report’ button. Choose your report, specify your report criteria and click ‘Run this report’.

Once the report has been generated, click on the ‘Schedule email for this report’ link on the right:

  

Now, change the ‘Report Format’ drop-down from CSV to ‘Custom’ and enter your custom string format in the box (see below for available fields and how to enter them correctly).

What are fields are available

• [CourseName] – The title of a course
• [CourseCode] – The reference code of a course
• [StartDate] – The date that a course or module was started (in the format DDMMYYYY)
• [FinishDate] – The date that a course or module was completed (in the format DDMMYYYY)
• [CompliantTillDate] – The date that a learner is compliant until for a course (in the format YYYYMMDD)
• [Compliant]- If the learner is compliant for that course
• [Completed] – True/False indicator if a course or module is complete
• [PercentageComplete] – The percentange of the modules in a course that are complete
• [UpToDate] – If the course is complete this indicates if the learner has completed the latest version of the course
• [FirstName] – Learners first name
• [LastName] – Learners last name
• [FullName] – Learners first and last name
• [Username] – Learners username
• [CompanyName] – The users company
• [JobTitle] – The users job title
• [TeamName] – The name of a team (see note below)
• [CustomField1] – Custom field data
• [CustomField2] – Custom field data
• [CustomField3] – Custom field data
• [CustomField4] – Custom field data
• [CustomField5] – Custom field data
• [CustomField6] – Custom field data
• [CustomField7] – Custom field data
• [CustomField8] – Custom field data
• [CustomField9] – Custom field data
• [CustomField10] – Custom field data
• [CourseCustomField1] – Course Custom field data
• [CourseCustomField2] – Course Custom field data
• [CourseCustomField3] – Course Custom field data
• [CourseCustomField4] – Course Custom field data
• [CourseCustomField5] – Course Custom field data
• [CourseCustomField6] – Course Custom field data
• [CourseCustomField7] – Course Custom field data
• [CourseCustomField8] – Course Custom field data
• [CourseCustomField9] – Course Custom field data
• [CourseCustomField10] – Course Custom field data

 

Note: The fields are all case sensitive and must include the [ ] brackets. 

Note:  Depending on the report ran, not all fields will populate.  For example, if the report is a User results report, [TeamName] will not populate due to the unlimited amount of data that this field may potentially contain.

Basic sample format

This is a sample format for exporting the data from a course report and we have delimited the fields using a $.

[FullName]$[Username]$[CourseName]$[Completed]$[FinishDate]

The result would be

John Doe$johndoe@company.com$Sample Course 101$True$2011-01-25

Adding field padding to the export

If you system requires fields to be padded with a particular character and to a certain number of spaces you can use an advanced export format.

[FieldName,NumberOfSpaces,CharacterToPadWith]

eg. The following would pad the CourseCode field to 8 places prefixed with 0′s

[CourseCode,8,0]

So if the course code was 555 it would be exported as 00000555

Creating a Custom csv file

Like with the Basic sample format above, this is a sample format for exporting the data from a course report.  However, to end up with a csv you will need to to delimit the fields using commas (,) instead of $.

[FullName],[Username],[CourseName],[Completed],[FinishDate]

The result would be:

John Doe,johndoe@company.com,Sample Course 101,True,20110125

The file will be downloaded as a .txt file.  If the file type must be a .csv, open the file in Notepad, select Save As…, rename the file with .csv at the end and click save.