What is the Salesforce Data Loader?
The Salesforce Data Loader is a client application for the bulk import, update or export of data. System Administrator Users can use this application to insert, update, delete, or export records. When importing data, Data Loader reads, extracts, and loads data ONLY from comma-separated values (CSV) files or from a database connection. If you have never used the Salesforce Data Loader, you'll need to download the application onto your computer.
See also: How To - Download and Install Salesforce Data Loader
A: In the first example of Data Loader usage we will be uploading a spreadsheet of Products into improveit 360. This is known as "Inserting" Product records.
B: In the second example of Data Loader uses we will be adding an "End Date" to the Staff members who no longer work for our company. This action is known as "Updating" Staff records.
Best Practice Tip: If you are Updating records, always create a backup before making any changes! We recommend you create a Report and Export the data you plan to augment. Save that file as a backup, in case you need to revert back to the original data values.
Example A: Inserting Product records using Salesforce Data Loader
1. Format your spreadsheet. improveit 360 recommends using at least the following Columns for a Products upload (NOTE: This type of load does not include any related Material mapping):
- Product Name
- Product Category
- Unit of Measure
- Our Cost
- Price
- Start Date
- Vendor (NOTE: Special values are required to Map the Vendor field)
Preparing your Data for Insert
Vendor information must contain the Salesforce Record ID (rather than simply typing in a Vendor's Name into the spreadsheet).
In this Example, we need to capture the Record ID for our Vendor, XYZ Distribution, LLC. I will capture the Record ID by going to the Account page. The ID is the 15 character alpha-numeric string after the "/" in the URL. (see screen shot below of highlighted Record ID)
Copy the Record ID, and Paste it into the Vendor column of your spreadsheet. Your company may purchase Products from a variety of Vendors. Be sure to do this for each Vendor as it applies to the Product.
Example Spreadsheet format
Price and Our Cost columns: Data Type: Number with two decimal places
NOTE: Be sure you have formatted the "Our Cost" and "Price" columns as Number (not currency). These fields cannot contain special characters (like a dollar sign or a comma) and can only contain two decimal places. Note the settings below: Format Cells as Number, with 2 Decimal Places and no comma separator.
Vendor column: Insert the Record ID copied from the Account URL (see screen shot above). The Vendor field cannot be free-form text. You must reference an Account record within your system, using the Record ID.
Finally, be sure your spreadsheet is saved as a .csv file on your computer.
Launch Data Loader, Click Insert and you will be prompted to Login. You must be the System Administrator user for your organization to perform this action.
In the Select Salesforce object menu: choose the Product (i360__Product__c) object. Then use the "Browse" button to locate and select your .csv file.
Click "Create or Edit a Map"
Then choose to "Auto-Match Fields to Columns"
If you've named the Columns in the CSV file exactly the same Names as the Field labels in improveit 360 the Data Loader will auto-match the Fields to the columns from your spreadsheet. However, you'll want to make sure the Name in the top box matches up with the API name of the fields you're mapping to.
If there are columns in your spreadsheet which do not auto-match to Fields, you'll need to Click and Drag the Field to the Field Name section, matching the Field Name to the appropriate File Column Header:
In our example below the Data Loader successfully matched all columns to fields except for "Product Name".
Scroll down in the Field list, click and drag it the "Name" Field and match the Field up to the Product Name column. (see screen shot below)
With the Product Name column mapped to the Name Field, we're ready to proceed. When all the Field mapping looks correct, Click Next
5. The Data Loader produces two log files. One log, shows all successfully inserted records and the second shows any records which failed to Insert. These two log files are known as the "Success and Error Logs." You will need to specify a location on your Hard Drive to save the two log files.
Use the Browse button to set the destination. After you've done so, click Finish.
The Data Loader will ask you if you're sure, as changes will be irreversible.
Best Practice Tip: Test an Insert of only one (1) Product for your first load. Make sure you have the formatting and mapping correct with one test record. If everything looks good for that one record, you can continue with a full load of all records.
Click Finish
Depending upon how many records, this can take awhile. Do not close the Data Loader. When finished, the Data Loader will provide a Success file and an Error file. If records failed, you can re-load from the Error file.
Helpful Hints:
If you notice you are getting lots of Errors, you can lower the Batch Size of the records being updated at one time. If you are trying to remove the data within a Field, "clear" the value in the Field and enter a Blank Value, be sure you have "Insert Null Values" selected in the Settings menu of the Data Loader.
Example B: Updating Staff records using Salesforce Data Loader
You can use the Data Loader to update a single Field or multiple Fields on existing records by using the record's Salesforce ID. In this example, I will use the Data Loader to enter an End Date on a large number of Staff member records. (Also Note: using Inline Editing you can Edit up to 200 records at one time using a List View.) Note: if you need to End Staff member, you can simply use Inline Editing; you do not need the Data Loader. Data Loader is typically used when you need to insert unique values on thousands of records.
1. Creating your spreadsheet. Login to improveit 360 and go to the Reports Tab.
Click the New Report button
Choose a Report Type of Staff:
In the Report Builder Tool, change the following Filters:
Show: change from My Staff to All Staff
Use the Quick Find to locate the End field
Drag and Drop "End" Date into the Filters pane, choose the operator of 'equals' and leave the Value blank, then click OK
Next Drag and Drop the "End" Date Field (or double-click on it) to add the End Date to the Fields visible in the Preview pane
Then use the Quick Find to locate the Staff: ID Field.
Drag and Drop Staff: ID into the Preview pane as well
Your Report should look like this:
Choose Save and give your Report a Name: All Staff without End Date
Click Run Report
Next, click the Export Details button
Then choose the Export File Format. Because the Data Loader requires a .csv file, choose "Comma Delimited .csv" as your Export File Format.
Preparing your Data for Update
The Field we will be updating on the Staff record is the End date. Be sure to format the Cells in the spreadsheet as Date Fields.
Highlight the End Column and right-click, choose Format Cells
When Formatting Cells be sure to select Category: Date
Choose a Type with two (2) digits for the month, two (2) digits for the day, and four (4) digits for the year.
Best Practice Tip: Check your Organization's Locale Setting. If you are located in Canada, be sure to match the formatting of your spreadsheet with your system's Locale Settings.
The Organization-Wide Locale Setting can be found by navigating to:
Setup > Company Information > Edit
Note the Default Locale of the Organization
If your system is set to Locale of Canada, in your spreadsheet you'd want to select "English (Canada)" when formatting the Date Cells.
Next...
Launch the Salesforce Data Loader, see also: How to Download and Install Salesforce Data Loader
Click Update and you will be prompted to Login.
You must be the System Administrator user for your organization to perform this action. Choose Password Authentication and enter your Admin login credentials
In the Select Salesforce object menu: choose the Staff (i360__Staff__c) object.
Then use the "Browse" button to locate and select your .csv file.
Next click "Create or Edit a Map" and choose to "Auto-Match Fields to Columns" If there are columns in your spreadsheet which do not auto-match to Fields, you'll need to Click and Drag the Field to the Field Name section, matching the Field Name to the appropriate File Column Header:
Once all Fields are mapped to a File Column, click OK, then Click Next
You will need to
When you click Finish the Data Loader will display one final message that this action cannot be undone. If you are SURE you're ready to proceed, click Yes.
The Data Loader will display a progress bar as the records are updated:
Helpful Hints:
If you notice you are getting lots of Errors, you can lower the Batch Size of the records being updated at one time. If you are trying to remove the data within a Field, "clear" the value in the Field and enter a Blank Value, be sure you have "Insert Null Values" selected in the Settings menu of the Data Loader.