Updating Records Using an Import
Here's the scenario: You have a database full of information and need to update specific account records using information from a spreadsheet. A "Mass Update" won't fill the need because the information you want to update for the account records is not the "same" for all of the records. As an example, for a certain set of accounts, the Billing City, Billing State, Billing Postal Code, and Billing Country are missing from some account records. Do you see how a "mass update" in Sugar would fall short of getting this taken care of easily (updates differ from record to record)? This is where the power of using "Import" to update information is a great solution.
Following is a step-by-step walk through of accomplishing these account record updates using an imported CSV file. We will use the example mentioned above in the steps that follow. [Although this example is using the "Accounts" module, this method or updating records can be used for other modules as too).
To start, it's important to note that Sugar needs to know exactly which account records need to be updated. For this, we will need to "export" the records that will be updated, and this can be done two ways:
- Using a report
- Using the module's List View process.
Our example will use the List View export method.
Exporting the Records to be Updated
In my database, I've tagged six (6) records as "Favorites, " and these need to have their billing address information added; they're now blank for:
- Billing City
- Billing State
- Billing Postal Code
- Billing Country
Each of the six will get different information for all fields except Billing Country. For Billing Country, all accounts in the import will be set to "USA."
Here in the Accounts module's List View, we see the accounts that will be updated with the imported information. These are filtered "Favorites" and missing billing address information:
After the records that need to be updated are in this list, all of them need to be selected:
The next step is to export the records. From the Actions menu, click Export:
You will be prompted to save a file (Accounts.csv) in our example:
Preparing the CSV File with Update Information Before Importing
The reason for exporting the records to be updated is so that we get the record "IDs" (this is the "ID" column you will see in the CSV file). Sugar uses these IDs (which are unique for each record) to know which existing record is to be updated during the import processing. Do not delete this column from your CSV file!
The CSV file you downloaded from Sugar will most likely contain more columns than you'll need for the import process. The file should be reduced to contain "only" the fields that are "required" by the module, the fields you want to modify, and the "ID" field mentioned above.
Open your CSV file in Microsoft Excel (or equivalent) and delete the columns that are not needed based on the previous instructions. Here's what we end up with for our example (new information to be updated not yet added):
Now we see the CSV file's contents with the appropriate information inserted for each of the accounts being updated:
After updating the CSV file, save the changes made. Be sure to save the file as a CSV (comma separated value) formatted file (*.csv).
Importing the Updates Using the CSV File
With the CSV file prepared, the import can take place that will modify your existing records with the information in the CSV file.
For our example, we'll use the Accounts tab drop-down and click Import Accounts:
In Step 1, click the Choose File button and navigate to your CSV file to open it.
After choosing the file, click the radio button for Create new records and update existing records and then click Next:
In Step 2, review that the information is correct as it's being read from the CSV file.
At the bottom of Step 2, select None and then click Next.
In Step 3, the field mappings will be displayed. As you see in the next screenshot, the mappings match because the data was exported directly out of Sugar and the headers map automatically to the correct field in Sugar. NOTE: All required fields, indicated by an asterisk, must be mapped for the import to work successfully (i.e., Name in this example).
Click Next to continue.
In Step 4, do not populate the Fields to Check column. Adding fields to be checked for duplicates would prevent records from being updated because the records already exist in Sugar.
Click the Import Now button to start the import process.
In Step 5, the View Import Results displays a message describing the successes/failures (if any) of the import. In our example, we see that "6 records updated successfully". Because we used the existing ID field, our processing "updated" existing records instead of creating new records. Here is a screenshot of the results:
Just click Exit, and you are all done!
Please note: If you get errors, there will be the ability to get an error report that can be reviewed and the changes can be un-done so you can start over.
This feature of "updating" records using the Import utility in Sugar is not new but often overlooked or misunderstood and could be just the ticket for getting your information updated easily.