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

[DEV4]

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.

[TRF]

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.

[DEV4 · Accepted solution]

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

[TRF]

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.

[DEV4]

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