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

[Anonymous]

@rhall

@manodwhb

@TRF

No other idea?

[manodwhb]

@Beauchamp_J, can you able to share the source file?

[Anonymous]

@manodwhb

I can't share because my file input is confidential. I masked the other lines to make the screen shot and modify a little and show it to you only the line which raised problem

[manodwhb]

@Beauchamp_J,since i was able to test with below way. i am attaching the sorce file too please check.

Test_data.xlsx

[Anonymous]

@rhall

@manodwhb

I have done a new file with this, i just modify name, number to keep confidential. It stays the same problem.

You can have a look in my file and maybe you can explain that?

Test.xlsx

[Anonymous]

@rhall

@manodwhb

The file that i shared is impossible to resolve?

Thank you in advance

[Anonymous]

First of all, your XLS file is massive for what it is. I'd advise copying the few meaningful rows into a new file. I did that and got it down to 11KB.

When I processed the new file (exactly the same details and equations), I got this output....

Starting job Child at 15:53 04/05/2018.

[statistics] connecting to socket on port 4067
[statistics] connected
E:\Talend\6.2.1\esb\container\etc\env.properties
Project ID|Customer Name|EXTERNAL
1|Name|419029.32
2|Name_1|687872.5800000002
3|Name_2|31252.5
4|Name_3|30350.909999999996
5|Name|42607.259999999995
6|Name_4|0
7|Name_5|8857
8|Name_6|14541.2
9|Name|0
10|Name_7|20495
11|Name_8|8075
12|Name_9|8230.07
13|Name|7800
14|Name_10|7410
15|Name_11|7030
16|Name_12|0
17|Name|6105
18|Name_13|7312.5
19|Name_14|7020
20|Name_15|6840
21|Name|6100
22|Name_16|7220
23|Name_17|3960
24|Name_18|0
25|Name|3808.3
26|Name_19|0
27|Name_20|4500
28|Name_21|0
29|Name|0
30|Name_22|
NET REV||1346416.64

I simply read using a tFileInputExcel and output using a tLogRow.

I suspect your Excel file is broken and this has led to issues with the job. a 32 row x 3 column Excel file should not be 14MB. As I said, copy the data out and paste it into a new file and see what the job does that with that.

[Anonymous]

I pasted the rows in a new file (11Ko also) and i run the job.

I put "Double" in column EXTERNAL in tMap. I got this output log.

Démarrage du job Groupement_Clients a 17:11 04/05/2018.
[statistics] connecting to socket on port 3335
[statistics] connected
For input string: "1 346 417"
1|Name|419029.32
2|Name_1|687872.5800000002
3|Name_2|31252.5
4|Name_3|30350.909999999996
5|Name|42607.259999999995
6|Name_4|0.0
7|Name_5|8857.0
8|Name_6|14541.2
9|Name|0.0
10|Name_7|20495.0
11|Name_8|8075.0
12|Name_9|8230.07
13|Name|7800.0
14|Name_10|7410.0
15|Name_11|7030.0
16|Name_12|0.0
17|Name|6105.0
18|Name_13|7312.5
19|Name_14|7020.0
20|Name_15|6840.0
21|Name|6100.0
22|Name_16|7220.0
23|Name_17|3960.0
24|Name_18|0.0
25|Name|3808.3
26|Name_19|0.0
27|Name_20|4500.0
28|Name_21|0.0
29|Name|0.0
30|Name_22|
[statistics] disconnected
Job Groupement_Clients terminé à 17:11 04/05/2018. [Code sortie=0]

That is my log console...

(For your next answers, I shall read them next Monday, I am going to leave for weekend. Thank you for the patience, and I hope that we shall eventually find the problem)

[Anonymous]

I've built a job like yours in v6.2.1. I've included it here. Try it in v7. It works absolutely perfectly in v6.2.1 with your Excel file. If it fails for you, it might be a Talend bug in v7.

ExcelJob.zip

[Anonymous]

@rhall

I use your job without modifying it in v6.5.1 and i have a error.

Démarrage du job ExcelTest a 09:44 07/05/2018.
[statistics] connecting to socket on port 3693
[statistics] connected
Exception in component tMap_2 (ExcelTest)
java.lang.NumberFormatException: For input string: "419029,32"
	at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source)
	at sun.misc.FloatingDecimal.parseDouble(Unknown Source)
	at java.lang.Double.parseDouble(Unknown Source)
	at talend_demo.exceltest_0_1.ExcelTest.tFileInputExcel_1Process(ExcelTest.java:1099)
	at talend_demo.exceltest_0_1.ExcelTest.runJobInTOS(ExcelTest.java:1524)
	at talend_demo.exceltest_0_1.ExcelTest.main(ExcelTest.java:1367)
[statistics] disconnected
Job ExcelTest terminé à 09:44 07/05/2018. [Code sortie=1]

I will try in v7. I download it at the moment.