• Home  |
  • Blog  |
  • 2016  |
  • 11  |
  • Formatting your data for import into Ava advanced agency web system

Blog posts written on Thursday November 2016

Posted Thursday Thursday, November 24, 2016 by Administrator

Importing your data into the Ava advanced agency web system

When you are importing data from an existing system, the data will be in the order that it is exported or copied from your existing system or systems. Whist there are a few standard obsolete or obsolescent systems, there are many that are based on evolved Excel spread sheets. Put simply there are an almost limitless number of variations in the way data can be layed out for us to import.

If you are replacing your existing system with our more modern, reliable and feature rich software, you will be happy to know that we can import data from any system and even combined systems that don’t talk to each other well.

Import is always done using excel spread sheet as the intermediary. We both want to keep your migration costs low so here are a few if the mistakes that can push your costs up or delay the migration process.


Ava can store a range of addresses, all these can be imported

For employees (not limited to), home address, full time work address, reference addresses, next of kin address, a limited company invoice address etc.

For customers (not limited to), organization address, main address, invoice address, contact address(es), work location address etc.

Address should be spread across up to five columns with a separate column for the post code:

Address Column Usage


Ava can store a range of employee skills or job descriptions:

Job descriptions, skills, positions, levels of employment.

These really need to be in separate columns with a simple “yes”

Job Column Usage

The same is true for all training and certification data which can be imported. Where the system stores documents or images, these must be uploaded manually.




Excel has the facility to format (display) dates how you like:

We like “dd mmm yyyy” format which is totally unambiguous as far as UK and other (US) date formatting is concerned. Please apply formatting to all date columns. If you format in this style then it is immediately obvious where the data is either ambiguous or badly formatted. Here is a (made up) example of what can happen:


Exported data:


Formatting Dates


The solution is to format by right mouse clicking on the highlighted cells you want to format. Your actual experience may vary depending on the version of excel or other (Open Office etc.) spreadsheet software you are using.

Formatting Dates 2


You will get the format option box

Formatting Dates 3


Select custom and type in "dd mmm yyyy"

 Formatting Dates 4

 Then click ok to apply this format to the selected area. You may need to google how to do this if your settings prevent this from immediately happening. If the displayed format does not immediately update, you need to copy the data and then (paste) re-enter the data as values.

New values change when you move off the cell.

Formatting Dates 5


You need to enter dates with forward slashes between the day/month/year. Once again experience may vary from version to version and spreadsheet to spreadsheet.

Formatting Dates 6


Phone numbers


Excel is a pain for phone numbers, at the earliest opportunity it will convert them to a pure number lopping off the leading zero.

As you enter it:

Phone Numbers


As it is instantly displayed in the cell

Phone Numbers 2


But when you move off the cell, this happens!

Phone Numbers 3


So enter phone numbers like this:

Phone Numbers 4


And the result is:

Phone Numbers 5


Matching up data for Employee names from two separate sources


If you have a list of employee names with different information from two separate sources e.g. payroll and booking, because they are separate there is no earthly reason to expect the two to match perfectly. Actually one of the reasons for adopting our system is to avoid the sort of problems this causes.


Here are a few examples:

Matching Names


I know my name is "Pettman" but is it "Tony" or "Anthony" Williams or are there two and they are distinguished by using the diminutive for one of them? Is there a Jon and a John Smith?


Only you know! To help this process, we will carry out this comparison and produce a report of miss-matches, and you can then "clean" the supplied data for us to then do a final import.


We can also import historical shift booking information.


This is a separate cost and we will quote for this on sight of example data.


How long does this take?


Using these processes we can get your site up and running from your receipted order usually within or around 10 working days. This is providing there is always someone available who can clarify any ambiguities as they arise.




If you found this information useful, please share it!

0 comments for "Formatting your data for import into Ava advanced agency web system". Leave one here:
  1. e.g. http://ava.co.uk


Contact Information

To find out more about Ava solutions you can contact us in a number of ways:
Follow Us...