tMap don't recognize space in number and don't delete that

[Anonymous]

Hello

In tFileInputExcel, there is a cell which has a number : 1 543 982

This number was calculated with a formula in Excel : =SUB.TOTAL(7;AB3:AB32) and we read : 1 543 982 (Number) in in Excel

So, Talend considers 1 543 982 as a String and not a number.

I tried delete space but tMap doesn't recognize the space in this "String" : 1 543 982.

So, i tried with : 

StringHandling.EREPLACE(row1.Number," ","")  in tMap but it doesn't work.

Also : 

(!Relational.ISNULL(row1.Number))?row1.Number.replace(" ",""):row1.Number

It doesn't work at all.

Double.parse(row1.Number)

==> Error : Exception in component tMap_1 (Marge_NET)
java.lang.NumberFormatException: For input string: "1 346 417"

I also tried with tReplace and nothing.

I don't understand why it doesn't work...

[TRF]

Hi,

Did you try this:

row1.Number.replaceAll(" ", "")

[Anonymous]

I think you are leaving something important out of your description. Copy and paste the following code into a tJava and run the job and you will see that each of the pieces of code which failed for you, do work. It suggests that the error lies somewhere else. Can you post a screenshot of your job, your input schema (from Excel) and your tMap?

String num = "1 543 982";
System.out.println("Original number: "+num);
num = StringHandling.EREPLACE(num, " ", "");
System.out.println("Number with spaces removed: "+num);
double dblNum = Double.parseDouble(num);
System.out.println("No double conversion error: "+ dblNum);

[Anonymous]

@rhall

this is the Excel Input : (NUMBER and not Standard)

I did the job like that : 

I juste write "row1.EXTERNAL" and keep in String. 

I copy your code in tJava and i have in console : 

 it always is in String (STANDARD in Excel...)

I hope that it will help you to find why

[Anonymous]

@TRF

I also tried that and nothing

[manodwhb]

@Beauchamp_J,Are you looking fro the following?

[Anonymous]

In your tMap try this...

row1.EXTERNAL4.replaceAll("\\s+","")

Let us know what happens. As shown by your running the code I sent you, everything you have tried should have worked IF you are getting the number formatted as you expected. I suspect you might have tabs or other "space" characters in your String. The above should solve that

[Anonymous]

@rhall

It doesn't work at all.

@manodwhb

I wrote that in tMap and i put Double in type :

Double.parseDouble(row1.EXTERNAL4.replaceAll("\\s+","")) 

I had a error in log : (same thing if i did that with float)

Exception in component tMap_1 (Marge_NET)
java.lang.NumberFormatException: For input string: "1 346 417"
at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source)
at sun.misc.FloatingDecimal.parseDouble(Unknown Source)
at java.lang.Double.parseDouble(Unknown Source)
at talend_demo.marge_net_0_1.Marge_NET.tFileInputExcel_1Process(Marge_NET.java:2200)
at talend_demo.marge_net_0_1.Marge_NET.runJobInTOS(Marge_NET.java:2678)
at talend_demo.marge_net_0_1.Marge_NET.main(Marge_NET.java:2527) 

[manodwhb]

@Beauchamp_J, can you try the below way

Double.parseDouble(ow1.EXTERNAL4.replaceAll(" ", ""))

other wise can you share your smaple excel file to test for you,since "1 346 417"data i have able to achive the required one you can find this in my previous replay.

[Anonymous]

My code covers that @manodwhb. It didn't work.