Revision as of 14:34, 7 November 2012

Data Migration is the process by which data is imported into a new system. This may take the form of simply typing in records where there are only a few pieces of data to enter, or where large amounts exist, creating an import program to import data stored in a database or spreadsheets.

Should you automate the import of your data?

One of the most important factors in deciding to have the data imported would be the volume of data. If there are hundreds or thousands of records then it is worth creating a program to import the records. Conversely, if there were only 20 records or so, it is better to have them manually imported. Volunteers may be able to help.

Another factor to consider is the cleanliness of your data. A manual import can be a good time to cleanse data. Cleansing data is a term used to describe the process of removing duplicated records, or correcting misspellings etc. This would apply to data where duplicates had appeared in the system (for example “Mr Richard Paul Smith” and “Mr Richard P Smith” have been entered in a Customers Database). Equally, it is possible for misspellings to be an issue. An example of this might be where a client has “Altzheimers” rather than “Alzheimers” against their record.

It is not generally possible to correct such mistakes during the data migration process, so if large scale data cleansing is needed, it is better to do this manually, or approach a contractor who specialises in data cleansing.

Automated import

This guide deals with the process of automating the import of your existing data to Charitylog. This needs to be done by the Charitylog programming team, and comes with a cost (though it is very reasonably priced!).

It is usually the case that an organisation buying Charitylog is working from a number of different systems. One project co-ordinator may be using an Access database, while another is using a series of Excel spreadsheets to store their data. Some projects, or even whole organisations, may still work on a paper filing system. Invariably there will be some data which can only be imported manually. From your (the client's) point of view, the important thing is to get your existing data into the best possible state for migration, to ensure that the process goes smoothly and that the end result is as expected. Ideally, you will also save time, and therefore money, in the process!

What can we do with our existing data?

The preferred format to receive data for import into Charitylog is in the form of Spreadsheets (often Excel files [.xls or .xlsx]).

We can also accept Microsoft Access databases from which we can extract the relevant data, but this creates one further step in the process and so more time will be required.

To avoid any issues of ambiguity, all spreadsheets should be uniquely and clearly named, for example;

• Carers.xls
• Clients.xls
• Disabilities.xls
• Doctors.xls

This will make it clear to us where the imported data needs to be stored within your new Charitylog system.

Charitylog "rules" for data

Name - All records must have a surname, or an organisation name. Records with no name cannot be imported.

Address format - Addresses in Charitylog are held in the following format:

• Address 1
• Address 2
• Address 3
• Town
• County
• Postcode

If the address is stored as a single string, we will not be able transfer it as part of the data migration process. Therefore you will need to manually separate it into different fields, and we can create the address properly in Charitylog.

Key Fields

An example of a record being linked to another record by a unique number

In some instances a user will have separate tables of data which link to one-another with key fields (numbers that relate to uniquely numbered records in a different table). In the example on the right, Joe Hughes has the Doctor number of 0125 stored against his record, and if we look at Doctor number 0125, we see that the doctor in question is Dr Marvin Monroe. If records are linked to other records (held in a different table) by a unique number, this is no problem. If they are linked by unique text, this too is not generally a problem for automated data import.

The problem occurs where the text used is simply typed in, as it will often contain errors (spelling, punctuation etc) or be different in some way. Consider this table of clients:

And this table of doctors:

It may be obvious to the user that “Dr Patterson” and “Doc. Patterson” are the same person, but as far as data migration goes, these will not be simple to import.