Exception in component tFileInputExcel_1 (DIJob_ADD_CA_Load_Update) java.lang.NumberFormatException: For input string

[delite]

Hi, 

I am quite new to Talend, I got the error below while trying to push some data from a 2003 excel file to an oracle table;

From my observation, the issue seems to stem from a number field that has a comma in between the digits (ie thousand separator). I mapped the source file input for this specific number as a float and also same as the destination field in the oracle table.

When I manually format the fields to number format in the excel file it fixes this issue, how can I automate this process to format the excel file and remove the ","? 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Exception in component tFileInputExcel_1 (DIJob_ADD_CA_Load_Update)

java.lang.NumberFormatException: For input string: "3,618.90"

at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2043)

at sun.misc.FloatingDecimal.parseFloat(FloatingDecimal.java:122)

at java.lang.Float.parseFloat(Float.java:451)

at routines.system.ParserUtils.parseTo_float(ParserUtils.java:111)

at routines.system.ParserUtils.parseTo_Float(ParserUtils.java:118)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileInputExcel_1Process(DIJob_ADD_CA_Load_Update.java:14152)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileExist_4Process(DIJob_ADD_CA_Load_Update.java:11542)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileExist_3Process(DIJob_ADD_CA_Load_Update.java:6321)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileExist_2Process(DIJob_ADD_CA_Load_Update.java:3901)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileExist_1Process(DIJob_ADD_CA_Load_Update.java:1185)

[FATAL]: etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update - tFileInputExcel_1 For input string: "3,618.90"

java.lang.NumberFormatException: For input string: "3,618.90"

at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2043)

at sun.misc.FloatingDecimal.parseFloat(FloatingDecimal.java:122)

at java.lang.Float.parseFloat(Float.java:451)

at routines.system.ParserUtils.parseTo_float(ParserUtils.java:111)

at routines.system.ParserUtils.parseTo_Float(ParserUtils.java:118)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileInputExcel_1Process(DIJob_ADD_CA_Load_Update.java:14152)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileExist_4Process(DIJob_ADD_CA_Load_Update.java:11542)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileExist_3Process(DIJob_ADD_CA_Load_Update.java:6321)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileExist_2Process(DIJob_ADD_CA_Load_Update.java:3901)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.tFileExist_1Process(DIJob_ADD_CA_Load_Update.java:1185)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.runJobInTOS(DIJob_ADD_CA_Load_Update.java:16735)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.main(DIJob_ADD_CA_Load_Update.java:16347)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.runJobInTOS(DIJob_ADD_CA_Load_Update.java:16735)

at etl.dijob_add_ca_load_update_1_6.DIJob_ADD_CA_Load_Update.main(DIJob_ADD_CA_Load_Update.java:16347)

[statistics] disconnected

Job DIJob_ADD_CA_Load_Update ended at 09:06 23/07/2020. [exit code=1]

[Anonymous]

Hello,

Please try to read the data as string from Excel file, and then remove the comma on tJavaRow or tMap, for example,

on tJavaRow:

output_row.columnName=input_row.columnName.replaceAll(",","");

Link tJavaRow to tConvertType to convert the data type from string to float.

Let us know if it works.

Best regards

Sabrina