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.
QUESTION: Please correct me if I’m wrong here.
There is a “dirty little open secret” about how companies actually work with Salesforce data.
Internally, Salesforce uses Oracle RAC databases, which “log ships” every change instantaneously to another region for disaster-proof recovery.
However, Salesforce does not provide a way for users to restore data from backups it has taken.
TL;DR What is your Salesforce RPO & RTO?
The IT industry-wide has names for recovery of data in case servers fail in enterprise data centers:
RTO (Recovery Time Objective) is the time that a company is willing to wait for data to be recovered. For Salesforce users not subscribing to a 3rd-party service, RTO would be in days or weeks rather than in minutes.
RPO (Recovery-Point Objective) is the amount of data that a company is willing to lose when restoring from disaster. Salesforce, by default, does not provide “point-in-time” recovery from “snapshots” of all data at various points in time.
Export of data using built-in Salesforce functionality is not automatically enabled when an account is established. And Salesforce limits full backups to once a week. Full sandboxes cost more money.
If you don’t run backup jobs, Salesforce charges a minimum of $10,000 to obtain that data, which will take a week or more.
Sandboxes for developer use are created only with metadata, not data.
Salesforce does not provide a comprehensive way to import CSV files exported. So intricate programming is needed, or pay thousands a month for 3rd-party utilities (described below).
(During cloning, existing users and their status and appends the name of the sandbox to logon Id’s, but keeps passwords.)
Salesforce encountered a data issue on May 17, 2019 related to Pardot.
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
Export .csv from Salesforce
There are several ways to obtain an export:
- Use the Salesforce GUI
B. Use the FuseIT CLI program for Windows
FieldDump from AppExchange
FieldDump is a free add-on via AppExchange that extracts a Data Model to a spreadsheet readable by Microsoft Excel, Google Sheet, etc.
Export using Salesforce GUI
- In Salesforce, enter Setup to look for “Export” (“Data Export”).
Notice object names are not sorted.
- Click checkbox “Include all data” under the “Weekly Export Service” heading.
- Click “Export Now”. Click “Schedule Now”
- Wait for an email with subject “Your Organization Data Export has completed.”
- In your email program (Outlook/Exchange, etc.), download the zip file by clicking the link. The file name is like “WE_00DG0000000gbbAMAQ_1.ZIP”.
Export using FuseIT
Salesforce Consultants FuseIT in New Zealand has a Salesforce Explorer
https://fuseit.com/Solutions/SFDC-Explorer/Help-Data-Exports.aspx that includes “Data Exports”. Their MongoDB for Salesforce (“M4S”) connector (super cache) for ASP.NET web app integration (which they call “G4S”).
Their CLI for Windows, after download and install, can be run by a scheduler to request exports with a command such as:
FuseIT.SFDC.DataExportConsole.exe \/u:firstname.lastname@example.org /p:12345678 \/t:C3P3qivIf5t6Q6uYtzxxxxxx \/e:Production “C:\\SFDCexports”
.csv files exported
Within the export (backup) zip file are CSV files for each object. One file for each object and internal table. Remember:
- 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.
Export Backups in DE (Developer eXperience) orgs can occur Monthly only.
Backups in prod (and Sandboxes) can occur weekly or monthly.
CAUTION: Salesforce does not allow daily backups.
More frequent backups can occur using a 3rd party app on AppExchange.
Each .csv file cannot be large than 500MB.
Spreadsheet of .csv files
Our spreadsheet of objects exported has 331 data rows plus a header row.
Column names in the header row begin with an underline so they always sort to the top. Thus, column A is named “_Seq”,
The “_Backup File Name” column contains file names ending in .csv. The list was created by Salesforce after all objects were selected in the export form.
My list contains objects from several add-ons:
- CnP_… for “Click and Pledge”
- EventbriteSync__… for the Eventbrite activity calendar synchronization
- MC4SF__… for MailChimp for Salesforce (4SF) from AppExchange
CAUTION: Read the reviews to each add-on listed above.
_SEN containing TRUE mark fields which contain sensitive information such as email address, birthdates, Social Security Numbers, bank account identifiers, and other personally identifiable information (PEI). These fields need to be cleansed when not in the production instance.
_Rows counts the rows in each .csv file.
- “0” values are for blank files.
- “1” values are for files containing just the header row but no data.
- “7” would be for a file with 6 data records plus a header.
_LABEL is the checkbox field lable in the Export form on Salesforce.
_API_NAME and _DEPLOYED are from the Object Manager at:
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.
Issues with reading CSV in Excel
The issue with .csv files exported are these:
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).
Schema of Data Object Dependencies
PROTIP: One cannot just insert data of any given object from a .CSV file because of dependencies.
The Salesforce Console provides a dependency viewer at:???
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.
There are many-to-many relationships. These are defined using junction objects which combines in a custom object two master-detail relationships.
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.
Insert .CSV into Salesforce
To insert data within .csv back into Salesforce, there are several approaches:
For “DYIers” who don’t want to spend extra money but have the technical chops:
- Write Java Apex codeto run within a Salesforce Console E. Write REST API callswithin a custom web app program (in Java, Python, or other language) to insert into Salesforce databases.
Because Salesforce generates .csv files that Microsoft Excel and Google Sheet can read, many think that Salesforce should also read .csv files as input for import as well, in a “round-trip”.
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.
QUESTION: What abut web-based (SaaS) Microsoft 360? or Google Sheets?