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
 
(apply to standard and custom objects)

Compatible Data Sources:
.XLS
.CSV
.MDB (Access)
.UDL

NOTICE- OFFICE 2007 USERS, DEMANDTOOLS WILL NOT RECOGNIZE FILES SAVED IN THE 2007 FORMAT. PLEASE SAVE ALL DOCUMENTS AS 2003 OR 2005.

NOTE: A field name ending in "ID" indicates a reference to another object in Salesforce and YOU MUST have the Salesforce ID for that field (not the name). For example, OwnerID, ParentAccountID, CampaignID, etc. 

Common Uses:

Step 1.  Update Newly Added Fields

1. Click the "Update Newly Added Fields" button b
efore 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 2.  Choose a file and an operation

1. Select the file type (.xls, .csv. mdb or .udl) by clicking on the radio button
1a. Click the "Select File" button to search for the file (click open when the file is located)
2. Select the worksheet to process by clicking on the name of the spreadsheet (it will highlight in blue), Click the "Use" button (the fields from the spreadsheet will populate in the left column of the page)


Operations

Insert
Creates a new single object
Update
Updates objects based on a matching key:
  • The key is usually the Salesforce unique ID but can be any field
  • Keys are case sensitive
  • Boolean fields 0 = False, 1 = True
  • Multiselect fields must be seaprated by a ";" character
  • 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).
Delete
Removes objects by their Salesforce ID. Deleted objects are placed in the Salesforce recycle bin.

Process Blank: Checking this box will allow for a populated field to be overwritten with a 'null' value. If left unchecked, empty cells in your spreadsheet will be ignored and the values for those fields WILL NOT be updated.

Restore File: When performing an update operation on Salesforce data and would like to create an undo file simply check the "Restore File" checkbox. This will cause the update to take longer but it will save a .XLS file of the current data state for the fields that are being updated. To "Undo" an update load the saved .XLS file, use the same mapping and reload your original data.

NOTE: The Restore File option IS ONLY AVAILABLE when using the Salesforce ID as the unique key. By default, the restore file is created as a .XLS; this can be changed byu accessing the Preference/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).


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".

AutoMap To automap the input data right click on the table name and choose automap. The column name in the data source must match either the Salesforce display or 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.
Selecting a Key ID

When processing an update operation a "Key" field is required. The default key is the Salesforce ID field. To choose a different field as the Key, right click on the field and choose "Set as Key".The lightbulb synmbol will change to a key symbol indicating that this field is to be used as the match key.

Note: If the unique key is changed, the following options are not available:

  • Restore File
  • Special upate option: Update Only if Empty
  • Merge options
Setting a Constant

To have the exact same value to appear on all the records being updated and/or inserted, right click on a field and select “Add Constant”. There is no longer 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:

  • For a picklist field, the constant must be a value that is valid in Salesforce
  • Text cannot populate a date field or a numeric field
  • A numeric field with a limitation on the number of places to the left or right of the decimal point can not be exceeded

Pick List Constant Example

Date Constant Example -----------Text Constant Example

Special Update Options

To assign individual field update options ("UPDATE" and "MERGE" options only available when using the Salesforce ID as key), right click on a mapped field (the one in blue).

  • Remove - will remove the field mapping
  • UPDATE field only if it is blank in Salesforce. Data will not be processed if anything exists in the Salesforce live instance field.
  • MERGE input data (Multi-select lists, text area, Boolean (T/F)). If the value being merged 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 alwasy wins",if the input data contains False, but the Salesforce field contains True, the resulting value will be True.

Scenarios


Utilize Save Scenario, Load' Scenario, or Clear Scenario to process multiple update files using the same mappings. For updates and imports that use a standard template the field mapping can be saved for use in the future. To recall a saved scenario/mapping click on the "Load Scenario" button.

*Note: Scenarios are schedulable.


Step 5.  Select the rows to use in the operation
 & process the data!

1. MassEffect allows for the specification of rows to process from the data source; for example to process the first 300 rows from a datasource of 13,000 rows  go to the control box and change the "End" number to 300 (applicable to .XLS and .CSV files). The default values are 1 to the end of the data.

**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.

2. 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". 

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 restore file (by default) is a .XLS file and is saved to the same location as the input file. The default file type can be changed in the Preference/Options menu (same setting for the Restore file). Be sure to use .CSV or .MDB when processing more than 64,000 rows of data. The restore file name will be the same as the input file with the added extension of "IdSave0.xls" (the zero will increment if the same input file is processed multiple times). A link to the restore file will be displayed along with the log file. To  “undo” the insert, use the new .XLS file as the input file and run a mass delete of the records (mapping on Salesforce ID's).
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.

  • Log files are stored in the C:\Program Files\DemandTools\LogFiles directory.
  • A separate log file is created for each day.
  • Insert operations return an ID list stored in the same folder as your input file.
  • Restore files are stored in the C:\Program Files\DemandTools\Restore directory.
  • 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:

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. 

To apply conditions to lengthy pick lists, right click the list of values to bring up a Filter menu:



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 or .UDL.

The data from Salesforce will be retrieved at up to 100 records per second but will depend on current internet traffic.



Export Log Files & Mapping

See also

MassChange - Single Table Data Manipulation
Reassign Ownership - Mass Transfer of Objects/Sub Objects
MassImpact - Multi-table and Formula based data Manipulation
MassChange Batch
ZipFix - Reverse Engineer Existing Zip Codes
MassBackup - Backup to local .MDB database
Restore Files