Data Import: Step 1 - Data Preparation

There are some important points to consider before processing a Data import into webCRM:

The quality of your Data. Some basic analysis will tell you if the Data is uniform where it should be (Dates, Names with leading upper case, Addresses with Cities and Postcodes each in one column etc)
If you need to map to fields that do not currently exist in your system, you may need to create Custom fields, or adjust system fields
Mapped out all the columns in your raw data to one or more fields in webCRM and noted which module they are in
If you import Data to Drop down lists, the raw Data options for that field should exactly match the options in the Drop down list, including case. Similarly for User names, you must have an exact match in the raw Data to the User name in the system
If one field is identical for all the imported Data, for example Territory = Europe for all the records you plan to import, then you do not have to create a separate column in the raw Data and populate it for all the rows. webCRM provides a ‘same value for all’ option that you can use when you set up the import
If and how you are going to search for duplicates (for example by Organisation name + Division, or Postcode, or by Email address for Persons)
If records already exist in the system, should the data be updated or the record be skipped altogether
If you have Organisations with identical Names (for example lots of regional offices for a national Company) then you could map the Town or Postcode to the Division to distinguish them

webCRM Data import allows you to both update existing records and create new ones. You can also create Activities and Opportunities as well as importing Data.

You can import to update and/or create new records for the following:

Activities
Opportunities
Deliveries
Meetings
Products
Documents
Emails

As an example, we are going to work through the following:

Creating Organisations
Creating Contacts
Creating Activities and allocating a user to that Activity

When importing data into webCRM it is important to prepare the Data properly. Use the following checklist to ensure your Data is ready for import:

Data is in CSV or txt format (It is recommended to prepare the data in Excel and then use Save As... CSV)
The text does not contain any ";" (Use find and replace function in Excel)
The values for drop down lists are exactly the same as in webCRM including upper case letters, exact spellings, use of hyphens etc.
User names must be entered as stated under 'Users' and not only the First Name, initials or other abbreviations
There is an Organisation name in the raw data for every row, else the entire row will be ignored

Example of prepared Data for import

The following shows an example spreadsheet saved in csv format. Whilst we have shown these sheets separately for easy reading, please consider them one continuous sheet:

Organisation Data

You will see from the data that there are two entries with the same Organisation name and Division. The webCRM Import will (by default) see these records as duplicates and only create one. If you wish to create records using the exact same Organisation name, but for different locations, then use the Division field to record the location. Duplicate checking will be based on Organisation name + Division and therefore it would create two records.

If, for example, webCRM had two affiliates in the UK, you could create two Organisations with the name webCRM but state Birmingham as the Division for one of them and London for the other.

Our recommendation, however would be to look through your data or use Excel and check for duplicates before import.

Your options are to create a new column in the raw Data and populate it accordingly (as per our example), or if the Data is uniform and good quality, you could simply plan to map the City field in the raw data to the Division field in webCRM.

Persons Data

We have included User names as the Sales Person in column I, i.e. the Responsible User for that Organisation. We have also created a date for the Activity.

NOTE: the Responsible User must be written 100% correct with their full name.

These fields will be used to create the Person records linked to the Organisation records and they will also create an Activity for each. The import routine will also create two Persons under Tesco.

Within the Newsletter column you will notice 'y' in some of the records. This character is used to check a webCRM Custom field checkbox, in this case to confirm the Person will be added to the Newsletter mailing list.

Preparing Data for import to Multi select fields

Multi select fields enable more than one option to be set. This creates the need for raw Data to be prepared differently.

For example, a Multi select field could be Interests. The available items for selection are:

1. Product A
2. Product B
3. Product C
4. Service A
5. Service B
6. Service C

Your raw data will need to be prepared so that all the items for one record are held in the same field and separated by a comma. For example: “Product A,Service B,Service C”

If your raw data currently shows the items in separate columns, then you can use Excel formulae (concatenation) and create a new column with each value. Your formula would look something like this: =CONCATENATE(H80,",",I80,",",J80)

NOTE: As each item has its own unique number in the multi select field, you can use this same number in your source file instead of the actual value:

Product A, Service B

becomes

1,5

Testing Your Import

Before you start importing Data, we always advise that you run through the following steps and test import a few lines of Data. You can do this by making a copy of your CSV and removing most of the rows. That way you can check that you have configured your import correctly, before running a complete import.

If you have 1000s of Data rows in your spreadsheet this can save you a lot of time and worry if you get it wrong or you wish to adjust the mappings.