W-Systems joins SugarCRM! Read Blog

Data Cleansing in Sugar – Part 2 (Using Import and Export)

by Lynn Catterson on December 9, 2015

11 minute read

In part 1 of this blog, we discussed using Mass Update to update data in Sugar. There are cases where exporting the records is a better option.  The data can be updated externally and then re-imported.

Some examples where this might be a better option are:

  1. When you have more than 1,000 records to update.
  2. You have multiple changes to make.
  3. You want to add field(s) of data.
  4. You can’t easily build a filter to identify the records to update.

Maybe when you started off with your CRM database, you allowed free typing in text fields, and you’ve discovered that without consistent data, you can’t adequately create filters on your data for marketing. Now you’d like a Drop-Down or Multi-select field that is consistent and easily searchable.

Perhaps you would now like to add a field for LinkedIn Accounts. You can pass off a spreadsheet to another staff member to look up the LinkedIn Accounts and then re-import the list to update your existing contacts.

One method to export is from the List View Actions menu as highlighted below. This will export the records to a CSV file that can be edited in another application such as Excel.

Depending on the browser used, there will be a prompt to open or save the exported file. The file will include ALL of the fields in the table. (It is recommended to delete the fields that you don’t need to update. If there’s a time delay between when you export records and import them again some of the values in the live database might have changed. So you don’t want to overwrite good data.)  However, be sure to keep the ID field that will be used to match up the records when they are re-imported.

Note: Only 1,000 records can be selected with a filter and exported. So if you have more than 1,000 records to update, it might be better to use a report and use the Export function as shown below.

Be sure that the report includes the ID field that will be used to match up the records when the records are re-imported.

Whichever method has been used to export the records, they can then be edited in another application such as Excel. With Excel you can use sorting options to see the different values for fields. You can use ‘Find and Replace’ to update values. You can add columns for additional data. When your changes have been made, save the data. If you’re using Excel, save the spreadsheet in CSV format (Comma Separated Values).

In my example, I’ve added a field for customer Category and for LinkedIn Account. I’ve also updated values for Interest. To accommodate these changes, fields must be added to Sugar for the new fields.  Review any fields that will be updated in the import. They must be configured as ‘importable’ as highlighted below. Otherwise, the field will not be available for selection when mapping fields during the import.

Make sure that any new drop-down values that you’re going to import have been added to Sugar.

When you are ready to begin importing, there are two options. You can select to import from the module menu, in this case, Import Contacts from the Contacts menu.

You can also use the Import Wizard in the Admin section of Sugar.

If you use the Import Wizard, the first step will be to choose which module into which you’ll be importing. As you can see, this option is available on most modules. The list will include custom modules if you have any.

Note: The import options might be restricted, based on the permissions set by the administrator. If you don’t see the import options, contact your system administrator for assistance.

If we import straight from the module menu, then the first step is to select the data source. In my example, I’m importing from an Excel spreadsheet on my computer or network. Click Next to follow through the import process.

Select Browse to locate the updated file. Select Create new records and update existing records. Click Next to proceed.

You’ll be warned that updates cannot be undone. Click OK to proceed, or Cancel to stop the import.

In this case, the file was not exported from Salesforce or Outlook, so we’ll select None, and then Next to proceed.

Any field names that match the header row of the spreadsheet will be mapped automatically. The others can be selected and mapped in this step. Click on the down arrow to select the field to which the data should be imported. (If you don’t find the field on the list, verify that the field was configured as ‘importable.’) If there are any fields that you don’t want to import select ‘—Do not map this field –‘ from the drop-down list. When the mapping is completed, click Next to proceed.

In this example, we’re not worried about duplicate checking, but in other circumstances, you could select a field or fields to check to avoid importing duplicate contacts. In this case, we don’t want to select any fields for duplicate checking, because we are updating existing records. Click Next to proceed.

 You’ll see a progress bar during the import.

Then you’ll get a summary report of imported/updated contacts.

After adding the new fields to the Contact List View, I can see that the updated fields have been imported.

Any field that you want to use in filters should also be added to the Search Layout so the field will be available for selection.

If you are a W-Systems customer, you can contact your W-Systems consultant or the support team for assistance or advice with these steps.

It is always recommended to have a backup before doing data manipulation. If you are a Sugar On-Demand customer, Sugar support would be able to restore a backup in the event of a problem. If you have Sugar On-Premise, be sure the administrator has a current backup.