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 MMDDYYYY)
- [FinishDate] – The date that a course or module was completed (in the format MMDDYYYY)
- [CompliantTillDate] – The date that a learner is compliant until for a course (in the format MMDDYYYY)
- [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. Also, if you include a field that does not contain any data the field will not populate in the report.
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$01-25-2011
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,01/25/2011
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.
Comments
0 comments
Article is closed for comments.