This functionality requires involved computing knowledge.
It is important that an experienced IT resource is available to manage this process.
Contents
Introduction
VTImporter is a specialised tool that allows a user to import data into VETtrak externally to the VETtrak software, using the Windows Task Scheduler application.
As these imports are done as a scheduled task, they require no user intervention, and will be completed unattended. This article will provide instructions for configuring VTImporter and windows scheduled tasks for performing unattended imports into the VETtrak database.
Available Importers
VTImporter can perform the following imports into the VETtrak database:
| Importer Name | Table | Description |
| Import clients | CLIENT | Add & edit clients |
| Update unit results | ENROUNIT | Edit unit results |
| Import payments | PAYMENT | Add & edit payments |
| Childcare importer (Foundation skills required) | CHILDCARE | Add & edit childcare sessions |
VTImporter Setup
The VTImporter executable (VTImporter.exe) takes two command line parameters:
- VETtrak initialisation (.ini) file path
- VTImporter initialisation (.ini) file path
Before setting up a windows scheduled task ensure that these configuration files have been created and the parameters are correctly set. Also make sure they're in a sensible location as their file paths will be required.
VETtrak Initialisation File
Standard file for identifying and connecting to the desired VETtrak database. Configured using VTConfig or modifying the file directly using a text editor. For more detail refer to the following articles:
VTImporter Initialisation File
A new initialisation (.ini) file containing configuration parameters for VTImporter. A VTImporter initialisation file can be configured for each required import.
Configuration parameters
The following VTImporter configuration parameters are contained in a [VTImporter] section:
- Username
- Must provide a VETtrak client username that the importer will use for role permissions & setting updated/edited by flags on imported records.
- RoleName
- Optionally provide a role name of one of the client's (identified by the given username) roles. Role will be used for permissions. Leave empty to automatically select an active non read-only role.
- LayoutFilePath
- Must provide a file path to a '.vtlayout' file. This will be used to set the import table and the fields being imported.
- ImportFilePath
- Optionally provide a file path to an import data file (CSV, XLS, or XLSX). Leave empty to use the import file stored in the layout file.
- OutputLogFilePath
- Optionally provide a folder path to write log files to. Leave empty to write logs to Temp folder.
- OutputLogEmail
- Optionally provide an email address to receive log files, otherwise leave empty. Email will send using the configured VETtrak email preferences.
- LogWarningDetails
- Turn on to log extra details for warning status records. [Off = 0][On = 1]
Example VTImporter INI file
[VTImporter] Username='vettrak' RoleName='VETtrak default role' LayoutFilePath='C:\VETtrak\VETtrakImports\MyLayoutFile.vtlayout' ImportFilePath='C:\VETtrak\VETtrakImports\MyImportFile.csv' OutputLogFilePath='C:\VETtrak\VETtrakImports\Logs' OutputLogEmail='username@domain.com' LogWarningDetails=0
Windows Task Scheduler Setup
Now that the VETtrak initialisation (.ini) and VTImporter initialisation (.ini) files have been setup a windows task can be scheduled to run the VTImporter executable passing both files as parameters. There are two common ways to achieve this, either via command prompt (CLI) or the Task Scheduler (GUI).
Command Prompt (CLI)
Only use command prompt if the Task Scheduler (GUI) is unavailable or you're familiar with command line interfaces.
Launch command prompt:
Launch command prompt (Win + R, type 'cmd' hit enter).
Create VTImporter task:
Create a task using SCHTASKS (append /? to get additional details on SCHTASKS or refer to the windows documentation). E.g. task creation command to run an import daily at 11am:
schtasks /Create /SC DAILY /TN "VETtrak_Imports\Client Import" /TR "'C:\Program Files\VETtrak\VTImporter.exe' 'C:\Program Files\VETtrak\VETtrak.ini' 'C:\Program Files\VETtrak\VTImporter.ini'" /ST 11:00
Notes:
- Provide the full path to your VTImporter executable
- Provide the full path to the VETtrak.ini file as the first inline parameter
- Provide the full path to the VTImporter.ini file as the second inline parameter
- Recommended that you keep your Task Schedule Library organised by placing VTImporter tasks in a VETtrak_Imports or similar folder.
Delete VTImporter task:
Tasks can be deleted using /Delete parameter. E.g.
schtasks /Delete /TN "VETtrak_Imports\Client Import"
View VTImporter tasks:
Use the /Query parameter to view all scheduled VTImporter tasks (when using a folder called VETtrak_Imports)
schtasks /Query /TN \VETtrak_Imports\
Disable/Enable VTImporter tasks:
Use the /Change parameter
Manually run a VTImporter task:
Use the /Run parameter
Task Scheduler (GUI) - Recommended
Task Scheduler is a graphical user interface (GUI) which allows for easy scheduling of windows tasks.
Steps to configure VTImporter task:
- Launch Task Scheduler by searching for it in the start menu or Win + R and type 'taskschd.msc', hit enter.
- Right click on 'Task Schedule Library' then select 'New Folder'
- Name it something like 'VETtrak_Imports'
- Make sure 'Task Scheduler Library' is expanded and right click on your new folder and select 'Create Basic Task...'
- Provide a name and a description, click next.
- Select a Trigger, click next.
- Set any additional trigger settings, click next.
- Select 'Start a program', click next.
- Provide the VTImporter.exe filepath in the 'Program/Script:' field. Provide the VETtrak.ini file path (first) and the VTImporter.ini file path (second) enclosed by single quotes and separated by a single space in the 'Add arguments (optional):'field.
- E.g. Program/Script: "C:\...\VTImporter.exe"
- E.g. Add arguments (optional): "'C:\...\VETtrak.ini' 'C:\...\VTImporter.ini'"
- Alternatively add the arguments to the 'Program/Script:'field
- Once you've correctly filled in the 'Start a Program' page click next.
- Review the task and ensure everything looks correct then click finish.
- Now with your folder selected you can select your tasks and right click or use the 'selected item' section to enable/disable/run/delete/change properties of the task.
Log File
A log file is written at the end of every execution of VTImporter. The log file can output to the following:
- To the file location specified by the OutputLogFilePath parameter in the VTImporter initialisation file
- To the email address specified by the OutputLogEmail parameter in the VTImporter initialisation file
- To the local temp folder (C:\Users\USER\AppData\Local\Temp\VETtrak)
- Occurs only when no log outputs have been specified or an error occurs before the VTImporter initialisation file load.
Structure
VTImporter log files contain application execution details in the following order:
- Indication of successful connection to the VETtrak database
- Overview of VTImporter initialisation file parameters
- Validation errors or warnings with VTImporter initialisation file parameter values
- Indication of loading permission and the role that has been used
- Start of Import process
- 10 steps of the import process
- Loading layout file
- Preparing table rules for layout table number
- Validating layout file
- Building column definitions from layout
- Creating datasets for import data
- Loading import file
- Cleaning data
- Validating data
- Re-validating & importing data
- Writing import batch
- End of Import process
- Log dispatch information
Example Log
Example VTImporter log inserting 10 new clients into a VETtrak database:
18/04/19 16:30:01.311 - Application running and successfully connected to the database. 18/04/19 16:30:01.311 - Loading Ini file [VTImporter] section params. 18/04/19 16:30:01.321 - Ini file [VTImporter] section params: Username : js Role name : Layout file path : C:\Users\X\Desktop\VTImport test\ClientsDefaultVTImport.vtlayout Import file path : C:\Users\X\Desktop\VTImport test\ClientVTImportData.xlsx Log file path : C:\Users\X\Desktop\VTImport test\Logs Log email : email@vettrak.com.au Log warning details: True User ID : 13 Role ID : 0 18/04/19 16:30:01.321 - Validating Ini file [VTImporter] section params. 18/04/19 16:30:01.321 - Ini file [VTImporter] section params are valid. 18/04/19 16:30:01.321 - Loading preferences and permissions. 18/04/19 16:30:01.411 - Role has been set to 'VETtrak default role' [ID: 3] (only assigned VETtrak non read-only role). 18/04/19 16:30:01.451 - Starting Import Process: 18/04/19 16:30:01.451 - [BEGIN] - Import step 1. Loading layout file: 18/04/19 16:30:01.451 - [END] - Import step 1. Loading layout file. 18/04/19 16:30:01.451 - [BEGIN] - Import step 2. Preparing table rules for layout table number: Set to import records into the Client table (table number: 1). Importer User Action Permissions: No Action, Add, Edit 18/04/19 16:30:01.471 - [END] - Import step 2. Preparing table rules for layout table number. 18/04/19 16:30:01.471 - [BEGIN] - Import step 3. Validating layout file: 18/04/19 16:30:01.521 - [END] - Import step 3. Validating layout file. 18/04/19 16:30:01.521 - [BEGIN] - Import step 4. Building column definitions from layout: 18/04/19 16:30:01.531 - [END] - Import step 4. Building column definitions from layout. 18/04/19 16:30:01.531 - [BEGIN] - Import step 5. Creating datasets for import data: 18/04/19 16:30:01.531 - [END] - Import step 5. Creating datasets for import data. 18/04/19 16:30:01.531 - [BEGIN] - Import step 6. Loading import file: 18/04/19 16:30:01.571 - [END] - Import step 6. Loading import file. 18/04/19 16:30:01.571 - [BEGIN] - Import step 7. Cleaning data: 18/04/19 16:30:01.571 - [END] - Import step 7. Cleaning data. 18/04/19 16:30:01.571 - [BEGIN] - Import step 8. Validating data: 18/04/19 16:30:01.571 - Process all records (perform import: false) 18/04/19 16:30:01.571 - Build foreign key lookup reports 18/04/19 16:30:01.571 - Load foreign key lookup datasets 18/04/19 16:30:01.581 - Clear summary 18/04/19 16:30:01.581 - Setup table rules 18/04/19 16:30:01.591 - Validating rows (records that need processing: 10) 18/04/19 16:30:01.591 - Build match reports 18/04/19 16:30:01.591 - Building match queries 18/04/19 16:30:01.591 - Iterate through the import data records: 18/04/19 16:30:01.591 - Record 1 requires processing 18/04/19 16:30:01.591 - Run match query 18/04/19 16:30:01.601 - Validate 18/04/19 16:30:01.821 - Record 2 requires processing 18/04/19 16:30:01.821 - Run match query 18/04/19 16:30:01.831 - Validate 18/04/19 16:30:01.991 - Record 3 requires processing 18/04/19 16:30:01.991 - Run match query 18/04/19 16:30:02.001 - Validate 18/04/19 16:30:02.171 - Record 4 requires processing 18/04/19 16:30:02.171 - Run match query 18/04/19 16:30:02.181 - Validate 18/04/19 16:30:02.341 - Record 5 requires processing 18/04/19 16:30:02.341 - Run match query 18/04/19 16:30:02.361 - Validate 18/04/19 16:30:02.521 - Record 6 requires processing 18/04/19 16:30:02.521 - Run match query 18/04/19 16:30:02.531 - Validate 18/04/19 16:30:02.691 - Record 7 requires processing 18/04/19 16:30:02.691 - Run match query 18/04/19 16:30:02.711 - Validate 18/04/19 16:30:02.871 - Record 8 requires processing 18/04/19 16:30:02.871 - Run match query 18/04/19 16:30:02.891 - Validate 18/04/19 16:30:03.061 - Record 9 requires processing 18/04/19 16:30:03.061 - Run match query 18/04/19 16:30:03.081 - Validate 18/04/19 16:30:03.241 - Record 10 requires processing 18/04/19 16:30:03.241 - Run match query 18/04/19 16:30:03.251 - Validate ------------------------------------ Validating rows performance summary: ------------------------------------ Records processed: 10 Total time: 00:01.840 (MM:SS.SSS) Total wait time: 00:00.000 (MM:SS.SSS) Total run time: 00:01.840 (MM:SS.SSS) Average per record: 0.18 (seconds) ------------------------------------ -------------------- Missing information: -------------------- - None --------- Statuses: --------- - Unknown: 0 - Okay: 10 - Warning: 0 - Error: 0 - Nothing to do: 0 - Processed: 0 18/04/19 16:30:03.411 - [END] - Import step 8. Validating data. 18/04/19 16:30:03.411 - [BEGIN] - Import step 9. Re-validating & importing data: 18/04/19 16:30:03.411 - Process all records (perform import: true) 18/04/19 16:30:03.411 - Build foreign key lookup reports 18/04/19 16:30:03.411 - Load foreign key lookup datasets 18/04/19 16:30:03.431 - Clear summary 18/04/19 16:30:03.431 - Setup table rules 18/04/19 16:30:03.431 - Re-validating and importing rows (records that need processing: 10) 18/04/19 16:30:03.431 - Build match reports 18/04/19 16:30:03.431 - Building match queries 18/04/19 16:30:03.431 - Iterate through the import data records: 18/04/19 16:30:03.431 - Record 1 requires processing 18/04/19 16:30:03.431 - Run match query 18/04/19 16:30:03.441 - Validate 18/04/19 16:30:03.601 - Save data (add) 18/04/19 16:30:03.651 - Record 2 requires processing 18/04/19 16:30:03.651 - Run match query 18/04/19 16:30:03.661 - Validate 18/04/19 16:30:03.821 - Save data (add) 18/04/19 16:30:03.851 - Record 3 requires processing 18/04/19 16:30:03.851 - Run match query 18/04/19 16:30:03.851 - Validate 18/04/19 16:30:04.011 - Save data (add) 18/04/19 16:30:04.041 - Record 4 requires processing 18/04/19 16:30:04.041 - Run match query 18/04/19 16:30:04.041 - Validate 18/04/19 16:30:04.201 - Save data (add) 18/04/19 16:30:04.221 - Record 5 requires processing 18/04/19 16:30:04.221 - Run match query 18/04/19 16:30:04.221 - Validate 18/04/19 16:30:04.381 - Save data (add) 18/04/19 16:30:04.401 - Record 6 requires processing 18/04/19 16:30:04.401 - Run match query 18/04/19 16:30:04.401 - Validate 18/04/19 16:30:04.561 - Save data (add) 18/04/19 16:30:04.601 - Record 7 requires processing 18/04/19 16:30:04.601 - Run match query 18/04/19 16:30:04.601 - Validate 18/04/19 16:30:04.761 - Save data (add) 18/04/19 16:30:04.801 - Record 8 requires processing 18/04/19 16:30:04.801 - Run match query 18/04/19 16:30:04.801 - Validate 18/04/19 16:30:04.961 - Save data (add) 18/04/19 16:30:04.981 - Record 9 requires processing 18/04/19 16:30:04.981 - Run match query 18/04/19 16:30:04.991 - Validate 18/04/19 16:30:05.151 - Save data (add) 18/04/19 16:30:05.181 - Record 10 requires processing 18/04/19 16:30:05.181 - Run match query 18/04/19 16:30:05.191 - Validate 18/04/19 16:30:05.341 - Save data (add) ---------------------------------------------------- Re-validating and importing rows performance summary: ---------------------------------------------------- Records processed: 10 Total time: 00:01.980 (MM:SS.SSS) Total wait time: 00:00.000 (MM:SS.SSS) Total run time: 00:01.980 (MM:SS.SSS) Average per record: 0.2 (seconds) ---------------------------------------------------- ----------------- Database actions: ----------------- - Inserts: 10 --------- Statuses: --------- - Unknown: 0 - Okay: 0 - Warning: 0 - Error: 0 - Nothing to do: 0 - Processed: 10 18/04/19 16:30:05.391 - [END] - Import step 9. Re-validating & importing data. 18/04/19 16:30:05.391 - [BEGIN] - Import step 10. Writing import batch: 18/04/19 16:30:05.391 - [END] - Import step 10. Writing import batch. 18/04/19 16:30:05.391 - Finished Import Process. 18/04/19 16:30:05.401 - Write log file complete.