(614) 259-7038 info@thecrmsense.com
Select Page

Wizard vs DataLoader

Based on VIDEO: Data Import: Choosing the Right Tool:

  Data Import Wizard Apex Data Loader
Import data format CSV CSV
Max. # records < 50,000 < 5 million
Update existing records yes no
Delete existing records no yes
Catch duplicates yes no
Import Opportunities no yes
Can turn off Workflow rules yes no
Can export data no yes
Can save mappings no yes

Both can handle custom as well as standard objects.
Both trigger validation rules during importing.

In-built Data Import Wizard

The Data Import Wizard is inbuilt within Salesforce to provide step-by-step guidance to manually specify how to get CSV files into Salesforce standard objects such as Leads, Accounts, Contacts, Solutions and custom objects.

Based on this article:

  1. Create a dedicated Data Loader Salesforce user account.
  2. Login by appending the Security Token to extend the account’s Password.
  3. Set that account’s User Profile to not fire triggers, workflows, or other automation.
  4. Setup that user whenever you setup automation.
  5. Create a checkbox fields on the user record labeled “Authorized to Send Email Alerts”. Include this field in your workflow rules, triggers or other automation, similar to option 1.
  6. Toggle the flag on/off depending on the type of data load.
  7. Download the DataLoader.
  8. Have a program calculate the number of records in the file. Salesforce limits imports of up to 50,000 recordsat a time. Use this Linux command:

wc -l importfile.csv

  1. Before each upload, run a report to count the number of records, so you have a “before” picture.
  2. Do a test run with just a couple records, so it’s easier to recover from mistakes.
  3. After an upload, run a report to calculate records and identify what has changed.

In-built Apex Data Loader

The standard Salesforce Data Loader is for migrating CSV datasets into Salesforce, up to 5 million records at a time.

But recurring data loads can be scheduled. It is a program that communicates with the Salesforce cloud, installed locally on a laptop or on-premise server. Some consider it annoying to install on a Mac.

Permissions?

It can match external identifier keys. But its Auto Mappings is tricky to define, which can lead to wrong mappings or missing fields.

3rd-Party

Several partners of Salesforce offer tools.

Own backup

ownbackup.com is the premier solution.

DataLoader.io

Dataloader.io from AppExchange is a popular tools because it works on the browser (“no software”), no installation It and Salesforce security token required. It’s intuitive and wizard driven, but takes a little longer to setup and runs slower than the Salesforce Apex Data Loader.

However, unlike the Apex Data Loader, it handles CSV files in DropBox, Box, SFTP, FTP. This app also provides users with list of fields from related objects, making exports easier and cuts down post-export work required to bring multi-object data into a single row.

Scheduling jobs?

Jitterbit ODBC/JDBC Data Loader

Jitterbit from AppExchange is a free program installed on Windows and macOS. It is offered by Jitterbit Inc. as an entry offering to their full integration suite.

It is intended for tech savvy users who operate local databases (such as MySQL) and want to connect them to Salesforce. Its specialty is loading not just csv flat files but running ETL scripts that use ODBC/JDBC.

Thus, its meant to process repetitive runs and not one-offs.

Informatica Cloud Data Loader

Informatica’s Cloud Data Loader from AppExchange provides powerful data transformation logic during loading “on the fly”, which enables more powerful formatting of data fields.

It comes with prebuilt integration templates for systems such as NetSuite or SAP;

It is part of a very sophisticated integration suite called “built-in intelligence”. So it’s for tech-saavy types.

It connects to Box.

Pity it only handles CSV files.

Vlocity

Vlocity’sDataRaptor is an extract, transform and load (ETL) tool that integrates with their Omniscripts to read and write Salesforce data. The DataRaptor Designer enables app developers to map data to the input format required by OmniScripts, to transform the data as required by business logic, and to write the output data back to Salesforce in compliance with the Salesforce object model.

Talend

Talend Components for Salesforce Data Integration integrates with Talend’s Open Studio and Integration Suite, which Forrester put at the top of all other vendors in both current offering and Strategy dimensions for “Data Integration Tools”.

The tool is open-source (free). It works on Hadoop and Spark big data. It schedules data loads from other systems, using the Salesforce API, to perform system integrations.

In addition to Input and BulkExec connectors, Talend has more sophisticated connectors such as “GetUpdated” which changes data.

LexiLoader

http://macappstore.org/lexiloader says does not exist.

BOFC (Bulk Object Field Creator)

Import & Export Salesforce fields using CSV or XLSX files to perform bulk CRUD operations with Point & Click. from tech9logy.com

Grabs your Salesforce Process builder flow into excel.

See http://salesforcebofc.com/

Apsona

Apsona is a set of SaaS-based tools for Salesforce.

https://apsona.com/pages/sfdc/nonprofits.html

DemandTools

SOQL Studio from Visual Software systems

SOQL Studio from Visual Software systems is a $50/year IDE to query, visualize and extract Salesforce data at a depth and breadth of features not available with the force.com IDE, Developer Console, Workbench, or Data Loader. The tool returns every data point – aggregates, compound fields, related records, multi-level child-to-parent fields. It can work with multiple queries at the same time. It allows for annotation of queries with single or multi-line comments. Queries can be saved for reuse later. Copy/Paste selected bits of you results or export the full results of your SOQL query in Excel, CSV, XML or custom text format, even if your query includes data from child objects.

Reflection Enterprise

reflectionenterprise.com/salesforce-data-export

JetBrains Illuminated Cloud

The Illuminated Cloud @IllumCloud) add-in to JetBrains’ IntelliJ IDE.

It supports multiple Dev Hubs when creating scratch orgs, which is nice when working on packages in different dev hubs.

CloudToolKit

Ben Edwards (@benedwards44 in New Zealand) https://cloudtoolkit.co (salesforcetoolkit.com and sftoolkit.co) open sourced in GitHub a suite of tools running on Heroku:

  • Schema Listerusing the Salesforce Metadata API to build a list of objects, fields, and field attributes from within a Salesforce Org.
  • sforgcomparewhich uses the Salesforce Tooling or Metadata API (user selection) to compare metadata between two Orgs – useful when planning deployments or building deployment packages, as well as seeing what configuration exists in Production to Sandbox or between Sandbox environments.
  • sfcodecleanscans all the (non-packaged) Apex Classes in your Org to build a table of where each property, variable and method is used. Useful for cleaning up unused code. Only Apex Class metadata is captured from your Org.
  • Switchwhich provides a web interface to easily enable and disable components in your Salesforce Org – Workflows, Triggers and Validation Rules. Very useful when doing data migrations and needing to disable certain automation in Salesforce.
  • packagebuilderwhich uses the Salesforce Metadata API to build a package.xml file based on components in a Salesforce org specified online. Handy for building a package.xml file for use with ANT com Migration or other IDE tools, or building a destructiveChanges.xml file.

Others:

  • Web Form
  • Email to Case
  • Email to database

Java Apex Code

A java program is more work, but much more flexible, operating at the “atomic” level, particularly with junction objects.

Techniques from the Big Objects Implementation Guide include Async calls of Salesforce Object Query Language in the background.

In the Apex Developer Guide: Inserting and Updating Records:

Using DML, you can insert new records and commit them to the database. Similarly, you can update field values of existing records.

First make a query to obtain related keys used in the query.

try {    Account acct = new Account(Name=’SFDC Account’);    insert acct;     // Once the account is inserted, the sObject will be     // populated with an ID.    // Get this ID.    ID acctID = acct.ID;     // Add a contact to this account.    Contact con = new Contact(        FirstName=’Joe’,LastName=’Smith’,        Phone=’415.555.1212′,AccountId=acctID);    insert con;} catch(DmlException e) {System.debug(‘An unexpected error has occurred: ‘ + e.getMessage());}

The above example inserts three account records and updates an existing account record. First, three Account sObjects are created and added to a list. An insert statement bulk inserts the list of accounts as an argument. Then, the second account record is updated, the billing city is updated, and the update statement is called to persist the change in the database.

References:

REST API calls externally

The advantage of a REST API interface is scale.

  • Several instances can update Salesforce at the same time.
  • The interface can update various orgs.
  • The interface can interact with resources outside Salesforce

Internally, Salesforce tables contain artificial keys call “identifiers” which uniquely identify each record. That identifier is created when new data is added. In parent-child relationships, child tables contain the identifier to its parent. See https://developer.salesforce.com/docs/atlas.en-us.218.0.api_asynch.meta/api_bulk_v2/datafiles_xml_rel_fields.htm?search_text=relationships

Rows created with data external to Salesforce contain an external_id.

That external_id is in CSV files created by the export process.

A program that inserts data based on CSV files cannot use the parent identifer exported because as each row is inserted, a new identifier is created.

Thus, CSV files need to be imported in a specific sequence – parent first, then its children. See Creating Parent and Child Records in a Single Statement Using Foreign Keys and Relationships It has a section on Async API Code walkthough

Bulk API v2

In Web Services Connector, the “Preparing to Integrate Java Apps with Force.com APIs” and “Creating an Enterprise WSDL Application” sections where it walks through setup, prep, and actually gives you some sample code that shows how to query, update, create, and delete records.

developer-centers/integration-apis (previously https://developer.force.com/REST)

https://developer.salesforce.com/docs/atlas.en-us.218.0.api.meta/api/sforce_api_calls_create.htm#MixedSaveSection

SaveResult[] = connection.create(sObject[] sObjects);

Use create() to add one or more records, such as an Account or Contact record, to an organization’s information. The create() call is analogous to the INSERT statement in SQL.

When creating objects, consider these rules and guidelines.

GitHub / Tools

https://github.com/danieljpeter/HyperBatch by MVP DanielJPeter

https://github.com/fredrikhogstrom/salesforce-test-data-creation by fredrikhogstrom provides NodeJs code to create test data for Salesforce unit tests, great for when in an unfamiliar environment. It uses multiple API’s to get the data.

Learning Modules

This 5 part video series walks you through all aspects of data import, from preparing your import files to matching owner and parent record IDs, on both Classic and Lightning.

This 3-part video series TC: SALESFORCE DATA BACKUP AND DISASTER RECOVERY OPTIONS [SP]

Project: Import and Export with Data Management Tools +300 to use Data Loader and the Data Import Wizard to manage data in Salesforce.

References

VIDEO: How to: Use Two Great (and Free) Data Tools – Data Loader and Excel Connector

SFDX Sandbox Data Loads from SnowForce SLC Apr 24 2019 [48m 14s] by

Randy Barton (“SFDX Baby Steps – Using sandboxes instead of scratch orgs”)

For those who have bought Mike Wheeler’s Udemy class:

More about Salesforce

This is one of a series about Salesforce

  1. Salesforce index
  2. Salesforce Ohana (about the Salesforce company, offices, mascots, emojis, and store)
  3. Salesforce Glossary (of acronyms)
  4. Salesforce Events (Conferences, local Meetups, ) to meet people face-to-face
  5. Salesforce Exhibitors (at Dreamforce)
  6. Salesforce Onboarding (Trailhead and IDEs)
  7. Salesforce Rock Stars (and influencers)
  8. Salesforce Offerings (Clouds, Industries, Domains, GitHub, editions, pricing, features, versions)
  9. Salesforce Certifications (training and exams)
  10. Salesforce Projects, Superbadges, and Sample Apps
  11. Salesforce myTrailhead for custom Trailhead content
  12. Salesforce Project Plans
  13. Salesforce Jobs (within Salesforce, with partners, etc.)
  14. Salesforce User Roles and Personas
  15. Salesforce Apps (in AppExchange)
  16. Salesforce Alexa
  17. Salesforce Heroku (external apps)
  18. Salesforce DX (Developer eXperience)
  19. Salesforce Non-Profit support
  20. Salesforce NPSP (Non-Profit Success Pack) performance (with Gatling)
  21. Salesforce Data Management
  22. Salesforce Einstein
  23. Salesforce Selenium (test automation)
Social Share Buttons and Icons powered by Ultimatelysocial