Convert invalid values to int

Seven Stars

Convert invalid values to int

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

Eight Stars

Re: Convert invalid values to int

I use String Utils for this assuming your values positive.

 

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

Re: Convert invalid values to int

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

Eight Stars

Re: Convert invalid values to int

use tLibraryLoad commons-lang3-3.0

Seven Stars

Re: Convert invalid values to int

Thanks Evan - can you please provide an example.

 

Appreciate it.

Seven Stars

Re: Convert invalid values to int

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

Seven Stars

Re: Convert invalid values to int

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"

 

 

 

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog