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
Highlighted
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

View solution in original post


All Replies
Highlighted
Sixteen Stars
Sixteen 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
Highlighted
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

View solution in original post

Highlighted
Sixteen Stars
Sixteen 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
Highlighted
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 GARTNER 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

Talend Cloud Developer Series – Updating Context Variables

This video will show you how to add context parameters to a job in Talend Cloud

Watch Now

Talend Cloud Developer Series – Deploying First Job to Cloud

This video will show you how to run a job in Studio and then publish that job to Talend Cloud

Watch Now

Talend Cloud Developer Series – Fetching Studio License

This video will help someone new to using Talend Studio get started by connecting to Talend Cloud and fetching the Studio License

Watch Now