Importing Data into SugarCRM
You can use the import functionality to import new data into Sugar as well as to update existing data. You can import data from CRM systems such as Salesforce.com, Contact Managers such as ACT!2005, and from Microsoft Excel. The import file can be a comma delimited or tab delimited file.
To import data from your previous CRM application or Contact Manager, you must first export the data from that application in a Comma Separated Values (.csv) file format to your local file system. You can then use the import function within a specific module to import the .csv file data into Sugar.
You can import data for the following Sugar modules from the Contact Managers and import file types listed below:
- Accounts: Comma delimited, tab delimited files, custom delimited file, Jigsaw, Salesforce.com, and ACT!.
- Contacts: Comma delimited, tab delimited files, custom delimited file, Microsoft Outlook, Salesforce.com, and ACT!
- Leads, Opportunities, and Cases: Comma delimited, tab delimited files, custom delimited files, and Salesforce.com.
- Targets: Comma delimited, tab delimited files, and custom delimited files.
- Notes: Comma delimited, tab delimited files, custom delimited files, and Salesforce.com.
Note: You cannot import data for custom Flex Relate fields.
Administrators can import the following data:
- Users. Comma delimited, tab delimited files, custom delimited files, and Salesforce.com.
- Product, Product Types, Product Categories, and Tax Rates. Comma delimited, tab delimited files, custom delimited files, and Salesforce.com.
Note: To successfully import drop-down list options, ensure that the import file contains database values (that is, item names) and not the display labels. This is because Sugar requires the ID associated with each option that is stored in the database.
Mapping Data and File Settings
When you import data, you will need to map fields that you are importing to fields that exist in the Sugar database. For example, to map the first and last names of contacts that you import into Sugar, these fields must exist in the Sugar database. If not, you will need to create these fields in Sugar before you import the data.
The last stage of the import process provides the option to view your import file settings under Advanced Options. Advanced Options lists the default settings in your My Account page. Ensure that the File Encoding format, the Date format, and the Time format match those in the import file. If not, change the settings under Advanced Options before you import the data. For example, if the import file is using EUC-JP and your default setting is UTF-8, you will need to change the default setting to EUC-JP.
File Encoding allows you to use an import file that is in another language. That is, a file with a different character encoding than what is specified for the Sugar instance. During the import, if the user selects the appropriate file encoding, then the data will be imported with the encoding specified for the Sugar instance.
You can save mappings for future use. You can also publish saved mappings to enable other users to use them. In the process of mapping data, you can assign a record to a user or a team.
Importing Account Data
If you are importing account data from another CRM system, typically, that system understands the distinction between a contact and an account – that one account can have multiple contacts, and has separate data for each contact. However, you can import just contact data from simpler Contact Manager applications– such as Microsoft Outlook. For more information, see “Importing Contact Data” on page 137.
To import account data
If you are importing account data from a CRM system, proceed to step 4. If you exported contact data, and want to import it as account data into Sugar, do the following:
- Copy your exported contacts.csv file and save it as Accounts.csv.
- Edit the Accounts.csv file using Excel. First, sort the file on the column which contains the company name.
As you scroll through your data, sorted by company name, you may see successive records which have the same company name if there is more than one contact from that account (in Sugar’s terminology). To avoid multiple copies of the same account within Sugar, you need to delete the duplicate contacts. And to make sure that the most complete information is attached to the account record, retain only the contact whose address and telephone information best represents the account as a whole.
Also look out for company names which are similar but not identical due to inconsistencies in the way the company name was entered – you should delete all duplicate records except the one with the company name spelled exactly how you want to see it in Sugar.
- Save the Excel file as a .csv file type.
- In the Shortcuts menu of the Accounts module, click Import and follow the process described in “To import data” on page 138.
After the account data has been imported, you can export contacts from your current Contact Manager application and then import them into Sugar.
Importing Contact Data
If you import a contact record that refers to an unknown account, then a new contact record is automatically created for an account of that name. Note that when account records are created automatically in this fashion, they are empty. That is, they have associated contacts but no address or telephone information. Therefore, you will need to manually add those details later. To avoid this task, it is recommended that you import the account data first to create complete records with address and telephone information (and other information depending on your previous CRM system) and then import contact data.
To export contacts from your current contact manager application
The process below describes exporting contact information through Outlook 2003. Other systems work in a similar manner.
- Under the File menu, select Import and Export.
- The Import and Export Wizard dialog box displays on the page.
- Select Export to a file and click Next.
- Select the option to create a file of the type Comma Separated Values (Windows), and click Next.
- Select an Outlook folder from which to export – typically your contacts folder – and click Next.
- Enter the filename and directory location for the exported file to be created, and click Next.
- To confirm your intention to export this file, click Finish.
Outlook 2003 creates a .csv file. To ensure that the data has been exported successfully, you can view the file using Microsoft Excel or a text editor.
Now you can import the contact data as described in “To import data” on page 138.
During import, ensure that you correctly map the names of the incoming fields with the names of the corresponding Sugar fields. If you are importing from Outlook, a particularly important field mapping is the incoming Company field to the Account Name field within Sugar. This is required to ensure that contacts are associated with the correct accounts.
To import data
- In the Shortcuts menu of the module’s home page, click Import.
The first step in the import process displays on the screen.
- Select one of the following as the data source:
Comma Delimited File. Select this option if the fields in the import file are separated by a comma or if the file is in .csv format.
Fields Qualified By: Identify the qualifier, if any, enclosing each field including delimiters in the import file. When you export Sugar records, the system uses double quotes as the qualifier.
From the drop-down list, select one of the following:
None. The import file does not contain any qualifiers.
Double Quotes. The import file contains double quote as qualifiers. For example:“abc”,“xyz”,“efg”.
Single Quotes. The import file contains single quotes as qualifiers. For example: ‘abc’,‘xyz’,‘efg’.
Other. The import file contains a qualifier other than single quotes or double quotes.
Tab Delimited File. Select this option if the fields in the import file are separated by a tab and the file extension is .txt.
Custom Delimited File. Select this option if the import file uses a delimiter other than a comma or a tab. Enter the delimiter in the Fields Delimited By field and enter the delimiter in the adjacent field.
Jigsaw. Select this option if the import file is located in the Jigsaw database.
Salesforce.com. Select this option if the import file is located in the Salesforce.com database. Instructions to import from Salesforce display on the screen.
Note: Salesforce record IDs are case-sensitive. Therefore, records that have the same ID but with variation in case are treated as separate records. However, MySQL and MS SQL databases do not recognize case-sensitivity and treat such IDs as one ID. As a result, some records may not be successfully imported, and parent-child relationships may be lost during import.
Act!2005. This option is available only for Sugar modules that are mapped to a corresponding module in ACT and the import data is located in the Act! 2005 database.
Microsoft Outlook. This option is available only to import contacts into Sugar.
- Under Import Action, select one of the following:
Create Records: Select this option to create new records.
If the import file contains record IDs that already exist in Sugar, and you do not map the IDs to the ID fields in the module, then new records will be created. If you map those IDs to the ID fields in the module, then import will not occur. The error log displays “ID already exists in this table” for each row containing an existing ID.
Create and Update Records: Select this option to create new records and update existing records with new values. To update records, the import file must contain the record IDs.
- Click Next to proceed to the next step in the import process.
Upload the file containing the import data from your local file system. Depending on the specified data source, the information you see on this page will vary. The screenshot below displays for the comma delimited option.
- In the Select file field, enter the path to the file location; or click Browse to navigate to the file location on your machine.
- If the file has a header row, check the Has Header box.
- Typically, a header row contains column titles such as Name and Address.
- Click Next to proceed to the next step in the import process.
The page displays the mapping between the fields in the Sugar database and the headers in the import file.
The Database Field column displays a drop-down list of all fields that exist in the Sugar database for the module. The system automatically finds possible matches for the field names in the Header row and selects them in the Database Field column. For unmapped fields, the Do not map this field option displays in the Database Field column. You can change the mapping if needed. If you do not want to map a field, selectDo not map this field.
The Header Row column displays the field titles in the header row of the import file.
The Default Value column provides the option to enter a value if the import file does not contain a value for the field. For example, when you import Leads, you can select the default value for the Lead Source, such as Direct Mail, Trade Show, and so on from the drop-down list in the Default Value column.
Row 1 displays the data from the first non-header field in the import file.
- To add additional data to a record for which the import file does not have a corresponding field, scroll down to the bottom of the page and click Add Field. Select an existing field in the Sugar database from the drop-down list and enter its value in the corresponding Default Value field.
The field with the new value is added to the list of database fields; to remove it, click the correspondingRemove Field button.
- To save the mapping as a custom mapping that you can use repeatedly, scroll down to the bottom of the page and enter a name in the Save Mapping as field.
Saved mappings are listed on the first page of the import process.
- To modify the datetime format or other settings to match those in the Import file, scroll down to the bottom of the page and click Show Advanced Options. Advanced Options displays the default settings, including the datetime format, from your My Account page. You can change the default settings if necessary.
- To check for duplicate entries, click Show Advanced Options. To check for duplicates using fields such as Team ID and Assigned users, move the desired indexes from the Index(es) Not Used column to the
- Index(es) Used column using the left arrow button.
To remove a field that you do not want to use to check for duplicates, use the right arrow to move it back to the Index(es) Not Used column. Use the up and down arrows to sort the order in which the indexes are checked for duplicates.
- Click Import Now to import the data.
The Import Results page lists the imported data and lists how many fields were successfully imported and how many were skipped.
If the import is unsuccessful or partially successful, the following page displays on the screen.
You can click the link to view the rows that were not imported and the error messages. You will be prompted to download the .csv file containing the information. You can download the file, correct the errors, and then import those records into Sugar.
- If you do not want to save the imported data because the field mapping is incorrect, click Undo Last Import; You can click Try Again to restart the import process.
- The system will not duplicate the record when you restart the import process.
- To import additional data, click Import More.
- To return to the module’s home page, click the Return to button. The screenshot above displays the Return to Contacts button.
To manage saved mappings
- To use a saved mapping, on the Import page, select the mapping before proceeding to Step 2 of the import process.
- To publish a mapping that you saved, on the Import page, select the mapping and click Publish.
- The mapping is now listed on the Import page of your team members. They have the option of deleting it, if needed.
- To un-publish a mapping that you published, click Un-publish on the Image page.
- The mapping is now no longer available to other members of your team.
Sage 100 Version 2018.4 Now Available for Download Let’s take a look at some of the most...Read More
Sage 100cloud vs. Sage 100 Classic: What’s the Difference? With all the talk and communication about Sage...Read More
Electronic Data Interchange (EDI) is an electronic exchange of business data presented in a standardized way....Read More