topic Re: Date format issuse in Excel file(xlsx) in Talend Studio

Date format issuse in Excel file(xlsx)

Anonymous — Tue, 28 Jan 2014 05:46:56 GMT

Hi,
I have a .xlsx file having two date columns A and B
A B
11/28/2011 11/28/2011
the format of Col A is date and format of Col B is Text
when I load the same in database I get the following output
Also I am reading both the columns as String
A B
Mon Nov 28 00:00:00 IST 2011 11/28/2011
so in col A the timestamp is attached and Col B is as it is
Can anyone help me figure out why this is happening
Regards,
Ojas

Re: Date format issuse in Excel file(xlsx)

Anonymous — Tue, 28 Jan 2014 06:16:35 GMT

Hi Ojas,
To which database r u loading? In MS SQL server database u don't have date datatype. So, it will load in MS SQL SERVER Database by default as datetime format.
With Regards,
Viswanath.Ch.

Re: Date format issuse in Excel file(xlsx)

Anonymous — Tue, 28 Jan 2014 06:22:44 GMT

Hi,
I am using Oracle sql Developer database for loading
Regards,
Ojas

Re: Date format issuse in Excel file(xlsx)

Anonymous — Tue, 28 Jan 2014 06:34:11 GMT

Hi,
I don't think there is DATE datatype in Oracle Sql because of that it is storing in Timestamp format. I have worked with MS SQL Server database. When i use to load data with date, it will convert date format to datetime.
With Regards,
Viswanath.Ch.

Re: Date format issuse in Excel file(xlsx)

Anonymous — Thu, 30 Jan 2014 01:06:18 GMT

My first question is, where do you see the different output formats?
You shows us 2 different string formats of the same date and this can only happened in a tLogRow component or similar. In the Oracle database the internal format has nothing to do how you see the format in any tool as text presentation. I think the database gets the correct value and you see the database content with a kind of viewer/sql tool which has its own settings how to represent the date as text.

Re: Date format issuse in Excel file(xlsx)

Anonymous — Thu, 30 Jan 2014 07:16:28 GMT

Hi,
I am getting the issue in the Oracle Sql Developer database when I load the file
Also as I showed you that
the format of Col A is date and format of Col B is Text
A B
11/28/2011 11/28/2011
when I load the same in database I get the following output
Also I am reading both the columns as String
A B
Mon Nov 28 00:00:00 IST 2011 11/28/2011
so in col A the timestamp is attached and Col B is as it is
So what i am trying to say that if i am reading the date which is column A having date format in the input file and when read as string the output we get there is a time stamp attached and where as if i am reading the date which is column B having Text format,when it is read as string the output is retained as it is

Regards,
Ojas

Re: Date format issuse in Excel file(xlsx)

Anonymous — Thu, 30 Jan 2014 22:21:43 GMT

Yes I agree with you but it is a fact: A Date typed value will always displayed (nothing else) in the way the displaying tol is configured. If you see your long format, than because the function converting the Date into a text format use this pattern. The way to display the Date value has nothing to do with the storage in the database.
I am pretty sure the SQLDeveloper from Oracle can change its way to display the date values.
I understood in the database Col_A is of the type DATE and Col_B is of the type VARCHAR2 is this correct?
A DATE typed value will mostly shows differently as a VARCHAR2 value. You could setup the SQLDeveloper to show DATE value in the format MM/DD/YYYY but at the moment your SQLDeveloper has probably a different pattern for displaying DATE typed values.
Once again: DATE is a DATE and in Oracle it is actually a kind of a numeric value.

Re: Date format issuse in Excel file(xlsx)

Anonymous — Fri, 31 Jan 2014 11:58:12 GMT

hi ojasam_12,
The dates in excel cells that are of type 'date' are read in the format 'Mon Nov 28 00:00:00 IST 2011'. If your excel data type is general (for the date columns), this error doesn't come up.
But if you change the dates in date format directly to Generat format, some formula is applied and it returns a number.
If the date columns are general, like if you add a space after the date, it becomes general, but this is not feasible for large datasets.
If I get some workaround for this I will keep posted.

Re: Date format issuse in Excel file(xlsx)

Anonymous — Wed, 05 Feb 2014 07:58:07 GMT

Hi ojasam_12,
We can make talend read excel dates in our required formats by changing some settings while reading the Excel in tfileinputexcel. In the advanced settings of the component, check the option 'Convert Date column to String'. Then select the columns which are in date format in excel and specify the format in which you want them. This will make talend read the dates in string format and not like 'Mon Nov 28 00:00:00 IST 2011'.

Re: Date format issuse in Excel file(xlsx)

vidya821 — Wed, 17 Oct 2018 15:41:31 GMT

Hi Remytom,

Below makes Talend to read the data in date column as String in the specified format however when i am trying to store the same value in oracle table with the field as String, the value in oracle field is still shown in Date Time format as we have in xlsx file. Do you have any idea as how can i store the date value as String in oracle table ?

Thanks

Vidya