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
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.
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
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
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!
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
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