Excel: Convert Double type into Date type

[gt0731]

Hi I am tyring to read  excel. In that,  following observations

I have date column in excel, but when I'm reading in my talend application I'm getting value as number

Example

Excel Date

1/1/2013 03:44:55 AM

Am getting as  means talend metadata reading it as double type 

41275.00

How to convert  double type to date type

We tried this java rountine but not able to call in  this job . 

package routines;



import java.text.SimpleDateFormat;
import java.util.Date;
public class DoubletoDatetype {

	public static void main(String[] args) {
		double myDouble = -242528463.775282;
		long myLong = System.currentTimeMillis() + ((long) (myDouble * 1000));
		System.out.println(myLong);

		Date itemDate = new Date(myLong);
		String myDateStr = new SimpleDateFormat("dd-MM-yyyy").format(itemDate);
		System.out.println(myDateStr);
        }
        
    }

[Anonymous]

Hello,

Can't you just read the date column as a string object and then use TalendDate.parseDate(String pattern, String Stringdate) ?

[gt0731]

When i am trying as mentioned by you
Getting this error
Starting job DebugTime at 19:34 05/11/2018.

[statistics] connecting to socket on port 4074
[statistics] connected
Exception in component tMap_1 (DebugTime)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "42933.67752314815"
at routines.TalendDate.parseDate(TalendDate.java:898)
at routines.TalendDate.parseDate(TalendDate.java:842)
at frog_demo.debugtime_0_1.DebugTime.tFileInputExcel_1Process(DebugTime.java:978)
at frog_demo.debugtime_0_1.DebugTime.runJobInTOS(DebugTime.java:1436)
at frog_demo.debugtime_0_1.DebugTime.main(DebugTime.java:1285)
Caused by: java.text.ParseException: Unparseable date: "42933.67752314815"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:884)
... 4 more
[statistics] disconnected

Job DebugTime ended at 19:34 05/11/2018. [exit code=1]

[gt0731]

We  created following java routine to convert  double type to date type 

package routines;



import java.text.SimpleDateFormat;
import java.util.Date;
public class DoubletoDatetype {

	public static void main(String[] args) {
		double myDouble = 42933.67752314815;
		long myLong = System.currentTimeMillis() + ((long) (myDouble * 1000));
		System.out.println(myLong);

		Date itemDate = new Date(myLong);
		String myDateStr = new SimpleDateFormat("dd-MM-yyyy").format(itemDate);
		System.out.println(myDateStr);
        }
        
    }

Please help how do i call in job this routine because this routine works perfectly to convert  double into date type 

This routine is not visible in expression box.

Any help on it would be much appreciated ?

Any help on 

[Anonymous]

Hello,

You can try this, atleast there the convertion works but i think you have a problem with the data source wich is not a timestamp. I have never seen a date type like this before.

testdate_0.1.zip

[gt0731]

When i exported this job getting this date format for all input 

For all the date getting the same date value

[Anonymous]

yes because your values in your excel file are not a timestamp, you need to find a solution to transform your double into a real timestamp to convert correctly to a date format.

[gt0731]

Here i tried  the java code 

How do i use in the tjavarow or  is it possible to create as routine 

https://www.tutorialspoint.com/compile_java_online.php

import java.text.SimpleDateFormat;
import java.util.Date;
public class DoubletoDatetype {

	public static void main(String[] args) {
		double myDouble = 42933.67752314815;
		long myLong = System.currentTimeMillis() + ((long) (myDouble * 1000));
// 		System.out.println(myLong);
		
		Date itemDate = new Date(myLong);
		String myDateStr = new SimpleDateFormat("dd-MM-yyyy").format(itemDate);
		System.out.println(myDateStr);

        }
        
    }

[Anonymous]

Yes you will need to create a routine.

Here is a tutorial to create a routine: https://community.talend.com/t5/Design-and-Development/Create-a-user-routine-and-call-it-in-a-Job/ta...

[Anonymous]

Hello,

Talend Help Center documentation:https://help.talend.com/reader/ib1101ybXTsSbZNEFaZ4Dw/~7p0MsFkjcWbQ_1PHB98WQ

Best regards

Sabrina