back to knowledgebase

How do I import data into the various modules in OpenCRM?

Rated 5.00 starRated 5.00 starRated 5.00 starRated 5.00 starRated 5.00 star Updated: 31 May 2022 17:02:01 OpenCRM::Leads OpenCRM::Contacts OpenCRM::Companies OpenCRM::Settings

To import data into OpenCRM you will need to have the import data in a compatible format. This is easy to do using a spreadsheet program, such as Microsoft Excel or OpenOffice, normally saving the file format as a CSV file. This is a common file type that is easy to manage and import.

First: Prepare the Data File

1. Ensure the data file is in CSV format (Note: for Mac users: Below the field showing the file name, you have a menu where you can select the file type. Here you should select the type CSV for Windows and this should ensure that the file is in a format recognised by the CRM import tool.)

2. Address fields (Building name, Address 1, Address 2, Address 3) need to be imported into one single field; use CONCATENATE formula in excel and you also need to insert line breaks. Click on the following link to see how to do this FAQ

3. Check telephone fields (excel has a habit of formatting as a number and removing the preceeding “0”)

4. Add a field called “Import Source” and populate this field with something that relates to the data “e.g. Bought Data October”. This is to help you identify where the data has come from.

5. Check data formatting

a. Date fields should be in the format yyyy-mm-dd (or) yyyy-mm-dd hh:mm

b. For tickbox fields data should be 1 or 0 (yes or no)

c. If wishing to assign import data to a particular user, add a field called USER ID

    i.      The user ID should be the USERNAME (login name) from OpenCRM (note this needs to be an exact match as the field is case sensitive)

    ii.      OR the externalid of the user, if users have been imported from an external system (e.g. sales force)

Next: Prepare OpenCRM

1. Add a custom field to the entity to import into called “Import Source” (if one doesn’t already exist)

2. Add custom fields for any fields present in the import source that are not in OpenCRM (even if they don’t seem relevant as they can easily be hidden/turned off)

Then: Time for the Import

1. Go to the module that you wish to import into (remember to apply some logic, in other words, if you are importing Contact Data, then go to the Contacts module, similarly with Company Data, this should be done through the Company module).

2. Select the Module

3. Scroll to the bottom of the screen and select the "Import" option

4. Follow the steps on screen, which include:

  • Select the Type of file to import - generally this would be a CSV file so select CUSTOM - Click NEXT
    • Next
      • Next
  • Select the CSV file to import using the BROWSE button

Note : Has Header means that the spreadsheets first row contained the data describing the columns details, this row is displayed, to help with the field mapping, but not imported. Click NEXT.

5. This will upload the data onto the server in a temporary area to help speed up the import process so may take a short while.

6. The next screen contains two columns, the left column allows you to select the OpenCRM Field that you are going to import into, the right column displays a sample of the data that is due to be imported, this helps with the field mapping.

7. When you have completed this field mapping, you can select the IMPORT NOW button to start the data import.

8. Saving as Custom Mapping - this allows you to see the field mapping that you have set up in case you need to use the same structure again. For details please see this FAQ: https://opencrm.co.uk/help/?kb=1494696&article=Can-I-make-a-field-mapping-within-Imports-available-to-other-people-in-my-team

Import Companies and Contacts

To link contacts to existing companies, when importing the contacts you will need to map either the Account External ID, if you mapped an external ID to your companies when you imported these, or Account ID, which will be the CRMID of the company you want to link a contact to. If you do not have either of these then you can map Company Name, this will link the contact to an existing company if one with exactly the same name exists or create one if it does not. If using the latter approach you should take care that if your contact file contains multiple contacts for a single company that the company name has been spelt consistently across all of its contacts.

Importing Activities

If you are importing Activities, it is likely that you will want to link these to existing Companies, Contacts, Opportunities or other entities in OpenCRM. If you are starting with a fresh OpenCRM system and want to import data including Activities, then you will need to import the other entities first (Companies, Contacts etc.) before importing the Activities. This is because you will use IDs to reference the records you are linking to the Activity, therefore the records need to actually exist in the system first.

The import process works in exactly the same way as other imports - you map the fields from your spreadsheet to the fields you want to populate in OpenCRM. To map to Contacts, Companies, Leads or Related To (ie any other module that you can link an Activity to, e.g. Opportunities), you will also need columns in your spreadsheet with either the CRM ID (ie the ID of the record used in OpenCRM) or an External ID (this is an ID you will have used when importing the Companies etc. first time round). If you do not have an External ID, you will need to use the CRM ID. To get this information you will need to run an export from your system to get you the CRM IDs for the records in the relevant module onto a spreadsheet

In the .csv file displayed below I have included both the CRM ID columns (for Contact, Company and a Related Opportunity) and the External ID.

When it comes to the import, map either one of the other, alongside the other Activity fields. Note that you should include fields for Type, Duration, Status and Due Date in your spreadsheet, therefore adding them in manually if they are not already present. This is to ensure that your Activities are displayed correctly on the Calendar and on the Pending (Not Started) and History (Done) sub-tabs on the related records.

Click to Start the import once you have mapped all the required fields. Once the data is imported it will be visible in the Activities module:

As you can see from the field mapping above, even though I did not map the names of the Company, Contact and (where relevant) Opportunity, the import has successfully linked those records to the Activities by using the CRM ID

Troubleshooting problem areas concerning data import

There are various reasons why a data import into OpenCRM doesn't work as you would expect it to. Please refer to this FAQ article which identifies some hotspots and presents solutions to these issues:

https://opencrm.co.uk/crm-knowledgebase/kb1401086/Things-to-consider-when-importing-data-into-OpenCRM-Troubleshooting-imports-that-are-not-working.html

Rate This Article
  • 1 star
  • 2 star
  • 3 star
  • 4 star
  • 5 star
Feedback and Comments
captcha code  


You may also be interested in: