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:

Untitled-1.jpg

  

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).

Untitled-1.jpg

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.

 

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.