topic Re: [resolved] Exception in component tMap_1 java.text.ParseException Unparseable num in Talend Studio

[resolved] Exception in component tMap_1 java.text.ParseException Unparseable num

Anonymous — Fri, 01 Aug 2014 09:29:28 GMT

Dear All,
I try to import data from flat file (csv) to Oracle 10g Database with talend ETL tool (Version 5.0 )
In the file that we recieved from the department we have American number format: 1.000,25
I use this expression in the tMap component to convert US format to German number format accrdoing to the database NLS parameters:
java.text.NumberFormat.getNumberInstance(java.util.Locale.US).parse(row1.SALES_VALUE_LOC).doubleValue()
It is working fine but we have a problem with empty fields.
The problem is occurs if the "SALES_VALUE_LOC" does not hold any value. Then java can not handle the empty field and interrupts the data import.
Exception in component tMap_1
java.text.ParseException: Unparseable number: ""
   at java.text.NumberFormat.parse(Unknown Source)
   at dwhggbnew.string_staging_sales_cn1_a_0_1.String_Staging_Sales_CN1_a.tFileInputDelimited_1Process(String_Staging_Sales_CN1_a.java:1743)
   at dwhggbnew.string_staging_sales_cn1_a_0_1.String_Staging_Sales_CN1_a.tOracleRow_1Process(String_Staging_Sales_CN1_a.java:642)
   at dwhggbnew.string_staging_sales_cn1_a_0_1.String_Staging_Sales_CN1_a.tFileList_1Process(String_Staging_Sales_CN1_a.java:498)
   at dwhggbnew.string_staging_sales_cn1_a_0_1.String_Staging_Sales_CN1_a.runJobInTOS(String_Staging_Sales_CN1_a.java:3034)
   at dwhggbnew.string_staging_sales_cn1_a_0_1.String_Staging_Sales_CN1_a.main(String_Staging_Sales_CN1_a.java:2902)
disconnected
Question:
Frequently in the file the number fields are null. How can I handle the null value with the method parse() in this scase.
I need something like:
if ( row1.SALES_VALUE_LOC != null)
{ java.text.NumberFormat.getNumberInstance(java.util.Locale.US).parse(row1.SALES_VALUE_LOC).doubleValue();
}
else{
row1.SALES_VALUE_LOC;
}
But this expression does not work if I inserted it in the tMap compnent.
Any support is appreciated.
Thank you!
Best Regards,
Robert

Re: [resolved] Exception in component tMap_1 java.text.ParseException Unparseable num

Anonymous — Fri, 01 Aug 2014 09:37:24 GMT

Hi Robert,
There is a function Relational.NULL(your.value), have you tested this...
Relational.NULL(your.value)?some dummy value:your.value
Similar logic you can implement for empty condition as well.
Try this.
Vaibhav

Re: [resolved] Exception in component tMap_1 java.text.ParseException Unparseable num

Anonymous — Fri, 01 Aug 2014 09:57:15 GMT

you can use same expression in tMAP like below.

row1.SALES_VALUE_LOC != null &&  row1.SALES_VALUE_LOC !="" ? java.text.NumberFormat.getNumberInstance(java.util.Locale.US).parse(row1.SALES_VALUE_LOC).doubleValue(): row1.SALES_VALUE_LOC

Re: [resolved] Exception in component tMap_1 java.text.ParseException Unparseable num

Anonymous — Fri, 01 Aug 2014 11:42:15 GMT

Hi Umeshrakhe,
I would go with your solution my expression is:
row1.SALES_QTY != null || row1.SALES_QTY !="" ? java.text.NumberFormat.getNumberInstance(java.util.Locale.GERMAN).parse(row1.SALES_QTY).doubleValue(): null
I used "or" --> || operator and finally in would return null.
But still geting the error if I have empty field in the csv file.
FYI I convert string to double see my screen shots.

Exception in component tMap_1
java.text.ParseException: Unparseable number: ""
    at java.text.NumberFormat.parse(Unknown Source)
    at dwhggbnew.copy_of_string_staging_sales_cn1_a_0_1.Copy_of_String_Staging_Sales_CN1_a.tFileInputDelimited_1Process(Copy_of_String_Staging_Sales_CN1_a.java:1744)
    at dwhggbnew.copy_of_string_staging_sales_cn1_a_0_1.Copy_of_String_Staging_Sales_CN1_a.tOracleRow_1Process(Copy_of_String_Staging_Sales_CN1_a.java:642)
    at dwhggbnew.copy_of_string_staging_sales_cn1_a_0_1.Copy_of_String_Staging_Sales_CN1_a.tFileList_1Process(Copy_of_String_Staging_Sales_CN1_a.java:498)
    at dwhggbnew.copy_of_string_staging_sales_cn1_a_0_1.Copy_of_String_Staging_Sales_CN1_a.runJobInTOS(Copy_of_String_Staging_Sales_CN1_a.java:3046)
    at dwhggbnew.copy_of_string_staging_sales_cn1_a_0_1.Copy_of_String_Staging_Sales_CN1_a.main(Copy_of_String_Staging_Sales_CN1_a.java:2914)
Where do I make mistake?
Best Regards,
Robert

Re: [resolved] Exception in component tMap_1 java.text.ParseException Unparseable num

Anonymous — Fri, 01 Aug 2014 12:16:26 GMT

It is not easy to trap Empty value you have to do trick like below. please try once.

row1.SALES_QTY != null|| row1.SALES_QTY !="" ? java.text.NumberFormat.getNumberInstance(java.util.Locale.GERMAN).parse(row1.SALES_QTY.trim()).doubleValue(): null

row1.SALES_QTY != null|| row1.SALES_QTY.trim() !="" ? java.text.NumberFormat.getNumberInstance(java.util.Locale.GERMAN).parse(row1.SALES_QTY).doubleValue(): null

Re: [resolved] Exception in component tMap_1 java.text.ParseException Unparseable num

Anonymous — Thu, 14 Aug 2014 12:06:46 GMT

Dear All
the following solution worked for me:
row1.Column0.equals("") ? null : java.text.NumberFormat.getNumberInstance(java.util.Locale.US).parse(row1.Column0.trim()).doubleValue()
Thank you!
Best Regards,
Robert