Tips on importing data into SugarCRM using Excel

The most dreadful thing about moving to a new CRM is the pain that you go through to get data into a new system. SugarCRM offers a fairly simple importing tool that you can use to import any CSV file that can be created using Excel.

We have done a lot of migrations of data for customers over the years and we’ve found a few tricks to get data cleaned and into the system. I’ve put down a few tips on how to clean your data and steps to importing it into SugarCRM.

Please note this is only for importing data for Accounts and Contacts. It can get a bit more complicated when dealing with notes or opportunities.

First, start with one big Excel file

Most of the time our customers provide us with all of their data in multiple Excel files. We end up combining them into one document for ease of cleansing and organizing the data structures.

Start with Accounts

The first thing to do is take your big master file and save it as Accounts.xlsx. Likely your spreadsheet will have a lot of duplicate Account Name’s in the rows and we need to eliminate duplicates so that we can import only 1 company at a time. The Account Name field must be unique and exact for each person from that company.

First thing we do is remove duplicates through filtering in Excel. Go to your data options and click on Filter then Advanced Filter. This will give you the following screen:

The trick here is to highlight the column with your Account Names and filter based on Unique Records Only. Once you’ve done this you can select all and copy this all into a new Excel spreadsheet.

Once you’ve done this, save your new spreadsheet with the unique Account Name records as a CSV file. You can then import this into Accounts in SugarCRM.

Now, on to contacts

This part should be straight forward. If you’ve made any adjustments to the Account Names (misspelling or what not) then make sure that this is reflected in the big master file. The Account Names can be automatically mapped to the Accounts you created above.

Go ahead and save the contacts into a new spreadsheet and save it as a CSV file. You can now import this file into SugarCRM.

Why two steps?

You could get away with doing everything in one shot by simply importing everything into the Contacts module. SugarCRM is smart enough to auto create the Accounts on its own but the problem has to do with unique names. By breaking it up into two stages it helps you cleanse the data a bit to make sure you’re importing fairly clean data.

Hopefully this basic step helps you in cleaning up your data a bit before uploading it to your SugarCRM instance.

2 Comments

I have had my share of problems with importing data in SugarCRM… I think breaking up things in two imports is indeed the best way to avoid many of these problems.

That being said, I thought SugarCRM had the capability to do duplicates cleanup by itself. Why would you say that it is required before hand if we can simply have that step done automatically by SugarCRM.

Derek
on September 3, 2014 at 12:41 am

Thanks for the comments Alexis. You can definitely take advantage of using SugarCRM’s duplicate detection during import if you’d like. It really comes down to the strategy that you have during import.

At Eligeo CRM we are now using tools such as Talend Studio to help with data import. Because of that we are now doing a lot of scrubbing outside of SugarCRM which for us is less time consuming. Getting the data cleaned up before importing saves a lot of headaches of dealing with a system you might not be used to using.