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

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

DEV4 — Sat, 16 Nov 2024 04:30:22 GMT

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.

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

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.

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

Thank You.

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

TRF — Tue, 01 Oct 2019 20:57:07 GMT

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.

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

DEV4 — Tue, 01 Oct 2019 21:47:12 GMT

Thanks for the reply TRF.

I saw below reply of yours in a different thread.

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

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

Please suggest.

Thanks

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

TRF — Tue, 01 Oct 2019 22:05:06 GMT

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.

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

DEV4 — Wed, 02 Oct 2019 17:57:01 GMT

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