Convert invalid values to int

talendstar — Sat, 16 Nov 2024 07:29:45 GMT

Hi there,

I am trying to load a table from a csv file. However in one specific column in the csv file sometimes instead of a number there are values like 'NA' or 'Not Available'.

I am using tMap to map the delimited file to Snowflake. The destination column is of number data type.

How can I convert the invalid values like NA or Not Available to an int like 999 so that the load go through successfully.

Thanks

Re: Convert invalid values to int

Anonymous — Tue, 09 Oct 2018 20:53:35 GMT

I use String Utils for this assuming your values positive.

StringUtils.isNumeric(row1.data) ? row1.data : 999

Re: Convert invalid values to int

talendstar — Tue, 09 Oct 2018 20:56:53 GMT

how do I use this? I don't see this in the expression editor.

Re: Convert invalid values to int

Anonymous — Tue, 09 Oct 2018 21:05:26 GMT

use tLibraryLoad commons-lang3-3.0

Re: Convert invalid values to int

talendstar — Tue, 09 Oct 2018 21:17:02 GMT

Thanks Evan - can you please provide an example.

Appreciate it.

Re: Convert invalid values to int

talendstar — Tue, 09 Oct 2018 21:41:08 GMT

dropped tLibraryLoad onto the workflow and connected it. Modified the tMap for required field with following code:

StringUtils.isNumeric(row1.OWNER_PH) ? row1.OWNER_PH : 999

but getting an error when running the job:

At least job "TstLoad" has a compile errors, please fix and export again.
Error Line: 39
Detail Message: The import org.apache.commons.lang 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:328)

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

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

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

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

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

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

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:532)

at org.talend.designer.runprocess.remote.RunRemoteProcessContext.exec(RunRemoteProcessContext.java:86)

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

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

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:255)

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)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:648)

at org.eclipse.equinox.launcher.Main.basicRun(Main.java:603)

at org.eclipse.equinox.launcher.Main.run(Main.java:1465)

tLibraryLoad is setup as following:

on Basic Setting table library is set to commons-lang3-3.0.jar

on the Advanced tab I am importing StringUtils: import org.apache.commons.lang.StringUtils;

Any thoughts on what could be going wrong here?

Thanks

Re: Convert invalid values to int

talendstar — Tue, 09 Oct 2018 22:39:07 GMT

modified the import statement to following:

import org.apache.commons.lang3.StringUtils;

But no luck

I am using following code:

!StringUtils.isNumeric(row1.OWNER_PH)?row1.OWNER_PH:"999"

topic Re: Convert invalid values to int in Talend Studio

Convert invalid values to int

Re: Convert invalid values to int

Re: Convert invalid values to int

Re: Convert invalid values to int

Re: Convert invalid values to int

Re: Convert invalid values to int

Re: Convert invalid values to int