Data Migration Guide

From Charitylog Manual
Redirect page
Jump to: navigation, search

Redirect to:

About data migration

It is usually the case that an organisation buying Charitylog/Crossdata 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.

Data Migration is the process by which this data is imported into your new system. This data can usually be divided into two categories:

  1. Data about people or organisations. For example, your list of current clients, and perhaps their associated data such as date of birth, address, phone number, etc.
  2. Data about contacts with these people. For example, a client's case history, with the details of what work has been done and when.

Data about people or organisations is usually easier to import correctly than data about contacts.

Please note that the setup of your system is not considered data migration. Lists of Projects (services), drop-down lists and so on are initially configured using your Pre-Implementation Spreadsheet.

There are three common ways that you can migrate data.

Manual data migration

The simplest way of importing your data is to have your own staff or volunteers do it. They can put clients onto your new system, add details about those clients, and even fill in case histories (the ysstem will allow you to record contacts retrospectively).

The standard data migration spreadsheet

We can provide you with a standard spreadsheet for you to put your data in. This is primarily for data about people or organisations, although you can use it to record the fact that a client is associated with a particular Project. The data entered must conform to the specification you will be given with the spreadsheet. Once you have filled in the spreadsheet, this can be imported into your new system.

There is no limit to the number of records you can enter via this spreadsheet, so this can be a very cost-effective way of migrating your data, providing you are able to extract it from your existing system(s) and insert it in the spreadsheet correctly. Please contact the office on 01989 763 691 for pricing.

Custom, automated data migration

You can also opt to have your data migrated with assistance from our programming team. This is the most complicated option and the price is entirely dependent on the complexity of the work needed, so if you are considering this option, please contact the office to discuss your options and obtain a quote (customer support: 01989 763 691).

The preferred format to receive data for import into the system is in the form of Spreadsheets, usually Excel files (.xls or .xlsx). We can also accept Microsoft Access databases from which we can extract the relevant data, but this creates an extra 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 system.

The system "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 the system 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 the system.

Key Fields

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

In some instances an organisation 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:

Data migration 2.png

And this table of doctors:

Data migration 3.png

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.

What happens once the programming team have the required data?

A diagram of the data migration process

Once we have the data that is to be imported we will review it to see that it meets the expected criteria. The next step for us will be to write a program to aid the import of the data. Assuming the data is in good order, this will be a fairly straightforward process, resulting in a test system (a snapshot version of your live system with the migrated data added) being made available to you after the import is complete, so you can check it over for any errors or omissions. Once you are satisfied with the data contained, we will re-migrate the latest version of your data to your main (live) system.

Choosing how to import your data

One of the most important factors in deciding to have the data imported is 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 are only 20 records or so, it is better to have them manually imported.

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.