Identify and Eliminate non-numeric data while loading in to Target column

Highlighted
Six Stars

Identify and Eliminate non-numeric data while loading in to Target column

Hello All,

I have a scenario where Im loading data from a source column which is in STRING format and loading that to target column which is big decimal.

I am handling the string to bigdecimal conversion in TMAP.

2.PNG

I have 23 million records in total and talend job loads till 22 million records and errors out with below error.

1.PNG

 

Looks like my source column has data other than numeric characters and hence thats the reason for error. I deleted that one record from the source column but Im still facing the error.

3.PNG

Please help me how to handle this scenario. The data should be loaded in to target only if the data is numeric.

 

Thank You.


Accepted Solutions
Six Stars

Re: Identify and Eliminate non-numeric data while loading in to Target column

Thanks for the reply TRF.

I saw below reply of yours in a different thread.

1.PNG

 

Based on that, Can I use below code in my tmap?

row10.code.replaceAll("[+].*$", "").replaceAll("[A-Z]", "").replaceAll("[a-z]", "")

 

Please suggest.

 

Thanks


All Replies
Fifteen Stars TRF
Fifteen Stars

Re: Identify and Eliminate non-numeric data while loading in to Target column

You can validate the value with a regex for both concerned fields using a filter expression for the input flow in your tMap.
Search for "Java regex bigdecimal" to get regex examples.

TRF
Six Stars

Re: Identify and Eliminate non-numeric data while loading in to Target column

Thanks for the reply TRF.

I saw below reply of yours in a different thread.

1.PNG

 

Based on that, Can I use below code in my tmap?

row10.code.replaceAll("[+].*$", "").replaceAll("[A-Z]", "").replaceAll("[a-z]", "")

 

Please suggest.

 

Thanks

Fifteen Stars TRF
Fifteen Stars

Re: Identify and Eliminate non-numeric data while loading in to Target column

That will replace non-numeric character from input so it can help if you accept to change the initial value before to convert to bigdecimal. However if you want to exclude invalid values, you need a regex to ensure what you have is compatible with bigdecimal data type.
Something like the following should return true or false depending on what yourField is made of:
row1.yourField.toPlainString().matches("-?+\\d+(\\.0*)?")
This one can be used as a tMap filter expression.

TRF
Six Stars

Re: Identify and Eliminate non-numeric data while loading in to Target column

Thanks TRF. client was ok to replace non-numeric character from input data iteslf and hence followed the method you mentioned earlier.

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

Talend Cloud Developer Series – Defining Metadata

This video focuses on different methods of adding metadata to a job in Talend Cloud

Watch Now