One of the most important aspects of any CRM software is the ability to import and export data.
The former allows you to input data en masse using data sources, such as Comma Separated Values (CSV) files or other file formats. Through this functionality, the process of migrating data from other systems, such as an accounting solution, is simplified. It is also helpful for importing lead lists that you may obtain at a trade show, networking event, and so on.
Exporting capabilities are equally important. The export functionality allows you to easily extract valuable data from your CRM system, permitting you to process it in other systems, such as a data scrubbing service.
The ability to insert and extract data from the CRM system through import and export tools becomes increasingly important as the role of the CRM tool grows within an organization. This should be expected within your organization too, as your goal should be to create a culture where the CRM system becomes the hub or central storage point for all your business and pertinent customer data (except for accounting information).
SugarCRM supports the importing of various kinds of data from several different popular competitive systems, and from online data services or custom created data files.
Some of the highlights are as follows:
Accounts may be imported from Salesforce.com, from ACT!, or from most other systems through a custom comma-delimited or tab-delimited file
Contacts may be imported from Salesforce.com, from ACT!, Outlook, or from most other systems through a custom comma-delimited or tab-delimited file
Leads may be imported from Salesforce.com, or from most other systems through a custom comma-delimited or tab-delimited file
Just like leads, opportunities, calls, meetings, and so on, various other data types can also be imported from Salesforce.com or through custom comma-delimited or tab-delimited files.
It is important to note that the options for Salesforce.com, Microsoft Outlook, and other competitive products merely provide a shortcut to field mappings which should save you some time in performing your import. The import tool, however, does not directly communicate with these other products to extract the data that is to be imported, and instead, still relies upon the use of a CSV file to perform the import.
The onus is on you to export the data from the other system into a properly formatted CSV file that in turn can be used within the import facility in SugarCRM.
Before we take a look at a few examples of the import tool in action, let us briefly mention an additional feature native to the import tool. In addition to allowing you to create new records in various modules, the import tool also allows you to update existing records. This is accomplished by using match keys, which we will discuss later in this chapter.
Let us take a look at the process of importing accounts and contacts.
If you are importing account data from another CRM system, chances are that the system understands the distinction between an account and a contact. As a refresher, the former is usually an organization or company, the latter an individual. Moreover, one account can have multiple contacts, each with separate data.
Importing contacts into SugarCRM is fairly straightforward. First you use your old CRM application or a contact manager to export the data into a .CSV file format. Then you use the import function within the Contacts module (accessible through the navigation shortcuts box) to import the data. If a contact record that is imported refers to an unknown account, then a new record is automatically created for an account of that name.
However, one thing to watch out for is that when account records are created automatically in this fashion, they are essentially empty—they have associated contacts, but no address or telephone information is recorded. As a result, you should typically import your account data first, creating the records complete with address and telephone information (plus perhaps account type and lots of other information, depending on your old CRM system). This avoids creating rather empty account records, and having to manually add the rest of their information later.
See the following sections for the exact steps for importing accounts, contacts, and leads.
Your source CSV file is likely to have data represented in a pattern that matches the following: "Value" followed by a comma.
For example:
"Angel","Magaña","ACME CRM"
"John","Smith","Big Company"
This pattern is extremely important, although it should be noted that the comma is not used after the last value. In this example, three columns are represented: first name, last name, and company name.
Furthermore, new records are separated by a new line marker. Thus, in the preceding example, the line with Angel Magaña as the name represents one record, and the line with John Smith represents a separate record.
If you are wondering about the possibility of using alternate delimiters besides double quotes, it is certainly possible. However, you must be consistent both across any given line as well as the entire file. You cannot use one delimiter for one column and another for a different column, nor one for one record and a separate one for a different record.
If the CSV file that you are attempting to import does not have a consistent pattern, your import will fail. Some common problems that occur when the file is not properly formatted include the following:
Available source field list is not complete
Import fails after successfully importing one or more records
Errors reading the source file
Truncated data
Along with the list of common problems there is also a list of common sources of said issues. They include the following:
Extraneous commas, double quotes or new line markers
Mix of delimited and non-delimited data
Often, the aforementioned problems are the result of the export process that was used to create the CSV file you are attempting to import.
For example, it is not uncommon for Microsoft applications to not delimit numeric values. This has the potential of causing problems because it disturbs the pattern as you end up with a line containing some columns with delimited values and other values that are not delimited.
Other issues are the result of the data itself. For example, if someone inputs a contact name with a comma (,) such as, Magaña, Angel, the resulting exported file may include a value that contains an extraneous comma. The extra comma throws off the file's pattern and causes read problems.
Another possibility is that your export file includes a multi-line text field. For example, a notes or comments area. Those fields are prime areas for extraneous new line markers, as users would have likely used a carriage return at some point while typing in the field in the other application. Given that a new line marker denotes a new record, the pattern of the file would again be skewed and the source file would not be read correctly.
All of these issues must be corrected through external tools. You should not attempt to import your data until you have performed a preliminary analysis of your source files to ensure that these issues are eliminated. Failing to do so only leads to frustration and loss of time.
This is also an opportune time to cleanse your data of duplicate records and other integrity issues. Performing this step before you import the data will help ensure that the end result in SugarCRM is of good quality and allows you to make immediate use of it.
We will assume that the file you exported is already in the proper state for importing.
Earlier in this section we touched on the point that other CRM systems from which you may be getting your source data might already recognize and compensate for the difference between accounts and contacts. However, if your data is being imported from a simpler contact manager—such as Microsoft Outlook, then the only data available is contact data, and you will need to be a bit creative if you wish to create an account-contact relationship for those records. See the section titled Export contacts from your current contact manager later in this chapter if you require assistance with exporting your data from Microsoft Outlook.
If you are importing account data from a system that distinguishes between accounts and contacts, proceed to step 6. If you only have exported contact data, and need to massage, or manually clean it to act as account data to be imported, perform steps 1 to 4 as follows (the example assumes the original data is in a file named Contacts.csv):
1. Copy your exported
Contacts.CSVfile, and name the copied fileAccounts.CSV.2. Edit the
Accounts.CSVfile using Excel. First, sort the file according to the company name.3. Now the more complex part: As you scroll through your data sorted by company name, you will see successive records that have the same company name, because there is more than one contact from that account. To avoid multiple copies of the same account within SugarCRM, you may need to delete or merge these duplicates. Of course, you may have valid reasons for keeping multiple records with the same account name. For example, if you work with differing branches of the same organization.
4. Also look out for company names that 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 as you wish to see it in SugarCRM.
5. Now that you have a nice clean set of account data, save the Excel file as a
.CSVfile type, and let's proceed to import this account data.6. Click on the Import Accounts function within the Navigation Shortcuts Box of the Accounts module.
7. Specify the Data Source. Select Salesforce.com, ACT!, Comma Delimited, or Tab Delimited. Choose the appropriate delimiter (if necessary) and then click on the Next button to continue. For massaged or manually cleansed Outlook files, where the field names no longer match exactly what is exported from Outlook, use the Comma Delimited data source.
8. Upload the export file. Use the Browse button to locate the
Accounts.CSVdata file, and then click on the Next button to continue.9. Confirm Fields and Import. This screen (see the next figure) shows four columns of data. Column 2 (Header Row) is the key—this contains the names of the fields being exported from your old CRM or contact manager. Column 4 shows an example data from the first record you are about to import. Column 1 (Database Field) is where you come in—you need to use all of the drop-down box controls in this column to select the fields within SugarCRM into which each incoming account field is imported (or choose to skip it by selecting Do not map this field).
 |
10. Spend some time with this, exploring the names of the incoming fields, and the names of the corresponding SugarCRM fields, until you are sure you have defined the optimum mapping between them. If you are importing from Outlook, a particularly important field mapping to get right is the mapping of the incoming Company field to the Account Name field within SugarCRM.
11. When you are satisfied you have the field mapping right, click on the Import Now button, at the bottom of the screen. Before you do this you may choose to click on the Save As Custom Mapping checkbox, and provide a name for this mapping so that it may be used again in the future.
12. The Import Results screen will be displayed. It will summarize the number of records imported and skipped, as well as provide information on the reasons for them being skipped. Below the summary there will be a complete list of all the data that was imported.
13. You can now choose to click on the Undo Last Import, Import More, or Finished buttons. Click on the Finished button if you are satisfied with the results of the data import, or Undo Last Import if you want to go back and try again—possibly the result of needing to adjust your field mappings.
Now that you have a set of account records with fully descriptive data, let's import your contact data as follows:
1. Click on the Import Contacts function within the Navigation Shortcuts Box of the Contacts module.
2. Specify the data source: Select Salesforce.com, Microsoft Outlook, ACT!, Comma Delimited, or Tab Delimited and then click on the Next button to continue.
3. Upload the export file: Use the Browse button to locate the data file exported by your contact manager, and then click on the Next button to continue.
4. Confirm Fields and Import: This screen (see the previous image) shows four columns of data. Column 2 (Header Row) is the key—this contains the names of the fields being exported from your old CRM or contact manager. Column 4 shows example data from the first record you are about to import. Column 1 (Database Field) is where you come in—you need to use every drop-down box control in this column to select the fields within SugarCRM, into which each incoming Contact field is imported.
5. Spend some time with this, exploring the names of the incoming fields, and the names of the corresponding SugarCRM fields, until you are sure you have defined the optimum mapping between them. A particularly important field mapping to get right is to map the incoming Company field to the Account Name field within SugarCRM, so that contacts are associated with the correct accounts. (Make sure that you have corrected inconsistent company names that you found in your data in step 4 of the Import Accounts section.)
6. When you are satisfied, and you have the field mapping right, click on the Import Now button, at the bottom right of the screen. Before you do so, you may choose to click on the Save As Custom Mapping checkbox, and provide a name for this mapping so that it may be used again in future.
7. The Import Results screen is displayed. It will summarize how many records were successfully imported, how many were skipped over, and the reasons why they were skipped over. Below the summary there will be complete lists of all the data that have been imported—both Contacts, and any Accounts that were automatically created.
8. You can now choose to click on the Undo Last Import, Import More, or Finished buttons. Click on the Finished button if you are satisfied with the results of the data import, or Undo Last Import if you want to go back and try again—usually to improve the field mapping.
Leads and opportunities are typically only tracked by a full CRM system, not a simple contact manager. If your old system is Microsoft Outlook or a similar contact manager, then you may not have any data to import. If you are migrating from a full CRM system, such as Salesforce.com, then the lead and opportunity data may be exported from that system and imported into SugarCRM in a very similar fashion to importing contact data, as described earlier in the chapter.
Earlier in this chapter it was mentioned that it is possible to update existing records through the import utility. To accomplish this, we must have a source file that contains a match key and a related index.
A match key is a unique value that exists both within the records in the target database and within the source data file being imported. It is used to link a record within the source file to a record that is already in the SugarCRM database. If the data in your source file was originally exported from SugarCRM, you can use the ID value as the key. If not, you will have to formulate a solution independently.
The key value itself can be a number, a character, or a mix thereof. In addition, its length does not matter. However, the value does need to meet two criteria. First, the value must be unique. Uniqueness ensures that only one record from the SugarCRM database would match any of the records in the source data file. Secondly, the SugarCRM database field containing the match key value must be indexed. Depending on which field within the SugarCRM database contains the match key value, it may be necessary for you to create a custom index.
The process of creating a custom index will vary slightly depending on your selected database platform. If you are unsure about the process, refer to the MySQL and Microsoft SQL server documentation, located at the following websites:
Our example assumes that we are working with the Accounts module and are using a custom field named custom_c to store the match key value, for which we have created a custom index. As we have met the requirements, we can proceed to enable the update feature by clicking on Create and Update Records on the Import Step 1 screen, as illustrated in the following image:
 |
Proceed to select your file and advance to the import field mappings screen. There you will click on the Show Advanced Options button located at the bottom left. After clicking on it, you should see something similar to the following on the bottom right of the screen:
 |
This area (as shown in the preceding image) permits us to select our match key by selecting its related index. Notice that the options are limited to only those indexes that are defined by SugarCRM, although it is possible to modify the list through a minor customization.
Assuming you have applied a custom index to your database (as we have for the custom_c field), you will need to make your SugarCRM system aware of the new index.
To add it to your system's configuration, you must first use Notepad, vi, or other text editor to create a file named custom.php with content similar to the following:
<?php
$dictionary['Account']['indices'][] = array('name' =>'idx_custom', 'type'=>'index', 'fields'=>array('custom_c'));
?>
Make a note of the Account reference as this specifies the module to be affected. Notice that it is specified in the singular form. Users will often make the mistake of specifying the module name in its plural form which will cause these types of customizations to fail. All module references must be in singular form.
The "name" section represents the descriptor that is displayed in the index list in the previous image. Feel free to use the value specified in this example or use a descriptor of your choice.
The "type" section should remain as illustrated in the preceding code snippet, but do take note of the "fields" section that immediately follows. This section is used to specify the column with the custom index. You will need to adjust it accordingly to reflect the name of the field that you have selected, in our case, custom_c.
Once finished, place the file in the following directory:
custom/Extension/modules/Accounts/Ext/Vardefs.
Access SugarCRM with an admin level account, and use the Repair option in the Admin control panel to perform a repair on the accounts module. The next time you use the import wizard in the accounts module, the new index will be available for selection as one of your match keys.