One Star

tFileInputExcel does not have an option to converts the date to string for .xls files

I am trying to create a generic job that will have an .xls file as an input and a dynamic schema since we will be using this for multiple files. This data will then be loaded to a .csv file.

The issue that I am facing is that I could not convert the dates to a desired format.

This is not an issue for .xlsx files since there is an option in the Advanced Settings that converts the data to string with the desired format.

image.png

This option is not present when the input is .xls (when the checkbox for Read excel 2007 is unchecked)

image.png

Because of this I could not specify the date patterns and it is just getting the format  in the .xls file.

I don't think it would be possible  to handle the conversion tMap since we want it to have a dynamic schema. I tried using talendDate.formatDate for the transformation but it encountered an error since the type is dynamic and not date.

image.png

I also tried to make a workaround which is to create a temporary .xlsx file from the .xls file. This .xlsx file will then be the one to load on the .csv file (with the convert date column to string option checked) but is still not working.

Here is the screenshot of the test job that I made:

 

image.png

Any advice on how to approach this?

Tags (1)
2 REPLIES
Moderator

Re: tFileInputExcel does not have an option to converts the date to string for .xls files

Hello,

Have you tried to replace your tFileInputExcel component with t<DB>Input to see if it works?

Best regards

Sabrina

 

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: tFileInputExcel does not have an option to converts the date to string for .xls files

Hi Sabrina,

My input is an excel file. How will it work if I am going to use a
tinput component?

Thank you.