Convert invalid values to int

[talendstar]

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

[Anonymous]

I use String Utils for this assuming your values positive.

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

[talendstar]

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

[Anonymous]

use tLibraryLoad commons-lang3-3.0

[talendstar]

Thanks Evan - can you please provide an example.

Appreciate it.

[talendstar]

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

[talendstar]

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"