topic Re: Error converting string to bigdecimal on tMap in Talend Studio

Error converting string to bigdecimal on tMap

Anonymous — Fri, 02 Mar 2018 05:35:24 GMT

Good Afternoon!

Just want to ask help from you guys, I'm trying to transfer data from delimited file to MySQL db. 3 of my columns has to be decimal so I tried using new BigDecimal(row.column) but I'm having error when I'm running the job. Thanks!

Re: Error converting string to bigdecimal on tMap

Anonymous — Fri, 02 Mar 2018 10:02:13 GMT

This question may have been overlooked as it probably is better suited to the "Design and Development" section.

It looks like your issues are caused by your number (I assume it is returned as a String from the file) being in a format that could not be converted. If a String representation of a number is empty ("") or has an extra space (" 34.8") or even has thousand separators ("10,000.00"), this error will be thrown. The first thing to try is to retrieve your data from the file in correct format. You should be able to adjust your schema for the file to set what it picks up as a BigDecimal. If this is not liked, then your data is in a bad format in the file. You need to figure out what is wrong in the file and then try to correct it before you convert it.

If it is a case of an extra space in the number (either side, like " 0.12" or "0.12 ") you can just add a trim to the column (row1.value.trim()).

If it is that there is a rogue comma (thousand separator in the UK), you can use a find and replace (row1.value.number.replaceAll(",", "")).

You will also need to check for null. You cannot convert null to a BigDecimal. So something like this....

row1.value!=null

But, a much better way of doing this (to cover most scenarios) would be to use the following code....

row1.value!=null ? new BigDecimal(row1.value.replaceAll("[^\\d.]", "")) : null

This checks for null and if it is not null, removes all bad characters and converts to a BigDecimal. If it is null, it returns null.

Re: Error converting string to bigdecimal on tMap

ThWabi — Fri, 02 Mar 2018 10:02:26 GMT

Hi pauldcop,

one of the input strings in row1.Value or row1.BasisValue or row1.Amount does not have the correct format to be converted to a BigDecimal. Please use this page https://docs.oracle.com/javase/7/docs/api/java/math/BigDecimal.html#BigDecimal(java.lang.String) (for example) as a reference of what an input string has to look like to be convertable to a BigDecimal value.

Best regards,

Thomas

Re: Error converting string to bigdecimal on tMap

Anonymous — Tue, 06 Mar 2018 08:28:31 GMT

Thanks rhall_2_0!

Re: Error converting string to bigdecimal on tMap

Anonymous — Tue, 06 Mar 2018 08:29:03 GMT

Thanks ThWabi!