Wilson Mar bio photo

Wilson Mar

Hello. Hire me!

Email me Calendar Skype call 310 320-7878

LinkedIn Twitter Gitter Google+ Instagram Youtube

Github Stackoverflow Pinterest

How to get data in and out of Salesforce


Overview

This article is a succinct hands-on deep dive on how to get data in and out of Salesforce.

3 billion transactions go through Salesforce daily.

Data Objects

sf-data-diagram-764x418-22034.jpg
Click to pop-up full screen image
*

PROTIP: Etherios EasyDescribe free app to view and extract object metadata by West Monroe Partners (of Chicago) is dated 2010 for Winter 11 / 1.91.0 and listed as “private”.

Layout Page free app from Clerisoft in 2015 converts any Salesforce Page Layout (Standard OR Custom) into a Standard Visualforce Page in just 3 steps.

There is more “smarts” with Master-detail vs. lookup:

  • Only 2 are allowed per object (vs 25 lookups)
  • Access to parent determines access to children.
  • All users who can access a specific parent record will also has access to all the related child records (parent has private OWD setting and has sharing rules on top of it)
  • Activities tagged to child records has to be shown on parent account too
  • When the parent record is deleted all the child records attached are deleted.
  • Rollup summary fields to parent based on SUM, AVG, MIN of child records
  • A child of one master detail relationship cannot be the parent of another.

References:

  • https://www.youtube.com/watch?v=fUD4MzgA0gk
  • http://certifiedondemand.com/lookup-or-master-detail-relationship-in-salesforce
  • http://www.salesforcetutorial.com/object-relationships-in-salesforce/
  • https://help.salesforce.com/HTViewHelpDoc?id=relationships_considerations.htm
  • https://help.salesforce.com/HTViewHelpDoc?id=overview_of_custom_object_relationships.htm
  • https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/relationships_among_objects.htm

Wizard vs Loader

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

 Data Import WizardApex Data Loader
Import data formatCSVCSV
Max. # records < 50,000 < 5 million
Update existing recordsyesno
Delete existing recordsnoyes
Catch duplicatesyesno
Import Opportunitiesnoyes
Can turn off Workflow rulesyesno
Can export datanoyes
Can save mappingsnoyes

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

Data Types

Data type—primitive types: collections, sObjects, user-defined types, and built-in Apex types.

There are three main types of collections in Apex:

  • Sets – unordered collection of elements that do not contain any duplicates.
  • Lists – ordered collection of elements distinguished by indices.
  • Maps – key-value pairs with each unique key mappings to a single value. Keys and values can be any data type—primitive type.

The import file should include a record owner for each record (defaulting to the account used to do importing).

Excel to CSV

Many use Microsoft Excel to create and edit CSV files for import into Salesforce.

There are some tricks to using it.

  1. Excel can open CSV files automatically when it’s double-clicked on Finder.
  2. Excel has no in-built way to specify the format of each field in CSV files. You’ll need to save the file in Excel format to manually specify number fields as such.

    PROTIP: The leading zero in Zip codes get stripped automatically.

    Double-quotes within text are problematic because they are also used to define the beginning and end of fields.

Excel Connector

The Force.com Excel Connector is an Add-on to Microsoft Excel via the Toolkit for Office. It provides bi-directional access to the Force.com API.

So it’s useful for cleaning and mass-updating salesforce.com-based data.

Updated features include access to Products2 and custom objects, API names or labels, simple query wizard, readable user names, etc.

It allows you to upload and export data directly in and out of an excel sheet.

Google Sheets

https://support.google.com/docs/answer/9073952?co=GENIE.Platform%3DDesktop&hl=en

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.

sf-soql-652x218

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 records at a time. Use this Linux command:

    wc -l importfile.csv
  9. Before each upload, run a report to count the number of records, so you have a “before” picture.

  10. Do a test run with just a couple records, so it’s easier to recover from mistakes.

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

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’s DataRaptor 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

Others:

  • Web Form
  • Email to Case
  • Email to database

Export / Backup

Backups in DE orgs can occur Monthly only.

Backups in prod (and Sandboxes) can occur Weekly or monthly (not daily).

The output is a set of 500MB zip file Salesforce emails to you. A backup file is a CSV file for each object, consists of many : one for each object and internal tables:

sf-backup-objects-598x410-28455

  • Export does not include any of your metadata (critical in rebuilding custom work and relationships).
  • Export does not include attached files.
  • Export can take a week or more to process through the queue.
  • Exports are not allowed from sandboxes.

More frequent backups can occur using a 3rd party app on AppExchange, such as ownbackup.com

https://reflectionenterprise.com/salesforce-data-export/

Data Quality

This is covered in the 211 course and:

Topics:

  • Assess, cleanse, and maintain data quality

  • Prevent duplicate records using Duplicate Management

  • Clean and enrich data with data.com, which uses external databases as the basis for cleaning Leads, Contacts, Accounts (not product Opportunities or Assets).

See validity.com #DemandTools, #PeopleImport, #DupeBlocker and #BriteVerify

Learning Modules

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

Account Data Strategies</a> +200 Learn how your sales team can use data to close deals.

References

  • http://www.arkusinc.com/archive/2013/comparing-data-loaders-for-salesforce “Comparing Data Loaders for Salesforce” (2013) by Roger Mitchell

  • http://www.salesforceben.com/best-data-loader-for-salesforce/ “Best Data loader for Salesforce” (2014) by Ben McCarthy

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

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

  • https://www.udemy.com/salesforce-administrator/learn/v4/t/lecture/4309758?start=0 Data Import Wizard and the Data Loader

  • https://www.udemy.com/salesforce-administrator/learn/v4/t/lecture/4812698?start=0 Additional Salesforce Data Management Tools

More about Salesforce

This is one of a series about Salesforce

  1. Salesforce index

  2. Salesforce Ohana (about the Salesforce organization and people)
  3. Salesforce Glossary (of acronyms)
  4. Salesforce Exhibitors (at Dreamforce)
  5. Salesforce Onboarding (Trailhead and IDEs)
  6. Salesforce Rock Stars (and influencers)

  7. Salesforce Offerings (Clouds, Industries, Domains, GitHub, editions, pricing, features, versions)
  8. Salesforce Certifications (training and exams)
  9. Salesforce Projects, Superbadges, and Sample Apps

  10. Salesforce Project Plans
  11. Salesforce Jobs (within Salesforce, with partners, etc.)

  12. Salesforce Apps (in AppExchange)
  13. Salesforce Alexa
  14. Salesforce Heroku (external apps)
  15. Salesforce DX (Developer eXperience)

  16. Salesforce Non-Profit Success Pack
  17. Salesforce NPSP (Non-Profit Success Pack) performance (with Gatling)

  18. Salesforce Data Management
  19. Salesforce Einstein
  20. Salesforce Selenium (test automation)

Tutorials under construction (listed alphabetically):

  • Salesforce Apex programming
  • Salesforce Apex Testing
  • Salesforce APIs
  • Salesforce Automation
  • Salesforce Bolt
  • Salesforce Customization (objects, fields, page layouts)
  • Salesforce Field Service
  • Salesforce Inbox
  • Salesforce IoT
  • Salesforce Lightning UX
  • Salesforce Mobile
  • Salesforce NPSP (Non-Profit Success Pack)
  • Salesforce Reporting & Analytics (Custom Reports)
  • Salesforce Security
  • Salesforce Selling Success Factors
  • Salesforce Visualforce