SugarCRM SupportKnowledge BaseImport and ExportUpdating Records Via Import

Updating Records Via Import

Overview

While Sugar provides the Mass Update utility to quickly update a set of records, you may find that updating records through a spreadsheet is much more efficient. This article will cover how to use Sugar's import tool to update existing records.

Use Case

We would like to update the Billing City and Billing Country fields on a number of account records via Sugar's Import tool. 

Steps to Complete

Exporting the ID field

First, you must extract the ID field and the fields we wish to edit from Sugar. There are two ways to go about this: exporting from a report and exporting directly from the module's listview.

Exporting Via Reports

  1. Create a Rows and Columns report based on the module that has the records you wish to edit.
  2. If you wish to modify only some of your records, add the appropriate filters.
  3. Add the ID, Name, and any fields you wish to modify as Display Columns.
  4. Save and Run the report.
  5. Open the Actions menu and select "Export" to create a .csv (spreadsheet) file of your data.
    rest/v10/supp_Files/a4b4dff4-9bcb-cf29-aa97-550281edf7e6/file/uploadfile?format=sugar-html-json&platform=base 

For more information on creating reports, please refer to the Reports documentation. 

Exporting via List View

  1. Click on the Accounts module tab to open the Accounts list view.
  2. If you wish to modify only some of your records, create filters as necessary to find the desired records. createfilter
  3. Check the boxes for the desired records or select "Select All" from the Actions menu to select all results. 
    selectrecords
  4. Click the triangle to open the Actions menu and select "Export" to create a .csv (spreadsheet) file of your data.  
    exportbutton7

Preparing the CSV File

Whether you choose to export from a report or the module's list view, your .csv file may include some extra columns. It is best to reduce the file to only display any fields marked as required when importing and any others you wish to modify. In order to update records that are already in Sugar, you must also include the ID field on the spreadsheet since Sugar depends on unique IDs to know which existing record you wish to edit.

For this example, we only wish to update the Billing City and Billing Country fields. Begin by opening the .csv file in a spreadsheet application such as Microsoft Excel. To delete unnecessary fields, highlight or click the lettered column headers, right click the header, and choose "Delete". By the end, you should only have the ID, Name, Billing City, and Billing Country fields.

Note: If updating records in a person-type module (i.e. Contacts, Leads, Targets), your export may include First Name, Last Name, Full Name, and Name fields. We recommend removing the fields "Full Name
and "Name", making any changes only to the First Name and Last Name fields.
rest/v10/supp_Files/5acf645c-0390-3dde-5b1d-550281014e9c/file/uploadfile?format=sugar-html-json&platform=base

Now it is time to edit the data. For example, we will change all of the Billing City to say "Syndey" and all of the Billing Country to say "Australia". Save the spreadsheet as a .csv file when finished.
rest/v10/supp_Files/33b1077e-4cd5-1193-c915-55028138f1a4/file/uploadfile?format=sugar-html-json&platform=base 

Importing Your Updates

  1. Click on the Accounts module tab and select "Import Accounts".
    rest/v10/supp_Files/a60b9af5-0a5f-cb31-fb01-550281576beb/file/uploadfile?format=sugar-html-json&platform=base
  2. On Step 1 of the Import Wizard, click "Choose file..." and select your .csv file.
  3. Then, click the radio button for "Create new records and update existing records" and click "Next".
    rest/v10/supp_Files/eb42c86d-d632-df4a-58af-550281375b66/file/uploadfile?format=sugar-html-json&platform=base
  4. In Step 2, confirm that the file is being read correctly and click "Next".
  5. In Step 3, map any necessary fields. Because the data was exported directly out of Sugar, the headers will map automatically to the correct field in Sugar. Then, click "Next".
    Note: All required fields, which will be indicated by the asterisk, must be mapped for the import to work successfully.
  6. In Step 4, leave the "Fields to Check" column blank. Adding fields to the duplicate check will prevent our updates from being imported since all the records already exist in Sugar. Then, click "Import Now".
    Import FieldstoCheckBlank
  7. When the import is finished, a message will display indicating that it updated your records along with any potential errors. For this example, the message "6 records updated successfully" confirms that we updated rather than created new records because we used the existing ID field.
    rest/v10/supp_Files/193d9e58-7335-a7c7-ee01-5502825823c3/file/uploadfile?format=sugar-html-json&platform=base 

Last modified: 2018-05-16 21:32:21