W-Systems joins SugarCRM! Read Blog
SugarCRM has many powerful features, especially in their Import Wizard, which can import data into other modules, including the most commonly used task of easily importing Excel spreadsheet data from trade shows or purchased lists.
In this tutorial we will show you how to set it up, along with guidelines for preparing your spreadsheets so that the imports runs smoothly.
Starting the Import Wizard
Once you’ve saved your Excel data as a .csv file, you can access the Wizard from the Leads dropdown menu under ‘Import Leads’.
The wizard matches the column header names against Sugar fields, but you can change the mapping as necessary. You can also save the mapping so that you can do a test run and then re-use it.
Cleaning the Data
You can save time and headaches by reviewing your data and cleaning it up before the import. Here are some steps to make the import process go smoothly. (We assume that Row 1 of your spreadsheet contains column headings.)
1. Make a copy of your original spreadsheet.
It’s always good to have a backup in case you change your mind about the import.
2. Change any column heading = ‘ID’.
Sugar creates its own unique identifiers on import. If you have an identifier field that is important to keep, then rename that column to something other than ‘ID’. Creating new records with a non-standard ID field will cause problems down the road.
3. Simplify data mapping with appropriate column headers.
If you do a lot of importing, you can make field mapping easier by changing the Excel column headers to match the module field names. Then Sugar will do the mapping for you. For example: ‘Primary Address Street’ rather than ‘Street’.
4. Add additional columns.
Typically, you will want to add a column for ‘Lead Source’ and copy ‘Trade Show’ into all the rows. And also add a column called ‘Lead Source Description’ = [name of the trade show]. You can also concatenate other columns on the spreadsheet that have valuable data into the ‘Lead Source Description’ column. To concatenate two columns, use an Excel formula like: =K2 & “ “ & M2
5. Remove unneeded columns.
If a spreadsheet column isn’t needed in Sugar, remove it before import to simplify the data mapping.
6. Specify an Assigned User.
If you don’t specify an assigned user, the records you are importing will be assigned to you. If the entire batch should be assigned to a single user, you can use the ‘Add Field’ button on the Data Mapping tab: type in the User Full Name and select ‘Assigned User Name’ from the dropdown. Otherwise, put an ‘Assigned User Name’ column in the spreadsheet and fill in the appropriate values.
7. Add leading zeroes to zip codes.
Excel will often treat zip codes as numbers and remove a leading zero. To fix this:
8. Separate First and Last Names.
If your data contains names in a single column with column heading ‘Name’, Sugar can separate it into first and last names. However, names like ‘R. Eliot Spenser, Jr.’ will end up as First Name=R., Last Name=Eliot Spenser, Jr. If you want more control over name formatting, use the Excel Text to Column Wizard (on the Data tab) to parse it into separate fields.
9. Dropdown fields.
Your data must exactly match the Dropdown Item Name or Display Value. Any incoming data that does not exactly match the Dropdown value will cause the record to be flagged as an error and not imported.
10. Dropdown fields with no data.
Keep in mind that all Sugar Dropdown fields have a default value. If your spreadsheet data doesn’t have values for a Dropdown field, the default value will be used. This can be a problem if the list doesn’t have ‘blank’ or ‘Unknown’ as the default value.
11. MultiSelect fields.
If you want to enter multiple values, separate them with commas. For example: Government, Manufacturing. (The space after the comma is optional.)
12. Format all columns as text and left justify.
While not strictly necessary, it lets you see if there are leading blanks in any of your fields which sometimes occurs with contact and company names.
13. Remove any duplicates.
You may want to sort by Last Name, First Name to help identify them. See duplicate filtering below.
14. Watch for delimiters.
Your Excel spreadsheet is going to be saved as a .csv file so Sugar can import it. However, if you have commas in your data, it may cause the .csv file to be formatted incorrectly. You can check by opening the csv file with Notepad and seeing if the fields with commas have been enclosed in double quotes. (Typically, the errors that Sugar reports look like bad data in a dropdown list because columns have shifted to the right.) If your version of Excel doesn’t handle the commas, search the entire spreadsheet for commas and replace with blanks, hyphens or other appropriate value.
Running the Import
15. Save spreadsheet as .csv file.
Reply ‘Yes’ to the warning message below. Note that now you’re looking at the .csv file you just created and not the original spreadsheet.
16. Select duplicate filters.
You can ask Sugar to check for duplicates by selecting from a predefined list of fields and creating a .csv file of the excluded duplicate rows. Email address is often used. On-Site clients have the ability to add additional fields themselves. On-Premise customers can open a support case to request that a field be added for import duplicate checking.
17. Create a test file.
Copy the first 10-20 lines of your cleaned-up spreadsheet data to a separate .csv file and do a test import. You can save your import mapping to re-use if the import looks good. If there were problems, you can undo the import.
18. Check the results in a different window.
As long as the screen above is displayed, you can undo the import if you find any problems. So make sure to right click the hyperlinked name and open in a new tab or window.
19. Use the Report Wizard to check your data.
If you want a quick way to verify your results, let your import sit on the screen above and open the Reports module in a new browser tab. You can create a Rows and Columns report with the data to be verified and scan it easily.
20. Download duplicates and errors to easily fix them.
If you click on the Duplicates or Errors tab, you’ll be able to download the records with problems, fix them and reload.
The SugarCRM Import Wizard is available from the menu for other modules besides Leads, and works the same way. For users who are authorized, it can also be used to update existing records by exporting the records, making the desired corrections and then importing them again.