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