You need to sign in to do that
Don't have an account?
Ian Bate 9
Data Loader Date Issue - Not the usual one
Hi there
I have an Amazon VM Windows server and I have a Powershell script to download a CSV file. The date format in the CSV file is DD/MM/YYYY. I can't change the date format in the file becaus this is intended to be a daily automated process. Lots of people on the forum have problems with dates going in one day offset etc. but this problem is something else...
The Powershell script runs Data Loader on the command line to load the CSV file. Data Loader settings have the 'Use European Date Format' checked.
The server Control Panel has all the regional settings, time zone etc. set to UK.
I'm loading the dates in the CSV file into SF Date type field.
Problem is that the dates get transposed, e.g. June 1st 2018 in the file is 01/06/2018 but ends up in SF as Jan 6th.
Does anyone have any ideas? My workaround will be to load it into a text field and then convert/transpose it but I'd rather not. And I shouldn't have to should I?
Thanks in advance.
I have an Amazon VM Windows server and I have a Powershell script to download a CSV file. The date format in the CSV file is DD/MM/YYYY. I can't change the date format in the file becaus this is intended to be a daily automated process. Lots of people on the forum have problems with dates going in one day offset etc. but this problem is something else...
The Powershell script runs Data Loader on the command line to load the CSV file. Data Loader settings have the 'Use European Date Format' checked.
The server Control Panel has all the regional settings, time zone etc. set to UK.
I'm loading the dates in the CSV file into SF Date type field.
Problem is that the dates get transposed, e.g. June 1st 2018 in the file is 01/06/2018 but ends up in SF as Jan 6th.
Does anyone have any ideas? My workaround will be to load it into a text field and then convert/transpose it but I'd rather not. And I shouldn't have to should I?
Thanks in advance.
There is an option : set the following in the process-config.xml file.
<entry key="process.useEuropeanDates" value="true"/>
- process.useEuropeanDates = boolean = Use European date formatSelect this option to support the date formats dd/MM/yyyy and dd/MM/yyyy HH:mm:ss. Sample value: true
https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/loader_params.htmA sample process-conf.xml file is in the \samples\conf directory that’s installed with Data Loader.
Data Types Supported by Data Loader:
https://help.salesforce.com/articleView?id=supported_data_types.htm&type=5&lang=en
Date Formats:
We recommend you specify dates in the format yyyy-MM-ddTHH:mm:ss.SSS+/-HHmm:
- yyyy is the four-digit year
- MM is the two-digit month (01-12)
- dd is the two-digit day (01-31)
- HH is the two-digit hour (00-23)
- mm is the two-digit minute (00-59)
- ss is the two-digit seconds (00-59)
- SSS is the three-digit milliseconds (000-999)
- +/-HHmm is the Zulu (UTC) time zone offset
The following date formats are also supported:- yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
- yyyy-MM-dd'T'HH:mm:ss.SSS Pacific Standard Time
- yyyy-MM-dd'T'HH:mm:ss.SSSPacific Standard Time
- yyyy-MM-dd'T'HH:mm:ss.SSS PST
- yyyy-MM-dd'T'HH:mm:ss.SSSPST
- yyyy-MM-dd'T'HH:mm:ss.SSS GMT-08:00
- 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
- yyyy-MM-dd'T'HH:mm:ss
- yyyy-MM-dd HH:mm:ss
- yyyyMMdd'T'HH:mm:ss
- yyyy-MM-dd
- MM/dd/yyyy HH:mm:ss
- MM/dd/yyyy
- yyyyMMdd
Note the following tips for date formats:If you read the source code of the dataloader :
1) com.salesforce.dataloader.config.Config.java:
public static final String EURO_DATES = "process.useEuropeanDates"; //$NON-NLS-1$
2) com.salesforce.dataloader.dyna.SforceDynaBean
3) DateConverter.java (https://github.com/forcedotcom/dataloader/blob/master/src/main/java/com/salesforce/dataloader/dyna/DateConverter.java)
https://github.com/forcedotcom/dataloader/blob/master/src/main/java/com/salesforce/dataloader/dyna/DateConverter.java
String baseDate = europeanDates ? "dd/MM/yyyy" : "MM/dd/yyyy";
All Answers
There is an option : set the following in the process-config.xml file.
<entry key="process.useEuropeanDates" value="true"/>
- process.useEuropeanDates = boolean = Use European date formatSelect this option to support the date formats dd/MM/yyyy and dd/MM/yyyy HH:mm:ss. Sample value: true
https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/loader_params.htmA sample process-conf.xml file is in the \samples\conf directory that’s installed with Data Loader.
Data Types Supported by Data Loader:
https://help.salesforce.com/articleView?id=supported_data_types.htm&type=5&lang=en
Date Formats:
We recommend you specify dates in the format yyyy-MM-ddTHH:mm:ss.SSS+/-HHmm:
- yyyy is the four-digit year
- MM is the two-digit month (01-12)
- dd is the two-digit day (01-31)
- HH is the two-digit hour (00-23)
- mm is the two-digit minute (00-59)
- ss is the two-digit seconds (00-59)
- SSS is the three-digit milliseconds (000-999)
- +/-HHmm is the Zulu (UTC) time zone offset
The following date formats are also supported:- yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
- yyyy-MM-dd'T'HH:mm:ss.SSS Pacific Standard Time
- yyyy-MM-dd'T'HH:mm:ss.SSSPacific Standard Time
- yyyy-MM-dd'T'HH:mm:ss.SSS PST
- yyyy-MM-dd'T'HH:mm:ss.SSSPST
- yyyy-MM-dd'T'HH:mm:ss.SSS GMT-08:00
- 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
- yyyy-MM-dd'T'HH:mm:ss
- yyyy-MM-dd HH:mm:ss
- yyyyMMdd'T'HH:mm:ss
- yyyy-MM-dd
- MM/dd/yyyy HH:mm:ss
- MM/dd/yyyy
- yyyyMMdd
Note the following tips for date formats:If you read the source code of the dataloader :
1) com.salesforce.dataloader.config.Config.java:
public static final String EURO_DATES = "process.useEuropeanDates"; //$NON-NLS-1$
2) com.salesforce.dataloader.dyna.SforceDynaBean
3) DateConverter.java (https://github.com/forcedotcom/dataloader/blob/master/src/main/java/com/salesforce/dataloader/dyna/DateConverter.java)
https://github.com/forcedotcom/dataloader/blob/master/src/main/java/com/salesforce/dataloader/dyna/DateConverter.java
String baseDate = europeanDates ? "dd/MM/yyyy" : "MM/dd/yyyy";
OK, so what I now understand is that when you run Data Loader at the command line it ignores the Settings that I set in the DL UI. So I added <entry key="process.useEuropeanDates" value="true"/> to the config and that sorted the issue.
However, I still have the problem with dates being loaded as the previous day... So, my solution is to load the date as a Text field and added a formula DATE(VALUE(MID(TXT__c, 7, 4)),VALUE(MID(TXT__c, 4, 2)),VALUE(MID(TXT__c, 1, 2))).
The previous day is a problem of time zone used by the Microsoft Windows user that you use for the loadings with the data loader.
1) Note the following tips for date formats:
- If your computer's locale is east of Greenwich Mean Time (GMT), we recommend that you change your computer setting to GMT in order to avoid date adjustments when inserting or updating records.
https://help.salesforce.com/articleView?id=supported_data_types.htm&type=5&lang=enComputer's locale is not the sames as the Salesforce locale of the connected user.
2) Time Zone: Select this option to specify a default time zone.
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. (your case is here)
- If an incorrect value is entered, GMT is used as the time zone and this fact is noted in the Data Loader log.
- Valid values are any time zone identifier which can be passed to the JavagetTimeZone(java.lang.String) method. The value can be a full name such as America/Los_Angeles, or a custom ID such as GMT-8:00
https://help.salesforce.com/articleView?id=configuring_the_data_loader.htm&type=53) How to Change the Time Zone in Windows 10: should be GMT.
https://www.tenforums.com/tutorials/6401-change-time-zone-windows-10-a.html