For the new method of adding Client Records to your VETtrak system, please see our article covering the new Import Clients utility:
The VETtrak Client Importer allows you to import client data into VETtrak from a CSV or Excel (.xls or .xlsx) file output from another system. It is very flexible, in that you can set it up to match the fields contained in your import file and the order in which they appear.
In order to set up and start using the importer, you will need:
- A sample file of the data you want to import.
- Excel or another spreadsheet programme to view the file in an easily readable format while you set up the importer.
To access the Client Importer, go to Utility > Client importer.
Setting Up the Import Grid
First, click the Settings button in the bottom left hand corner of the Client Importer Wizard.
- Indicate which is the first row of actual data. For example, if the top row of your spreadsheet file contains the names of the columns, and the data starts on the second line, you would select "2" here.
- Indicate what character is being used to separate the fields in your import file. If the character you are using is not listed, select "Other" and enter the character in the Field Separator box.
- Select whether the end of a line in your import file is indicated by a carriage return, line feed, or both. Check with your system administrator if you don't know what setting to use here.
- If you tick this box, the separate address fields used in AVETMISS 7 address formats (unit, building name, street number) will be available to select when setting up the import grid. If this box is unticked, all this information needs to be imported in a single address field. NOTE: Having the wrong address format specified here will cause address fields not to be imported.
- Tick this box if you want the records to be imported in one go. If you untick this box, a confirmation message will be displayed before importing each record.
- This can only be activated if your VETtrak package includes the Client Updater feature.
- If you tick this box, any matching clients found to be already in the database will be added to the group you are importing these clients into.
- Tick this box if you want the importer to check the database and display a warning if it finds an existing client with the same Given Name / Surname combination. (Disabled if client update feature is enabled.)
- If you tick this box, any client for whom a matching client warning was indicated will not be imported. (Disabled if client update feature is enabled.)
We can now set up our import grid in the Wizard itself to match the arrangement of data fields in our import file.
- Select your import file here by pressing the button on the right hand side of the field.
- Select a group within your VETtrak database for the clients to be added to upon import. You can press the Handy Button
on the left hand side to create a new group on the fly if required. - These buttons toggle all columns on and off. Depending on how many fields of data you are importing, it may be helpful to have all columns selected or not selected to start with. If you are importing a large number of data fields, you can Show all columns, and then untick the ones you don't want. If your import only contains a few fields, it is probably easier to Hide all columns, and then tick the ones you want to import.
- Shows the current Grid Layout File being utilised. If you have not yet created a Layout file, pressing Save Layout will prompt you to name your current layout and have it saved in a location - That will then be displayed in this field. This layout will be remembered the next time you open the wizard. If you have files in different formats to import, you can save a number of layouts and load the one appropriate to the file you are importing.
- Click here to drop down the list of fields to select from, and tick the fields that you wish to import.
- Drag and drop the fields into the correct order to match your import file.
You can also rearrange the columns by dragging and dropping the column headings in the grid, as shown below.
- If you have columns in your import file containing data that will not be imported into VETtrak, tick one of the Blank column boxes and drag it into the position to match that of the superfluous field in your import file. The importer will skip this column and ignore anything it contains.
- Tick this box to see an information panel with a number of hints regarding setting up the import grid.
- Ticking this box will display the Status Legend
- Press Load file to process the Import file you have selected. If the data can be read by the Client Importer, it will display in the grid to review. Press Validate to run a data check over the displayed information.If your document does not load, there is likely formatting issues with the data it contains. Open the document in the programme it was created in and review the data prior to attempting to load it once more
- When ready, press Import to process the import of the data.
Importing the Data
Once we have everything set up, we can proceed to import the data.
- Start by selecting the particular File to Import
- Clients may be imported into a group, so select the group you want to put them into here. If you do not with to add your newly imported clients to a group, simply leave this field blank - Or delete the text in this field to clear out the selected group. Depending on your organisation's needs, this can be a group they will stay in permanently, or you may have a group set up for the specific purpose of importing new clients into, so you can check them before moving them to another group or out of any groups at all. You can press the Handy Button on the left hand side to create a new group on the fly if required.
- Click Load file to process the data from your document into the Client Importer.
- Click Validate to process a data check over your expected data to import.
The following items must exactly match information in VETtrak for the import to proceed:
Employer
Suburb / State / Postcode combination of the address
Position
Division
If any of these items do not match you will see the following message:
Click Yes to see the information that is missing:
From here, you can click on each tab to see the information that is missing. If the information in the import file is correct, you will need to close the Importer Wizard and create the missing items in VETtrak before you can proceed.
If there are a number of items you need to enter, you can copy them to the clipboard and paste them into a Word Document or Notepad file so you have a list to refer to.
When you have entered all the necessary missing information, repeat steps 1-4 above. If there are no more errors, you will see a message like this:
The Status column will change colour according to the status of that client:
- Green - There are no errors and the person will be imported.
- Orange - Warning - this is an information alert about something that might be unusual but is not serious enough to prevent the record being imported. This is for your information only, to allow you to check and decide if the data being imported is correct or not.
- Blue - The person is already in VETtrak and will not be imported.
- Yellow - The person is already in VETtrak but needs to be edited (only applicable if the Client Updater feature is included in your VETtrak package.)
- Red - Error - there is a problem with this record and the import cannot proceed until this is fixed.
Click on a coloured box for more information.
To see a reference of what the different colours mean, tick the Show legend box to see an explanation.
When you are happy that everything is correct, click Import.
Client Updater
If the Client Updater feature is included in your VETtrak package, the importer can edit existing client records as well as inserting new ones.
To allow editing of existing client records by the importer, you need to tick the Allow updates to existing clients box in the Settings window. This will untick and disable the options to warn if there are existing clients and to skip records with matching names.
NOTES:
- If you are unable to tick the Allow updates box, check that you:
- Have a valid registration key for the Client Updater (under File > Global Preferences > Registration key.)
- Have the correct Security permissions to use the Client Updater (right-click on the appropriate Role in the Security Manager, select Edit feature list and check that the Client Updater box is ticked. If you do not have access to the Security Manager yourself, ask your VETtrak administrator to check that you have the correct permissions.)
- For the updater to work, it must be able to match clients in the import file with clients in VETtrak. The updater matches clients according to the following fields:
- VETtrak Code - If the code is included in the import fields, the client code must match for the importer to recognise it as the same person. If a client in VETtrak has a code matching the one in the import file, that client will be matched and updated, regardless of how many other fields have changed.
- Given name / Surname / Date of Birth - If the VETtrak Code is not included in the import fields, the updater looks for clients with the same combination of Given Name / Surname / Date of Birth.
Therefore, if you wish to use the Client Updater feature, you must ensure that your import data includes either the client's code, and / or their given name, surname and date of birth. Without this information, the updater cannot match records with existing clients, and new clients would be created.
Import Fields
Below is a list of the fields that can be imported. Some items must be in a particular format, or exactly match data in VETtrak, as indicated in the Notes column below. When matching the values for a field, the search is NOT case sensitive.
For an Excel file, multiple item list data can be a comma separated string without double quotes (eg: Physical, Medical Condition).
For items marked in red, the import cannot proceed until either the missing item is entered in VETtrak, or the import file is corrected.
For items marked in blue, if the record does not precisely match an item from the Import Fields list below the import can proceed, but these items will not be imported. The relevant field in VETtrak will be set to "Not Stated".
| Field | Notes |
|---|---|
| Title | |
| Given name | |
| Surname | |
| Other name | |
| Preferred name | |
| VETtrak code | If a client code is included in the import, any prefix generation set in VETtrak will be ignored, and the code imported as is. If the client code field is blank in the import, a code will be automatically generated. If client code is not imported, the automatically generated client code will include any appropriate prefix set up as per normal VETtrak function. NOTE: A Client Code cannot be updated using the Client Updater utility. |
| Home phone | |
| Work phone | |
| Mobile phone | |
| Secondary Mobile phone | |
| SMS Both Mobiles Flag | Any SMS to be sent to both primary and secondary mobiles. A value beginning with ’Y’ means Yes; a value beginning with ‘N’, or a blank, means No. Any other value maps to No, with a warning. |
| Fax | |
| Gender | Values are imported as follows:
|
| DOB | Used along with Surname, Given Name combination to match with existing clients in the database. Both must have the same valid date to match, i.e. two clients with an empty date of birth will not be matched. Must be formatted as DD/MM/YYYY - e.g 01/12/1992 |
| Employer name | Must match an Employer that already exists in VETtrak. |
| Secondary Email Address | |
| Email Both Addresses Flag | Any email to be sent to both primary and secondary email addresses. A value beginning with ’Y’ means Yes; a value beginning with ‘N’, or a blank, means No. Any other value maps to No, with a warning. |
| Username | Auto-generated if not included in import file. |
| Password | Auto-generated if not included in import file. |
| Client notes | |
| Primary unit address | Only available if Import separate AVETMISS 7 fields is ticked. Use when the import file contains separate fields for street name, building name and number, and / or unit number. NOTE: If address fields are not importing, ensure the correct address format is selected in the Importer Preferences. |
Primary building address | |
| Primary address number | |
| Primary street address | |
| Primary address | Only available if Import separate AVETMISS 7 fields is NOT ticked. Use when street name, building name and number, and unit number are all in a single field in the import file. NOTE: If address fields are not importing, ensure the correct address format is selected in the Importer Preferences. |
| Primary address city | Must match a suburb / state / postcode combination that already exists in VETtrak |
| Primary address state | |
| Primary address postcode | |
| Postal unit address | Only available if Import separate AVETMISS 7 fields is ticked. NOTE: If address fields are not importing, ensure the correct address format is selected in the Importer Preferences. |
| Postal building address | |
| Postal address number | |
| Postal street address | |
| P.O. Box address | |
| Postal address | Only available if Import separate AVETMISS 7 fields is NOT ticked. NOTES:
NOTE: If address fields are not importing, ensure the correct address format is selected in the Importer Preferences. |
| Postal city | Must match a suburb / state / postcode combination that already exists in VETtrak |
| Postal state | |
| Postal postcode | |
| Emergency contact name | |
| Emergency contact relationship | |
| Emergency contact phone | |
| Emergency contact mobile phone | |
| Date last assessed | |
| Date commenced | |
| Salary / hourly rate | |
| Date terminated | |
| Passport Number | |
| Passport Country | Must exactly match a country name in VETtrak |
| Nationality | |
| Visa Number | |
| Visa Effective Date | |
| Visa Expiry | |
| Year Completed | Must be numbers |
| Where Completed | |
| Indigenous status | Must contain the full AVETMISS value to be recognised for the import:
|
| Proficiency in spoken English | Only imported if Language Spoken at Home is not English. Ignored if language is English or Not Stated. Must contain the full AVETMISS value to be recognised for the import:
|
| Have disability | Values are imported as follows:
|
| Disabilities | Must be full value or number, as listed below. If multiple disabilities are to be imported, they must be enclosed in double quotes and separated by commas, for example: "Physical, Vision" 11 - Hearing/Deaf 12 - Physical 13 - Intellectual 14 - Learning 15 - Mental Illness 16 - Acquired Brain Impairment 17 - Vision 18 - Medical Condition 19 - Other 99 - Unspecified |
| Have Prior Education | Values are imported as follows:
|
| Prior Education | Must be full value or number, as listed below. If multiple prior education types are to be imported, they must be enclosed in double quotes and separated by commas, for example: "Ceritificate I, Miscellaneous Education" 008 - Bachelor Degree or Higher Degree Level 410 - Advanced Diploma and Associate Degree Level 420 - Diploma Level 511 - Certificate IV 514 - Certificate III 521 - Certificate II 524 - Certificate I 990 - Miscellaneous Education |
| At School | Must contain either Yes / No, Y / N, or the full AVETMISS value to be recognised for the import.
|
| Country of Birth | Must exactly match a country name in VETtrak |
| School level | Must be full value or number, as listed below. Otherwise set as "Not Stated". 02 - Did not go to school 08 - Year 8 or below 09 - Year 9 or equivalent 10 - Completed year 10 11 - Completed year 11 12 - Completed year 12 |
| Labour force status | Must be full value or number, as listed below. Otherwise set as "Not Stated". 1 - Full time employee 2 - Part time employee 3 - Self employed - not employing others 4 - Employer 5 - Employed - unpaid worker in a family business 6 - Unemployed - seeking full time work 7 - Unemployed - seeking part time work 8 - Not employed - not seeking employment |
| Language spoken at home | Must exactly match a language name in VETtrak |
| Position | Must match a position that already exists in VETtrak. |
| Division | Only available if you have a valid key for the Security feature. Must exactly match a Division that exists in VETtrak. |
| Citizenship | Must match one of the following:
|
| Former surname | |
| Blank columns 1-4 | These columns can be used if you have fields in your import file that don't match fields in VETtrak, or that aren't to be imported for some other reason. Place these columns in the same position in the grid to match where these unwanted fields are located in the import file. |
| Archived Flag | A = Active T = Terminated (archived / inactive) Any other value will be imported as Active. |
| External Code | Importing a Client's External Code will store the imported code against the External System item marked as the default, as well as set it to the Primary External Code for that Client. |
| USI | Must be 10 characters and contain only capital letters (except O and I) and digits 2 - 9. |
| Birth Town | |
| Contact Method | Must be full value or number, as listed below. Not case sensitive. Additional data will be required if options 1, 2 or 3 are used. 0 or empty - Not Specified 1 - Email 2 - Mobile 3 - Mail |
| USI Exempt | 1, Y, T or E = Exempt 0, Empty or any other character = Not Exempt |
| VIC industry of employment | As required in Victoria. As shown below, a single letter in the range A to S, or the string to the right of the equals sign, or blank. Any other value results in S (OTHER SERVICES). A = AGRICULTURE FORESTRY AND FISHING |
| VIC occupation identifier | As required in Victoria. As shown below, a digit 0 to 9, or the string to the right of the equals sign, or blank. Any other value results in 0 0 = NONE |
| VIC Student Number (VSN) | ‘888888888’ sets VSN Type to New (yet to be allocated). A blank value sets VSN Type to Unknown (client may have a VSN but doesn’t know it). Any other value must conform to the complex parity rules for a VSN |
| South Australian Certificate of Education (SACE) student number | Must be blank, or consist of six digits followed by a single letter |
| Student identification confirmed | A value beginning with ’Y’ means Yes; a value beginning with ‘N’, or a blank, means No. Any other value maps to No, with a warning. |
| Student identification details | |
| Centrelink Reference Number (CRN) | |
| CRN expiry date | |
| Job seeker ID |