Print Friendly and PDF
Follow

Client Importer User Guide

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. This can only be activated if your VETtrak package includes the Client Updater feature.
  7. 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.
  8. 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.)
  9. 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.

  1. Select your import file here by pressing the button on the right hand side of the field.
  2. 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.
  3. 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.
  4. 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.
  5. Click here to drop down the list of fields to select from, and tick the fields that you wish to import.
  6. 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.

     draggingexample.gif

  7. 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.
  8. Tick this box to see an information panel with a number of hints regarding setting up the import grid.
  9. Ticking this box will display the Status Legend  
  10. 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
  11. 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.

  1. Start by selecting the particular File to Import
  2. 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.
  3. Click Load file to process the data from your document into the Client Importer.
  4. 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.

If a your import document is a CSV file, items that allow multiple selections must have their data entered in quotes (eg "Physical, Medical Condition") in order to work.

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:
  • Blank or any other value - Not Stated
  • Male / M / m - Male
  • Female / F / f -  Female
  • X / x - Indeterminate/Intersex/Unspecified (This gender will currently only be reported in AVETMISS in VIC)
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.
Email  
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:

  • If the postal address fields do not exist in your import file, the Postal is same as residential box will be ticked, and the residential address will be copied into the postal address fields.
  • If the postal address fields exist in your import file, but the records are blank, Postal is same as residential box will be unticked, but the postal address field will be left blank.

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:
  • Not Stated
  • Aboriginal
  • Torres Strait Islander
  • Aboriginal AND Torres Strait Islander
  • Neither Aboriginal nor Torres Strait Islander
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:

  • Not Stated
  • Very well
  • Well
  • Not well
  • Not at all
Have disability Values are imported as follows:
  • Anything with "Y" as first letter
  • Anything with "N" as first letter, except "Not stated"
  • "Not Stated" or blank - Not Stated
NOTE: If anything is found in the Disabilities field, this field will be set to Yes, regardless of what is in the import.
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"
or
"12, 17"

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:
  • Anything with "Y" as first letter
  • Anything with "N" as first letter, except "Not stated"
  • "Not Stated" or blank - Not Stated
NOTE: If anything is found in the Prior Education field, this field will be set to Yes, regardless of what is in the import.
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"
or
"524, 990"

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.
  • Not Stated
  • At secondary school
  • Is not attending secondary school
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:
  • Not Stated
  • Australian citizen
  • Overseas resident
  • Permanent resident of Australia
  • New Zealand citizen
  • Visa holder (humanitarian)
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
(or any text with these as first character)

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
B = MINING
C = MANUFACTURING
D = ELECTRICITY GAS WATER AND WASTE SERVICES
E = CONSTRUCTION
F = WHOLESALE TRADE
G = RETAIL TRADE
H = ACCOMMODATION AND FOOD SERVICES
I = TRANSPORT POSTAL AND WAREHOUSING
J = INFORMATION MEDIA AND TELECOMMUNICATIONS
K = FINANCIAL AND INSURANCE SERVICES
L = RENTAL HIRING AND REAL ESTATE SERVICES
M = PROFESSIONAL SCIENTIFIC AND TECHNICAL SERVICES
N = ADMINISTRATIVE AND SUPPORT SERVICES
O = PUBLIC ADMINISTRATION AND SAFETY
P = EDUCATION AND TRAINING
Q = HEALTH CARE AND SOCIAL ASSISTANCE
R = ARTS AND RECREATION SERVICES
S = OTHER SERVICES

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
1 = MANAGE
2 = PROFESSIONALS
3 = TECHNICIANS AND TRADES WORKERS
4 = COMMUNITY AND PERSONAL SERVICE WORKERS
5 = CLERICAL AND ADMINISTRATIVE WORKERS
6 = SALES WORKERS
7 = MACHINERY OPERATORS AND DRIVERS
8 = LABOURERS
9 = OTHER

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  

 

 

 

 

Back to Top
    Tags:
Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.