Salesforce: Mass Upload files to Files using Data Loader

Many years ago, I wrote a blog Salesforce: Mass Upload files to Attachment using Data Loader, that blog explains step by step from preparing CSV file and execute from Data Loader to upload file into Salesforce as Attachment.

In the era of Lightning, "attachment" has been replaced by "files", if you are still using the old "Notes & Attachments" related list in Lightning, the file uploaded from Lightning actually is stored as Files, no longer as Attachment.

1. Prepare CSV file
You can use Microsoft Excel to create new CSV file with following columns:

• Title - this is text that will show as Title of the file. 
• Description - (optional) description if the file.
• VersionData - complete file path on your local drive, example: c:\data\test1.docx
• PathOnClient - complete file path on your local drive, example: c:\data\test1.docx
• OwnerId - (optional) file owner, if not provided, owner would be to the user uploading the file.
• FirstPublishLocationId - this can be Account Id for file need to be linked to Account, or Opportunity Id, or User Id, or Library Id (Library Id start with prefix 058).
• RecordTypeId - if you define record type under "Salesforce Files" setup menu. To check if you have any record type for Files, query this Select Id, Name FROM RecordType WHERE SobjectType = 'ContentVersion'.
• TagsCsv - (optional) tag.

A sample of CSV file here.

2. Data Loader
With the latest Data Loader application, you can login to Salesforce with OAuth or Password Authentication, make sure to select "Show all Salesforce objects", then look for object "Content Version (ContentVersion)".

Follow the steps, if no error occurred, all the files listed in the CSV file should be loaded to Salesforce as Files. If you are using Lightning but still using Notes & Attachment related list, you will see the file is appeared in Notes & Attachment too, although technically, it is stored as Files, instead of Attachment.

Reference:

• Upload Content with the Data Loader
• Mass Upload files to Attachment using Data Loader

Salesforce: Data Import Wizard to update Account

Data Import Wizard may not be so famous compare to Data Loader or other 3rd party tools, such as: dataloader.io or workbench. But, for simplicity and to be used by super user (not system admin), Data Import Wizard may be an option.

Use case: super user need to mass update certain fields. List View is not an option because the fields will be update with different values and for multiple fields.

Step-by-Step:
1. Navigate to Data Import Wizard menu: Setup | Data Management | Data Import Wizard

2. Click Launch Wizard! button

3. Select "Accounts and Contacts" under Standard object, if the user does not see "Accounts and
Contacts", make sure the user have Import Personal Contacts permission.

4. Select "Update existing records" under "What do you want to do?"

5. Leave "Match Contact by" to default value since we are not updating Contact

6. Change "Match Account by" to "Salesforce.com ID" since I have Salesforce Account Id

7. Make sure to select "Update existing Account information"

8. Drag CSV file to upload

9. Click Next to continue

10. Click Change link under "Account: Salesforce.com ID, Contact: Salesforce.com ID"

11. Remove Contact Id from mapping

12. Click Next, then Start Import button


Once done, user should get email from Salesforce with:

Subject: Salesforce import of "filename.csv" has finished. 999 rows were processed.
Content:
Your Accounts import is complete. Here are your results:
Accounts Created: 0
Accounts Updated: 999
Accounts Failed:  0 (We couldn't import these due to errors.) Processed job information for
imported Accounts: No link is available because you do not have the correct permissions to
manage Bulk Api jobs.

If user have Bulk Api jobs permission, they will see:
Accounts Failed:  0 (We couldn't import these due to errors.) Processed job information for
imported Accounts: https://na3.salesforce.com/75017000001eNj3AAE?fromEmail=1


Permissions
- Permission to manage Bulk Api jobs is Manage Data Integrations
- User will only see Data Import Wizard setup menu from Setup | Data Management, if only user have any of the following permissions:

• Import Custom Objects
• Import Leads
• Import Personal Contacts
• Import Solutions

Reference:

• Import Data with the Data Import Wizard
• Monitoring Bulk Data Load Jobs

Blog last update: 23 Oct 2016

Salesforce: Data Loader Time Zone

For most Salesforce administrator and consultant, Data Loader is a familiar tool when work with data. Data Loader is a simple yet powerful tool provide by Salesforce to insert, update, upsert, export, and delete data in Salesforce. To insert, update, upsert and delete Salesforce record, you need to prepare the data in CSV file format. After execution, it will provide you success and error log in the CSV format as well.

But, many users not realize about Time Zone setting in Data Loader. By default, after you install Data Loader, it will follow your computer time zone. This will cause issue when you load data for Date or Date Time field.

If you refer to my blog earlier regarding upload Quota, where Start Date in Revenue Quota is not correctly loaded, this is one of the sample caused by incorrect Time Zone setting in Data Loader.

In this exercise, we are not turning on "Use European date format (dd/mm/yyyy)".

Scenario 1: insert or update Date field in Salesforce:

• You set Data Loader time zone to eastern GMT time zone (from GMT+01:00 to GMT+14:00), example: Asia/Singapore GMT+08:00
• You provide the data in CSV with date only (format yyyy-MM-dd), without time, example: 2015-02-14

Result: this will cause the date auto adjust to 1 day earlier of the date specify in CSV file.

Analyse: even for Date field (not Date Time field), if you do not provide time in the CSV file, it will treat the time as 00:00:00. So, 2015-02-14 added with time 00:00:00 for Asia/Singapore time zone, it is equal to 2015-02-13T16:00:00 GMT. In Salesforce, it will stored as 2015-02-13, instead of 2015-02-14.

Solution:

• Add time x hours as your time zone setting in Data Loader, example: 2015-02-14T08:00:00.000 SGT
• To make our life easier, change Data Loader time zone setting to GMT, you do need to add time anymore.

Scenario 2: insert or update Date Time field in Salesforce:

Option (a): CSV is prepared with date only, Salesforce accept the date in 3 formats below:

• yyyy-MM-dd
• MM/dd/yyyy
• yyyyMMdd

In this exercise, Salesforce user time zone is (GMT+08:00) Singapore Standard Time (Asia/Singapore). Let us see how is the result, when Data Loader time zone is set as GMT and as Asia/Singapore.

Source	GMT	Asia/Singapore
2015-02-16	2/16/2015 8:00 AM	2/16/2015 12:00 AM
02/16/2015	2/16/2015 8:00 AM	2/16/2015 12:00 AM
20150216	2/16/2015 8:00 AM	2/16/2015 12:00 AM

Summary from this exercise, if we load date time field without time given, set Data Loader time zone the same as User time zone to get 12:00 AM

Option (b): CSV is prepared with date and time, Salesforce will accept following format:

• yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
• yyyy-MM-dd'T'HH:mm:ss.SSS SGT
• yyyy-MM-dd'T'HH:mm:ss.SSSSGT
• yyyy-MM-dd'T'HH:mm:ss.SSS GMT-08:00
• yyyy-MM-dd'T'HH:mm:ss
• yyyy-MM-dd HH:mm:ss
• yyyyMMdd'T'HH:mm:ss
• MM/dd/yyyy HH:mm:ss

and NOT following format:

• yyyy-MM-dd'T'HH:mm:ss.SSS Singapore Standard Time
• yyyy-MM-dd'T'HH:mm:ss.SSSSingapore Standard Time
• yyyy-MM-dd'T'HH:mm:ss.SSSGMT-08:00
• yyyy-MM-dd'T'HH:mm:ss.SSS -800
• yyyy-MM-dd'T'HH:mm:ss.SSS-800

Source	GMT	Asia/Singapore
1) 2015-02-16T09:00:00.000Z	2/16/2015 5:00 PM	2/16/2015 5:00 PM
2) 2015-02-16T09:00:00.000 SGT	2/16/2015 9:00 AM	2/16/2015 9:00 AM
3) 2015-02-16T09:00:00.000SGT	2/16/2015 9:00 AM	2/16/2015 9:00 AM
4) 2015-02-16T09:00:00.000GMT+08:00	2/16/2015 9:00 AM	2/16/2015 9:00 AM
5) 2015-02-16T09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM
6) 2015-02-16 09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM
7) 02/16/2015 09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM
8) 20150216T09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM

Summary:
1) both time zone will return 5 PM, because Asia/Singapore = GMT+08:00 plus 9 hour = 5 PM
2 - 4) both time zone will return 9 AM, because we have defined time zone in the source data
5 - 8) GMT setting will return GMT+08:00 (this is user time zone) plus 9 hour = 5 PM, while Asia/Singapore setting will return GMT+08:00 (this is user time zone) minus Asia/Singapore (this is Data Loader setting time zone) plus 9 hour = 9 AM

Following information cite from Data Loader guide. If a date value does not include a time zone, this value is used:

• If no value is specified, the time zone of the computer where Data Loader is installed is used.
• If an incorrect value is entered, GMT is used as the time zone and this fact is noted in the Data Loader log.

The value can be a full name such as America/Los_Angeles, or a custom ID such as GMT-8:00


Reference:

• Salesforce Supported Time Zones
• Data Types Supported by Data Loader
• How can I ensure my dates are correct when loading with the Data Loader?

Salesforce: Data Loader Sandbox and Null Values

If you are using Salesforce Enterprise Edition or Unlimited Edition, you can use Data Loader to import, update, delete or export data to/from any object in Salesforce.com. In this blog, we will share 2 tips:

Login to sandbox instance
By default, after you install Data Loader, the default setting is configure to login to Production instance. You need to change in Data Loader setting to point it to sandbox instance.
Go to Settings and change the default value of "Server host (clear for default)" from https://login.salesforce.com to https://test.salesforce.com

Update fields to null
If you update a field with blank value, if the value have existing value, notice the value will stay the same (not updated to blank). To overwrite existing value with blank, you need to change Data Loader setting.
Go to Settings and select "Insert null values".

This option is not available if the "Use Bulk API" option is selected. Empty field values are ignored when you update records using the Bulk API. To set a field value to null when the Use Bulk API option is selected, use lue of  #N/A  in the CSV file.