SugarCRM: Import Wizard Tips

In this SugarCRM post I will discuss some tips on importing data into SugarCRM using the import wizard.

To start off with I would like to review what the import wizard is intended for. Even though the import wizard can import mass amounts of data, it’s intended use is to allow users to easily import data from various outlets such as trade shows, event attendance lists, and other exports that get appended to the current record set.

Tip 1: Database Migrations

As of SugarCRM 5.1 the import wizard is robust enough to import tens of thousands and even hundreds of thousands of records from a database dump. For an accounts migration starting point the import wizard will do the job. The problem comes when you want to maintain relationships during the migration.

When you are migrating data, each system relates records using different keys or formats in the database. SugarCRM is Accounts driven and relates using unique ids. This means that Accounts will go in smoothly but to import a related record, like a related Contact, you will have to get the contacts related account name and the SugarCRM Account id, merge the id in to the contact information and then import. This process is best left to tools like Talend and not the import wizard.

Tip 2: CSV Files

Most people use the comma separated values ( CSV ) format to import data. CSV does the job well but there are things that you need to check for before you import. The first is commas. Since you are importing using CSV, any time that the import wizard ( or Excel ) sees a comma is will think that it is working with the next column of data. This will completely throw off your imports. To fix this you will need to cleanse the data of commas before you import. You can usually substitute with a space or a dash depending on the data. The other alternative is to use another format for importing such as tab delimited since most dumps don’t have tabs in the data, but it is still something to watch out for.

The same thing that goes for commas goes for the the field qualifier. If each entry is wrapped in single quotes and the data fields have single quotes ( Ex: Janes’s Store ) then your data could get imported incorrectly.

Tip 3: Field Qualification and Saving Templates

Fields in CSV files are usually wrapped in quotes, double quotes, or are not wrapped leaving just the comma separators. When you are building the import mapping you are asked to choose which field qualification to use. My tip is to add which type you use in the naming of the template. The reason is that in SugarCRM’s current implementation if you import data using a template with the quotes field qualification and the data actually uses double quotes then the mapping will be blank. This isn’t a problem with a one time import or when the same person does the import the same way each time, but this is not always the case. You may receive weekly exports of the same type of data but from different people with different version of MS Excel/Open Office Calc causing different field qualifiers.

Tip 4: Drop Downs

All drop down boxes added in  Studio need to have a NULL entry as the first option. This is where you click the add button in the drop down editor with no data in the fields. The reason is that when you import data you have to specify a default value for fields that are not filled in. If you don’t have a NULL entry as an option then the import wizard will automatically assign the first value in the list even though the data for that column may not exist.

Tip 5: Matching Data for fields

If you are importing data that requires custom fields or custom values, then each of the custom fields/values needs to exactly match the data being imported. Let look at an example:

DropDown Example

Your data has field/column called Manager Level and the fields are filled in with three options which are Manager, Middle Manager, and Senior Manager.

In SugarCRM you have drop down box that has Manager and Senior Manager.

When you import the data you will get an error for every record that has Middle Manager as a value since it is not in the SugarCRM drop down. For data accuracy reasons SugarCRM is not going to automatically add that as a drop down item.

Hopefully these tips will help you the next time you import data into SugarCRM. If you run into any others that need to be added to the list then feel free to comment or send me an email.

Tags: , , ,

Sunday, December 28th, 2008 SugarCRM, Uncategorized
  • Pingback: SugarCRM Atlanta Meetup - Tips, Tricks, and Tools | JoshSweeney

  • AndyC

    One thing we’re struggling with is delimiting entries in a mulit-select field on import?

  • http://systemsconsciousness.com/ dean

    Hey great site. I'd love to collaborate… systemsconsciousness.com.

    p33ce,
    Dean

  • http://systemsconsciousness.com/ dean

    Hey great site. I'd love to collaborate… systemsconsciousness.com.

    p33ce,
    Dean

  • http://www.alt-invest.net Josh Sweeney
  • Max

    Hi,
    thanks for the giude but what about the possibility to add more than an email address to the same contact? Do you have a tip about it?

  • http://www.alt-invest.net Josh Sweeney

    Max,

    What kind of data are you looking to add to a contact? Are you looking to do an update to an existing contact or add a new contact with data?

    Thanks,
    Josh

  • Battlebrown

    in sugarcrm CE 6.1 the only way to make the import of data work and get correct date formats is to have the user preferences for date format set to be the same as the locale setting for date format (under administration). I also choose to use yyyy-mm-dd format and once this was set up data could be imported with various delimiters e.g. “-”, “.” I did not try alternate y, m, d order, but my guess is this won't be a problem. So a mismatch between user and locale date formats cause problems.