Technical Desk - Migrating your existing data

When you move to a new CRM system, it is important that your existing data is preserved and can be transferred across to the new system. Think about all the time it has taken to enter your customer information in the form you currently use it ---- this represents a significant investment. Imagine if you had to have all of that data re-entered by hand! Not to mention the potential for data entry errors.

Thankfully, Commence has very powerful tools to be able to import data. With a little planning, and sometimes some scripting in say Excel, you will be able to get 100% conversion of your data.

Export your data

To begin with, you will need to export your data from your existing package. In general, this is best done by exporting to a comma separated ASCII file. Some packages may require some macros (e.g. Tracker 2 notes entries) to get the data completely exported.

Import / Export Templates

Next, define an Import / Export Template in Commence. These templates specify the rules for importing or exporting data, and are accessed via the Customize > Database menu selection.

The two main components of the template are the field mapping and rules. The field mapping specifies how each field in your data source (usually an ASCII file) maps to the field in Commence.

The rules mainly specify what to do with duplicate entries. If you allow duplicates in your category, you may choose to just add duplicates as new items. Or if you want each item to be unique, you may want to "ask" on each duplicate to prompt further attention. The rules also allow you to only import data to selected fields and update those fields only.

Relational Structure

As you are planning the data import, bear in mind that Commence is a relational database. This means that each category contains one type of information. For example, you may have a category of companies and a category of contacts. Commence establishes a "connection" to relate the contact information to a company.

A relational database is essential to efficiently represent your contact information, and to build a true CRM solution.

In most cases, your existing data will be in a simpler "flat file" structure. So you need to consider how to get this into the relational structure.

This is easily handled by having two import templates: in our example the fist template will import to the company category (skipping fields that are only relevant to the contact) and the second will import to the contact category (skipping fields that are only relevant to the company category).  The company import is run first. Then in the contact import, the company field is mapped to the connection to the company in the import template.

Tips and Tricks

Backup first!!! If you make a mistake, say in mapping or in how the data is handled, you can simply restore from the backup and start again. Without the back up you may damage the data you have already entered. If you make a mistake, and have forgotten to make a backup, you could be in for some grief.

To create a backup, go to the File > Open/Manage Database menu, select the active database, and click the backup button. This is the only safe way to make a backup of your Commence files (just making a copy of the directory is not correct).

A previous CRMtimes article has provided extensive infomration on correct backup procedures.

Disable syncing while you are importing, if your Commence database is enrolled in a workgroup. Again, if you make a mistake, you can restore from backup. But if you did not firstly disable syncing, your changes will have been sent to other clients in the workgroup.

To disable syncing, go to the Customise > Workgroup menu, then select your communication setting and set the sync interval to 0. To restart syncing (once you are happy with the import) go back to customise workgroup and restore the original setting. Then exit Commence and restart.

Before doing an import, make a note of the item count in the status bar - to see how many items already exist in the category. Then after the import, note the number of items. This should correlate with the number of lines of data in the import file.

If you are importing into a new database, and are sure there is no data already in the category you are importing to, you may be able to make good use of the Edit > Delete All Items command. This may be faster than doing a restore if you find a mistake in the import template. Take care using this command!

Microsoft Excel

The humble Excel spreadsheet will be your friend during the import process. It is easy to use Excel to open a comma or tab separated ASCII file, and split the data into fields (ie columns in Excel).

You can instruct Excel to treat the data in each column as a particular data type - for example, date, text, number. This is particularly useful for correct date handling.

Using Excel, its easy to examine the data in each field, to assist you in setting up the field mapping. Remember you need a source field for each field in Commence, so if the same source field needs to go into two or more Commence fields, its a simple matter to copy and paste a column in Excel. You could use Excels' formulas to derive additional data from earlier columns.

You can also use Excel's extensive VB scripting to do further data massaging and manipulation. For example, you may need a unique sequence number in a column - this can be coded.

When you are ready to import to Commence, its simple to write out the comma separated ASCII, with the File > Save As menu, selecting the .csv file type. The file produced can then be imported to Commence.

By thinking carefully about what you want to achieve, reviewing your data in Excel and taking care with proper backups, the import procedure will be quick, accurate and complete.