No way to convert String to Double in a column (in Excel : this column has : SUM) Talend refuse

[Anonymous]

Hello

Sorry, i come again back. I am going to try to explain clearly my problem. 

I have a column in Excel : =+S3+N3+I3+X3+AC3   ==> 1 530 618 (for first line )

Same thing for the following lines with the sum.

I must get back this column in Talend. So, i put this as Double (or Long but it stays the same) in input and output in tMap.(and tLogRow for check if all is going)

But Talend refuse and said that it is in String.

Démarrage du job Test_avec_D a 17:02 17/05/2018.
[statistics] connecting to socket on port 3384
[statistics] connected
For input string: "1 197 140"
For input string: "164 622"
For input string: "142 676"
For input string: "127 963"
For input string: "103 405"
For input string: "98 377"
For input string: "72 706"
For input string: "61 934"
For input string: "60 546"
For input string: "38 463"
For input string: "36 280"
For input string: "36 075"
For input string: "35 515"
For input string: "34 780"
For input string: "33 914"
For input string: "33 670"
For input string: "33 188"
For input string: "32 220"
For input string: "31 680"
For input string: "31 300"
For input string: "30 590"
For input string: "30 060"
For input string: "19 800"
For input string: "19 042"
For input string: "1 645"
.-------------------+----------+------------------.
|                    tLogRow_2                    |
|=------------------+----------+-----------------=|
|Customer_Name      |DELIVERED5|marge_Pourcentage5|
|=------------------+----------+-----------------=|
|BANK  |0         |ERROR:#DIV/0!     |
|BANK1|0         |ERROR:#DIV/0!     |
| BANK GROUP        |0         |ERROR:#DIV/0!     |
|BANK FED|0         |ERROR:#DIV/0!     |
'-------------------+----------+------------------'

[statistics] disconnected

So, I did "Double.parseDouble(row1.Example)" or "Long.parseLong(row1.Example)"

Exception in component tMap_2 (Test_avec_D)
java.lang.NumberFormatException: For input string: "1 197 140"
       at java.lang.NumberFormatException.forInputString(Unknown Source)
       at java.lang.Long.parseLong(Unknown Source)
       at java.lang.Long.parseLong(Unknown Source)
       at talend_demo.test_avec_delivered_0_1.TEst_avec_delivered.tFileInputExcel_1Process(TEst_avec_delivered.java:1311)
       at talend_demo.test_avec_delivered_0_1.TEst_avec_delivered.runJobInTOS(TEst_avec_delivered.java:1839)
       at talend_demo.test_avec_delivered_0_1.TEst_avec_delivered.main(TEst_avec_delivered.java:1688)

So.....I talk with my colleague and he doesn't know how to resolve that. I need this column for Excel Output. 

[manodwhb]

First you need to replace space wih empty for that column using row1.column.replaceAll(" ","") and then convert double or long,which you want.

[Anonymous]

Yes, i already tried that but it doesn't work.

tReplace : No deletion. That remains the same

row1.Example.replaceall(" ", "") : No replace. That remains the same. No error but nothing happens

StringHandling.EREPLACe(row1.Example. " ", "") : idem

row1.Example.replaceAll("\\s+","") : idem

No error. 

-------------------------------+----------.
| tLogRow_1 |
|=------------------------------+----------=|
|Customer_Name |EXAMPLE|
|=------------------------------+----------=|
|Bank |1 530 618 |
|Bank1 |1 197 140  |
|Bank GROUP |164 622  |
|BANK FED |142 676 |
...
'-------------------------------+----------+'

Spaces always are here

[vboppudi]

Hi,

Are you looking for the following output? try below tMap configurations

Regards,

[manodwhb]

Can you check thousand separator is used as space in Advance settings of tFileInputExcel?
If yes make it to empty.

[PaulyWally]

These work fine:

[manodwhb]

@Beauchamp_J,still if you have issue? can you post you job design?

[Anonymous]

Sorry, yesterday evening, i finished my day. 

So, i did screenshot. 

@vboppudi 

@PaulyWally

@manodwhb

I checked Advanced setting of the fileInputExcel: It was not marked but i marked to show you.

And i did what you showed me for tMap and Var.

There are errors...

[manodwhb]

@Beauchamp_J,is it possiable to share the sample excel file?

[Anonymous]

Or with a tJavaRow? I am not good in Java but i try. I think do as that:

Or I try to delete spaces or I get back only figures in my chain with a tJavaRow.

I tried but it doesn't work. 

@manodwhb@PaulyWally@vboppudi

@rhall

import java.util.regex.Matcher
import java.util.regex.Pattern
Pattern chercherChiffres = Pattern.compile("\\d+");
Matcher ChiffreRecuperee = chercherChiffres(input_row.DELIVERED5);
while (ChiffreRecuperee.find())