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

Highlighted
Seven Stars

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

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. 

 

 


Accepted Solutions
Seven Stars

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

 Yes, i found a solution but not with Talend. In Excel, i should deselect thousand separator in Number format in the column which raised problem. And after no more spaces. In Talend, I was able to put in double.

 

But in other column in Excel, there are thousand separator with spaces and it works very well except this column. It stays mysterious


All Replies
Thirteen Stars

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

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

Manohar B
Seven Stars

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

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

Nine Stars

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

Hi,

 

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

StringtoDouble.PNGStringtoDouble1.PNG

Regards,

 

Veeru Boppudi
Thirteen Stars

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

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

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

These work fine:


2018-05-17 10_58_32.png

2018-05-17 10_59_11.png

Thirteen Stars

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

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

Manohar B
Seven Stars

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

Sorry, yesterday evening, i finished my day. 

So, i did screenshot. 

tmap.PNGerreur1.PNGsettings.PNG

 

 

@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...

Thirteen Stars

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

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

Manohar B
Seven Stars

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

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_2_0

 

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

 

Sixteen Stars

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

The reason you are getting this is because your Excel number configurations do not match your system ones. The easiest way to resolve this is to ensure that your Excel file is set to use the System number formats. This is similar to the problem you had before.

Seven Stars

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

@manodwhb

 

If that helps you...

 

I want Customer Name & DELIVERED_YEAR (with sum in formula Excel). The other DELIVERED works very well , without spaces in Talend. 

Seven Stars

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

@rhall_2_0

Well, I check Excel file, and in settings Number, there is "thousand separator" which is marked. 

I delete this and no more spaces after.

On talend, it works finally. Only on this column, and the other column have marked thousand separator on Excel and Talend can read that with no matter at all.

It stays mysterious for me....

Thirteen Stars

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

@Beauchamp_J,in the Excel file it has Floating data types. Please check.

 

your excel data below.

Untitled.png

Manohar B
Seven Stars

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

@manodwhb

 

Capture.PNG

 

(Infos : i put in Talend for tfileInputExcel Mode : Not much memory consumed for a big file because my Excel file is big (many data with many sheets)

Thirteen Stars

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

@Beauchamp_J,since i do not find problem with the file which you shared.

 

You have issue with acthually file which you were using.

Manohar B
Seven Stars

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

 Yes, i found a solution but not with Talend. In Excel, i should deselect thousand separator in Number format in the column which raised problem. And after no more spaces. In Talend, I was able to put in double.

 

But in other column in Excel, there are thousand separator with spaces and it works very well except this column. It stays mysterious

Thirteen Stars

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

@Beauchamp_J,since it is problem with format of Excelfile,not with Talend.

Manohar B
Thirteen Stars

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

@Beauchamp_J,please mark this case as a sloved.

Manohar B