MassEffect - ETL for Salesforce

Home > DemandTools for AppExchange Modules > DemandTools Maintenance Modules > 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:

Update information from a CSV, XLS, 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, MDB or UDL 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)

Step 1. Update Newly Added Fields

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

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

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!

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.

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:

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 alllow 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 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

Log files are stored in the C:\Program Files\DemandTools\LogFiles directory.
A separate log is created for each day.
Insert operations return an ID list stored in the same folder as the input file.
Restore files are stored in the C:\Program Files\DemandTools\Restore directory.
Mapping can be saved for later use by clicking on the "Save Mapping" button.
To use a saved mapping click the "Load Mapping" button.

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.