topic Re: Excel: Convert Double type into Date type in Talend Studio

Excel: Convert Double type into Date type

gt0731 — Sat, 16 Nov 2024 07:21:07 GMT

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);
        }
        
    }

Re: Excel: Convert Double type into Date type

Anonymous — Mon, 05 Nov 2018 11:19:11 GMT

Hello,

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

Re: Excel: Convert Double type into Date type

gt0731 — Mon, 05 Nov 2018 14:05:59 GMT

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]

Re: Excel: Convert Double type into Date type

gt0731 — Mon, 05 Nov 2018 14:13:07 GMT

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

Re: Excel: Convert Double type into Date type

Anonymous — Wed, 07 Nov 2018 10:44:34 GMT

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

Re: Excel: Convert Double type into Date type

gt0731 — Wed, 07 Nov 2018 11:29:19 GMT

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

For all the date getting the same date value

Re: Excel: Convert Double type into Date type

Anonymous — Wed, 07 Nov 2018 11:37:22 GMT

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.

Re: Excel: Convert Double type into Date type

gt0731 — Wed, 07 Nov 2018 11:41:35 GMT

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);

        }
        
    }

Re: Excel: Convert Double type into Date type

Anonymous — Wed, 07 Nov 2018 11:52:04 GMT

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-p/21665

Re: Excel: Convert Double type into Date type

Anonymous — Thu, 08 Nov 2018 06:20:27 GMT

Hello,

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

Best regards

Sabrina

Re: Excel: Convert Double type into Date type

gt0731 — Thu, 08 Nov 2018 07:07:35 GMT

We tried the same way as per above instruction


Getting this error 


org.talend.designer.runprocess.ProcessorException: Job compile errors 
At least job "DebugTime" has a compile errors, please fix and export again.
Error Line: 961
Detail Message: routines cannot be resolved
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.

	at org.talend.designer.runprocess.JobErrorsChecker.checkLastGenerationHasCompilationError(JobErrorsChecker.java:338)

	at org.talend.designer.runprocess.DefaultRunProcessService.checkLastGenerationHasCompilationError(DefaultRunProcessService.java:464)

	at org.talend.designer.runprocess.RunProcessService.checkLastGenerationHasCompilationError(RunProcessService.java:316)

	at org.talend.designer.runprocess.ProcessorUtilities.generateBuildInfo(ProcessorUtilities.java:812)

	at org.talend.designer.runprocess.ProcessorUtilities.generateCode(ProcessorUtilities.java:586)

	at org.talend.designer.runprocess.ProcessorUtilities.generateCode(ProcessorUtilities.java:1736)

	at org.talend.designer.runprocess.RunProcessContext$1.run(RunProcessContext.java:582)

	at org.eclipse.jface.operation.ModalContext.runInCurrentThread(ModalContext.java:466)

	at org.eclipse.jface.operation.ModalContext.run(ModalContext.java:374)

	at org.eclipse.jface.dialogs.ProgressMonitorDialog.run(ProgressMonitorDialog.java:527)

	at org.eclipse.ui.internal.progress.ProgressMonitorJobsDialog.run(ProgressMonitorJobsDialog.java:284)

	at org.eclipse.ui.internal.progress.ProgressManager.run(ProgressManager.java:1190)

	at org.talend.designer.runprocess.RunProcessContext.exec(RunProcessContext.java:534)

	at org.talend.designer.runprocess.ui.ProcessComposite.exec(ProcessComposite.java:1401)

	at org.talend.designer.runprocess.ui.views.ProcessView$RunAction.run(ProcessView.java:701)

	at org.talend.designer.runprocess.ui.actions.RunProcessAction.run(RunProcessAction.java:58)

	at org.talend.designer.core.debug.JobLaunchConfigurationDelegate$1.run(JobLaunchConfigurationDelegate.java:84)

	at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:35)

	at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:136)

	at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:4147)

	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3764)

	at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$9.run(PartRenderingEngine.java:1151)

	at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)

	at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1032)

	at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:148)

	at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:636)

	at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)

	at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:579)

	at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)

	at org.talend.rcp.intro.Application.start(Application.java:265)

	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)

	at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)

	at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)

	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:380)

	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:235)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Re: Excel: Convert Double type into Date type

gt0731 — Thu, 08 Nov 2018 07:09:12 GMT

Here is the code for routine.

package routines;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;

public class ConvertDoubleToDate {
	public static  void main(String[] args) throws InterruptedException {
	        Date date = new Date();
	        DateFormat format = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
	        format.setTimeZone(TimeZone.getTimeZone("Etc/UTC"));
	        String formatted = format.format(date);
	        System.out.println(formatted);
	        format.setTimeZone(TimeZone.getTimeZone("Australia/Sydney"));
	        formatted = format.format(date);
	        System.out.println(formatted);
	    }
	}

Re: Excel: Convert Double type into Date type

gt0731 — Thu, 08 Nov 2018 07:11:10 GMT

Here is detail :

Input date : double type looks like

|42888.6743287037 |
|42888.677453703705|
|42888.68003472222 |
|42891.37412037037 |
|42891.41011574074 |
|42891.41289351852 |
|42891.41289351852 |
|42891.41289351852 |
|42891.41289351852 |
|42891.41289351852 |
|42891.4190625

Output expected : date type
08-11-2018

Re: Excel: Convert Double type into Date type

Anonymous — Thu, 08 Nov 2018 08:11:40 GMT

Hello have you tried your code in a java compiler?

if it works check your jdk version

https://community.talend.com/t5/Installing-and-Upgrading/Configure-to-use-a-JVM/td-p/112893

Edit : I think there is problem with your data, it's not even a timestamp and i don't see how you will convert them into a datetime

Re: Excel: Convert Double type into Date type

gt0731 — Thu, 08 Nov 2018 09:19:43 GMT

I corrected the JVM issue but after ecxecuting routine . does not give me result.

i need to convert excel date (double type ) into date type in outout.

Please let me know if you have any java routine or expression for it

Re: Excel: Convert Double type into Date type

Anonymous — Thu, 08 Nov 2018 09:33:25 GMT

I don't think a kind of routine deals with your request as like i said, you double is not a timestamp.

I suggest you once again to check at your data source and i would try to modify and understand how are generated those doubles who are not timestamp values.

Unless your double is a special timestamp specific to your country.

The problem is not just how to convert your double to a date, we solved that. But now you need to find the mathematic solution to convert your double into a correct timestamp and then convert it to a format date with your routine / tjavarow.

Good luck