Home > DemandTools for AppExchange Modules > DemandTools Maintenance Modules > MassEffect - ETL for Salesforce
MassEffect - ETL for Salesforce
Capabilities & Features:
Mass Insert/Import
Mass Update
Mass Delete
Mass Export
(applies to standard and custom objects)
Compatible Data Sources:
.xls
.xlsx (Excel 2007)
.csv
.mdb (Access)
.udl
Note: DemandTools 2.2 supports Office 2007 files. Previous versions will not recognize files saved in the 2007 format. If running an older version all Office 2007 files will need to be saved as 2003 or 2005 files.
Common Uses:
- Update information from a csv, xls, xlsx, mdb or udl file into Salesforce
- Update any Salesforce object including custom objects
- Migrate data from legacy systems
- Insert products and price books
- Insert attachments from files that reside on your local PC (includes image files, xls, word documents etc.)
- Insert new Tasks, Opportunities, and Events to any object in Salesforce
- Mass deletion of a record set
- Populate newly created custom fields
- Updating an object using something other than the Salesforce ID as a 'key'
- Loading new objects
- Export the full or partial contents of any table, or a subset, to a csv, xls, xlsx, mdb, dbf, or xml file
- Schedule regular data loads, such as nightly imports
- Repair incorrectly imported data
- Save field mapping for future use (a great time saver, especially when a standard template is used for all imports and updates)
Quick Links:
Tip*: Update Newly Added Fields Button
Click the "Update Newly Added Fields" button before using MassEffect for the first time or after the addition of custom fields to Salesforce. This will ensure that all editable fields are made available in MassEffect.
Step 1. Choose a File
1. Select the file by clicking the "Select File" button
2. Browse to find the file and click "Open"
3. If an .xls or .mdb file has been chosen the user must select the worksheet/table name to process by clicking on it (will highlight in blue). Click the "Use" button on the right or double click to select (the fields from the spreadsheet/table will populate in the left column of the page).
Step 2. Select an Operation
- Update Existing Records: Updates Salesforce object records based on a matching key
Note: If a restore file is required and/or special update options needed, the DemandTools Discovery module can be used to match the input file to the appropriate table in Salesforce based on the desired key which will return a file that contains all the input data AND the Salesforce ID. This file can then be used as input to MassEffect, and now matching can take place on the Salesforce ID. More information on Discovery can be found here.
- Insert: Inserts new records for one object per pass (i.e. cannot create new Account and Contacts in one pass; this would be two passes with MassEffect)
- Delete: Deletes/Removes Salesforce records via the Salesforce ID. Deleted records are placed in the recycle bin.
- Process Blank: When a checkmark is placed in this checkbox it allows for a populated field in Salesforce to be overwritten with a 'null' or blank value from the incoming spreadsheet. If left unchecked, empty cells in your spreadsheet will be ignored and the values for those fields WILL NOT be updated.
- Restore File: When the "Restore File" checkbox is selected an "undo" file will be created and saved in the Restore Folder created upon the installation of DemandTools. The Restore File, by default, is an xls file and can be used in MassEffect to revert changes made during an update.
-
The Restore File option IS ONLY AVAILABLE when using the Salesforce ID as the unique key.
-
To change the default restore file type to csv or mdb, access the Edit/Options menu. When processing more than 64,000 records be sure to change the restore file type to something other than .xls (.csv or .mdb or for those running DemandTools version 2.2 .xlsx is also an option).
-
Export: Exports information from any standard or custom object in Salesforce
-
Export is the only operation in MassEffect that is accessed outside of the Operation section. To Export information via right click on the table and choose Export Table
-
For more details on Exporting records click here
Step 3. Field Mappings
1. Choose the object in the middle of the screen to modify by clicking on the + sign next to the object. This will reveal the fields that are able to be modified.
2. Map the fields from the data source by left clicking, dragging a dropping to the matching Salesforce object field.
3. To remove an incorrectly mapped field right click on the mapping and choose "Remove".
Important notes on mapping and data contained in the input file:
- The tool will show all "Updateable" fields but some may not be updateable in conjunction with other fields. This information will be shown in the log files.
- NOTE: Fields with a Data Type of "Auto Number" are NOT updateable (includes Salesforce auto number fields, i.e. Case Number and custom Auto Number fields). and CANNOT BE USED AS A UNIQUE KEY FOR AN UPDATE (cannot use the "Set as Key" functionality on these fields).
- Boolean fields 0 = False, 1 = True; to mark a boolean field as true or false the input file field must have either a 0/1 or True/False as the input value to make the appropriate selection
- Multiselect fields, multiple values must be separated by a ";" character. By default, new multi-select values will OVERWRITE existing values. To merge the new value with the existing value, right click on the blue mapped field and select "Merge input data with existing values in Salesforce".
- For all lookup/ID fields in Salesforce, the Salesforce ID, not the name, will be needed. Some examples include:
- Owner ID
- Owner Alias can also be mapped to the Owner Alias field, but only one can be mapped (either the Owner ID or Owner Alias but not both). Owner Name is never accepted.
- RecordTypeID
- CampaignID
Reasons to Right Click:
- AutoMap:
- To automap the input data right click on the table name and choose "AutoMap".
- The column name in the data source must match the Salesforce display name or the internal reference name for a field to auto map (to see the internal reference name, check the "Show MassEffect Field Name" checkbox in the Preference/Options menu).
- If a field cannot be auto mapped, it will remain black on the left hand side of the screen. Once it is mapped manually, it will turn blue.
- Add Constant:
-
To have the exact same value appear on all the records being updated or inserted, right click on a field and select “Add Constant”. This replaces the need to create a column in the data source for a field(s) of data that is constant for all records.
-
Only acceptable values will be allowed by field type:
- A numeric field with a limitation on the number of places to the left or right of the decimal point can not be exceeded
-
- Picklist field: the constant must be a value that is valid in Salesforce
- Text cannot populate a date field or a numeric field
See below for details on each option in the image above (these are all found by right clicking a mapped field)
- Remove:
- removes the field mapping
- Update from input data only if the field is empty in Salesforce:
- Will only update a fields value in Salesforce if the field is Salesforce is blank. If the field in Salesforce has data in the field
- Merge input data with existing values in Salesforce:
- Applicable only to these field types: Multi-select pick lists, text area and Boolean (T/F)
- If the value being merged from the input data exists in the picklist it will become an additional value rather than a changed one. Example: a multi-select picklist includes the value of "apples" and the input data has a value of "oranges", the final value will be "apples; oranges".
- Boolean True/False fields will always retain the True value, "T always wins". If the input data contains a value of False, but the Salesforce field contains True, the resulting value will be True.
Step 4. Saving the Scenario
- Save Scenario:
- Saves all field mappings including, added constants
- Saved scenarios are scheduleable. To learn more about scheduling, click here.
- Clear Scenario:
- Load Scenario:
- Allows for the selection of any saved MassEffect scenario and loads the mappings
*Note: A set of Scenario control buttons are also available in the Export function pop up. Those buttons work the same as the ones described above.
Step 5. Select the rows to use in the operation & process the data!
- MassEffect allows for the specification of rows to process from the data source
- Ex: to process the first 300 rows from a data source of 13,000 rows go to the control box and change the "End" number to 299 (applicable to .xls and .csv files). The end number is 299 because row 1 is actually the header row, so we need to add one to the end row when changing the endpoint.
- The default values are 1 to the end of the data.
- Tip*: Reports exported from Salesforce have approximately five (5) rows at the end that are seen as relevant data. Processing these rows will generate errors in the error log.
- To perform the update, insert or delete, click the "Process" button.
- If the operation results in errors the results pop-up box will display the total count of errors with a direct link to the log file.
- To quickly locate the error in the log file search for "status code". To learn more about exception codes found in log files, click here.
Restore Files
- When performing an Insert, a file will be created containing the Salesforce ID's for the newly created records as well as the original data in the data source.
- The file name will be the same as the input file with the added extension of "_IdSave0" (the zero will increment if the same input file is processed multiple times).
- A link to the ID file will be displayed along with a link to the log file when processing completes.
- To “undo” the insert, use the new .xls file as the input file and run a mass delete of the records (mapping on the Salesforce ID).
- When performing an Update, a restore file is created of the data (being modified) as it was in Salesforce prior to the processing of the updates.
- The restore file (by default) is an .xls file and is saved to the location specified in the File Paths tab in Options.
- The default file type can be changed in the Preference/Options menu.
- Be sure to use .csv .mdb or .xlsx when processing more than 64,000 rows of data.
- The restore file name will be similar to: MErestore_Account_Jan132009_00001
- When performing updates with the restore file option turned on, the path to the restore file will be displayed upon completion of the update.
- To "undo" the updates, process the restore file in update mode
Log Files:
- A new log file is created each day
- The most recent job processed is at the bottom of the log file
- NOTE: If the job returns anything OTHER THAN 0 ERRORS, be sure to check the log file for the nature of the errors. Correct the input data and re-process any failed records if necessary.
Exporting Records
To export the contents of a table, right click on the table name and the export data dialog box will appear.
Exporting data is commonly used for the following purposes:
-
To find the Salesforce ID fields of the users and roles to use for inserts/update
-
To find the Salesforce ID's of objects that will become parent ID’s for other objects being inserted/updated.
-
To export data for reporting
-
To find the Salesforce ID's of objects to mass delete
-
To export data, make modifications in the spreadsheet and then upload the changes.
1. Choose Export Table
2. Choose to export "ALL" records in an object or "Use Conditions" to specific records based on conditions.
3. Build the conditions by selecting the field name, an operator and the value.
- Picklist and Owner field values are shown as checkboxes when used as a condition.
To apply conditions to lengthy pick lists, right click the list of values to bring up a Filter menu:
- Add Value To List - Allows for the addition of a value for export purposes only. This will not add a value as an active option to the picklist in Salesforce.
Filter List - Allows for easy searching of a value by entering in the letter(s) in which a value begins or contains.
Reset - Will strip all filters and added values and reset to the values in your Salesforce instance.
- To "OR" multiple values from the same field (i.e. BillingState = OR or WA) separate the values with a comma.
- Continue to select “Add Condition” to further refine the selection.
- To "Or"/"And" different sets of conditions check the "Expert Mode" checkbox. This will allow for sets of conditions to be compared by an "or" or "and" .
4. Choose the fields to include in the export by manually checking the boxes of the fields to include or by clicking the "Check All" button.
5. Once all options are set, click the "Export Data" button.
6. Enter the output file name and location in which to save the file. The file can be .xls, .csv, .mdb, .xml or .udl.
The data from Salesforce will be retrieved at up to 100 records per second but will depend on current internet traffic.
See also