Date format Issue while reading ms excel with tfileinputexcel

[Anonymous]

Hi-
I have been working on extracting the large data excel files to the MySQL O/P. The data spreadsheets contains the date columns in date format. As I need to process the large files, I am using the Source component tfileinputexcel with the  Advanced settings Generation Mode: Less Memory Consumed for large files(Event Mode).
While reading the excel file date column as string in Event mode, The date format is converting from "MM/dd/yyyy" to "MM/dd/yy". In detail, If the date data is "12/31/2014" then reading as "12/31/14" and if the date data is "12/31/1923" then its reading as "12/28/23".
After applying the below expression to convert to format "MM/dd/yyyy",
row1.date== null ? null :TalendDate.formatDate("MM/dd/yyyy" , (TalendDate.parseDate("MM/dd/yy" ,row1.date)))
I am facing the date data loss. Here, "12/31/14" and "12/31/1923" is converting to as "12/31/2014". I am getting data issue for the Years >=1990 .
Please suggest any one how could I handle this scenario.
Please see the below input file and tfileinputexcel attachment.
https://www.talendforge.org/forum/img/members/312129/mini_input_date.jpg https://www.talendforge.org/forum/img/members/312129/mini_excel_date.jpg

Please share your suggestions. Your suggestions will be highly appreciated.
Talend Version: Talend 5.6.2
Thank you.!

Kind Regards,
Raghav K                                                                           
 

[TRF]

Hi,
Did you try to change the date format to "MM/dd/YYYY" for the concerned fields when configuring the tFileInputExcel?
Else, you may also try using a string datatype then convert it to date to use with TalendDate methods.
Hope this helps.
Regards

[gorotman]

Hi,
colud you try to change date format in excel?
Value remain the same, but chenge cells format from "MM/dd/yyyy" to "yyyy/MM/dd"(and adapt your formulas).
Let me know if it works.
P.S. I saw that class used with "less memory..." options gets cell value in different ways, and value may depends from excel format. So you can try different date format, may one fix your problem.
Bye

[Anonymous]

Hi-
Thank you for response and sharing your valuable ideas.
I am working with Tfileinputexcel in Advanced settings Generation Mode: Less Memory Consumed for large files(Event Mode) and reading the concerned date field as String. In my requirement, As I need to do some validation I shouldn't read the column as Date.
If the excel cell format is formatted to "MM/dd/yyyy", then this issue will work and I can not change the excel format because we are getting client generated spread sheets.
Please suggest me how to handle this issue in the case ,if the cell format of excel is date and reading in the tfileinputexcel as String.
Kind Regards-
Raghav

[gorotman]

Hi

I am working with Tfileinputexcel in Advanced settings Generation Mode: Less Memory Consumed for large files(Event Mode) and reading the concerned date field as String. In my requirement, As I need to do some validation I shouldn't read the column as Date.

Why use this Generation Mode?
Large excel files? Reduce memory? Fast loading?

If the excel cell format is formatted to "MM/dd/yyyy", then this issue will work and I can not change the excel format because we are getting client generated spread sheets.
Please suggest me how to handle this issue in the case ,if the cell format of excel is date and reading in the tfileinputexcel as String.

Sorry, I've no idea. By my tests, this API gets cell value as text using format declared in excel. So in TOS you have year with 2digits and then values are ambiguous (05 -> 1905 or 2005?). Java date class resolves ambiguous values as 20xx, as a convention. If you have a strong rule to choose between 1905 and 2005, then you can cast string to date using calendar class (or similar), specifing year, month and day separately.
Examples

Calendar c = Calendar.getInstance();
c.set(year, month, day, 0, 0);  

LocalDate.of(year, month, day); //2015-12-22
LocalDate.parse("2015-12-22");

[Anonymous]

Thanks for your Response.
Yes I am using tfileinputexcel generation mode for working with the large excel files and to avoid the out of memory exception errors.
I will try with your answers and let you know if problem will be resolved.
Thank You!!!
Kind Regards,
Raghav K