How to get data in and out of Salesforce
- Data Objects
- Wizard vs Loader
- Data Types
- Excel to CSV
- In-built Data Import Wizard
- In-built Apex Data Loader
- Jitterbit ODBC/JDBC Data Loader
- Informatica Cloud Data Loader
- BOFC (Bulk Object Field Creator)
- Export / Backup
- Data Quality
- Learning Modules
- More about Salesforce
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.
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.
Wizard vs Loader
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|
|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.
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.
- Excel can open CSV files automatically when it’s double-clicked on Finder.
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.
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.
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:
Create a dedicated Data Loader Salesforce user account.
Login by appending the Security Token to extend the account’s Password.
Set that account’s User Profile to not fire triggers, workflows, or other automation.
Setup that user whenever you setup automation.
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.
Toggle the flag on/off depending on the type of data load.
Download the DataLoader.
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
Before each upload, run a report to count the number of records, so you have a “before” picture.
Do a test run with just a couple records, so it’s easier to recover from mistakes.
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.
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 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.
Jitterbit ODBC/JDBC Data Loader
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
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’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 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.
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.
Apsona is a set of SaaS-based tools for Salesforce.
- 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:
- 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
This is covered in the 211 course and:
- Trailhead module: Data Quality +700
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
Project: Import and Export with Data Management Tools +300 to use Data Loader and the Data Import Wizard to manage data in Salesforce.
Data Management +550 Learn how to import and export data in Salesforce.
SalesforceIQ to Sales Cloud Setup +1000 to optimize your new Sales Cloud org for your growing business.
Data Modeling +1500 to give your data structure with objects, fields, and relationships.
Large Data Volumes +400 Plan for and work with large data volumes within Salesforce.
Data Migration and Service Cloud Setup +200 to move your data from Desk.com to Service Cloud and perform your initial setup.
Custom Metadata Types +1400 Learn how to customize, deploy, package, and upgrade application metadata with ease.
Account Data Strategies</a> +200 Learn how your sales team can use data to close deals.
- Lightning Data Service Basics +1100 Learn about force:recordData, its benefits, and how to use it in your components.
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
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
- Salesforce Ohana (about the Salesforce organization and people)
- Salesforce Glossary (of acronyms)
- Salesforce Exhibitors (at Dreamforce)
- Salesforce Onboarding (Trailhead and IDEs)
- Salesforce Offerings (Clouds, Industries, Domains, GitHub, editions, pricing, features, versions)
- Salesforce Certifications (training and exams)
- Salesforce Project Plans
- Salesforce Apps (in AppExchange)
- Salesforce Alexa
- Salesforce Heroku (external apps)
- Salesforce Non-Profit Success Pack
- Salesforce Data Management
- Salesforce Einstein
- 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