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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

or 

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

[Anonymous · Accepted solution]

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