Salesforce Data Loader Date Format

Salesforce is all about data management. It allows the users to record, store, track, and analyze specific data sets that belong to the customers catered by an organization and the organization itself. It is this data stored in specific Salesforce objects that helps the users in providing personalized services to their customers and obtain valuable insights about a variety of business processes.

This makes it important for every Salesforce user to manage their database with extreme precision and accuracy. You cannot afford to load incorrect or insufficient data into your systems. This may result in dire implications like mismanagement of processes and faulty communication.

To make sure that the data imported into and exported out of your Salesforce org is accurate, Salesforce provides the users with a securer and automated data management tool – Salesforce Data Loader.

What Is Salesforce Data Loader?

Salesforce Data Loader is a data management tool for Salesforce users that helps them import and export data in bulk. It provides you with the functions of insert, update, delete, and export Salesforce records.

For importing data from a specific source to Salesforce, Data Loader reads, extracts, and loads data either from the CSV (comma-separated values) files or from a database connection. For exporting data from Salesforce to a specific destination, the tool outputs CSV files to the said location.

Salesforce Data Loader can be used in two distinct ways – through the user interface and through the command line.

On working with the user interface, you work in an interactive way for specifying the parameters, CSV files used for import or export, and the field mappings that help you map the field names in your import file with the field names in Salesforce.

On the other hand, when you work with a command line, you specify the configuration, mappings, data sources, actions, and mappings in your files. This is what helps you set up the tool for automated data processing.

Key Features Of Data Loader

Here are some of the key features of Salesforce Data Loader that help the users in managing their Salesforce data:

1. It provides users with an easy-to-use wizard interface for seamless processes

2. It facilitates automated batch operations through an alternate command line (for Windows only)

3. It supports management of files with up to 5 million records

4. It offers easy drag-and-drop field mapping functionality

5. It supports import and export of all Salesforce objects (standard and custom)

6. It allows the processing of data on both Salesforce and Database.com

7. It provides users with detailed error and success log files in CSV format

8. It comes with an in-built CSV file viewer

Date Format In Salesforce Data Loader

Whenever you are managing your data in your Salesforce account, the dates of the files play an important role in tracking specific information. Keeping a note of the dates helps you in understanding the context of your data sets and assessing their relevance over time.

The Salesforce Data Loader date format can be expressed in American (MM/DD/YYY) and European (DD/MM/YYYY) formats.

Here are some of the major types of Salesforce Data Loader date format with examples:

MM/DD/YYY (example: 08/12/2019)

DD/MM/YYYY (example: 14/11/2019)

YYYY/MM/DD (example: 2020/12/06)

Military Time Zones – MM/DD/YYYYThh:mm:ss.mmmZ (example: 12-05-2019T12:04:12.000Z)

3 Letters Time Zones plus Offset – MM/DD/YYYYThh:mm:ss:mmmXXX+hh:mm (example: 04/12/2019T12:04:12.000GMT+03:00)

3 Letters Time Zones – MM/DD/YYYYThh:mm:ss.mmmXXX (example: 05/22/2019T12:04:12.000GMT)

3 Letters Time Zones – YYYY-MM-DDThh:mm:ss.mmmXXX (example: 2019-11-09T12:04:12.000GMT)

Changing Date Formats For Salesforce Data Loader

For changing your Salesforce Data Loader date format, you can go to Settings and select “Date/Time” for setting your date format and time zone.

Why Do Date Fields Import Incorrectly While Using Data Loader?

There may be instances when the Data Loader imports the data fields of your files incorrectly while importing them to Salesforce. This is because the Data Loader automatically converts the date mentioned in the CSV files to GMT.

If the time zone in your system has not been set or if the clock sets itself to the daylights savings time (DST), there are chances of your dates being inconsistent by a day.

For preventing Data Loader from changing the dates when you import files to your Salesforce org, you can directly change the format of cells that contain dates in a way that they reflect the native time zone.

Follow these steps:

1. Open the concerned CSV file in Microsoft Excel.

2. In every cell in which you entered dates in, add hour data for representing the concerned native time zone. For example, if the date in the file is May 5, 2018, and the time zone is GMT + 8, enter May 5, 2018 8:00. Excel will automatically reformat the date to 5/5/2011 8:00.

3. Right-click on the cell where you entered the dates and click on “Format Cells.”

4. Click on Number | Custom.

5. In Type, enter yyyy-mm-ddThh:mm:ss.sssZ. For example, if the cell showed 5/5/2018 8:00, the date will now be 2018-05-05T08:00:00.00Z.

Tips To Keep In Mind Relating To Date Formats

Here are a few important tips you should keep in mind pertaining to date formats in Salesforce Data Loader:

1. If you want to enable date formats that begin with the day instead of the month, choose the “Use European Date Format” box in the Settings dialog.

2. If your computer system is set to the east of Greenwich Mean Time (GMT), it is advisable to change the time zone to GMT to prevent inconsistencies in the date format while inserting or updating your records.

3. It is important to note that the Data Loader only permits dates within a specific range. The earliest valid date is 1700-01-01T00:00:00Z GMT (just after midnight on January 1, 1700). On the other hand, the latest valid date it set to 4000-12-21T00:00:00Z GMT (just after midnight on December 31, 4000). These values are offset based on the time zone you belong to.

4. Now that you are thorough with all the important matters pertaining to the Salesforce Data Loader date format, let us have a look at important tips for preparing your files for the transfer.

Tips For Preparing Your Files For Salesforce Data Loader

Although Data Loader is a fairly convenient and automated data management tool, it is important to make sure that import, export, and update your data sets with precision.

Here are some of the most important tips that you should keep in mind while preparing your files for Salesforce Data Loader:

1. Always make sure that the files that you want to import are in the CSV format before they are uploaded. However, it is easy to work in Excel format to preserve formatting if you are not able to complete converting all the files into CSV in a single session.

2. When you are saving your files as CSV, it is important to open the file to make sure that there is no corruption upon making the conversion. However, re-save your file as CSV before uploading to preserve the formatting of dates.

3. Make sure there are no empty columns or columns with the same header names. This might lead to field mapping issues.

4. Make sure you have populated a column header and rows of data for all system required fields, such as Account Name or Contact Last Name.

5. Before you save your Excel file as CSV, make sure that you leverage the functionality of conditional formatting for highlighting cells with duplicate values in the columns that should not have duplicate data.

6. If you carry out data formatting in an Excel file before converting it to CSV, you will need to reapply the formatting. CSV files do not preserve formatting.

7. On applying formatting to a CSV file, make sure to save it and keep it open before importing the file to preserve the formatting.

8. Make use of the Excel cell format of “Phone Number” for formatting the phone and fax columns.

9. Always make sure that all email addresses are formatted correctly. Faulty emails will not be accepted by the Salesforce email fields, affecting your business communication. Check for details as small as commas and dots before importing your data.

10. Get rid of all currency symbols and commas from the number and currency columns.

11. Make sure you do not add text to the number fields in Salesforce.

12. In case of checkboxes, use “1” or TRUE for checked and “0” or FALSE for unchecked. Also, you can leave the unchecked cells blank.

13. Salesforce Data Loader does not allow the import of multiple columns into a single field in Salesforce.

14. The Data Loader will not support Address 1,2, and 3 fields. If you are willing to import more than one street address for a record, you can check the Salesforce Help topic of “Account Billing Address Fields.”

Data management in Salesforce should never be taken lightly. If you take these aspects into consideration before going ahead with loading your data into Salesforce, the Data Loader will help you sail through seamlessly.