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

Seven Stars

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

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

 

Tags (1)
Forteen Stars TRF
Forteen Stars

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

Hi,

Did you try this:

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

TRF
Sixteen Stars

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

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);
Seven Stars

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

@rhall_2_0

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

Capture.PNG

 

I did the job like that : tmap.PNG

 

tmap1.PNG

 

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

 

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

tlog.PNG

 out.PNGit always is in String (STANDARD in Excel...)

 

I hope that it will help you to find why

Seven Stars

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

@TRF

I also tried that and nothing

Twelve Stars

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

@Beauchamp_J,Are you looking fro the following?

 

Untitled.pngUntitled.png

Manohar B
Sixteen Stars

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

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

Seven Stars

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

@rhall_2_0

It doesn't work at all.

Capture.PNGtmap.PNG@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) 

Twelve Stars

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

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

Manohar B
Sixteen Stars

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

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

Seven Stars

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

@rhall_2_0

@manodwhb

@TRF

 

No other idea?

Twelve Stars

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

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

Manohar B
Seven Stars

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

@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

Twelve Stars

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

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

 

Untitled.pngUntitled.png

Manohar B
Seven Stars

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

@rhall_2_0

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

 

 

Seven Stars

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

@rhall_2_0

@manodwhb

 

The file that i shared is impossible to resolve?

 

Thank you in advance

Sixteen Stars

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

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.

Seven Stars

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

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

erreur.PNG

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)

Sixteen Stars

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

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.

Seven Stars

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

@rhall_2_0

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.

Seven Stars

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

Well, it works in the v7 and so, i again run with the file which i work above (Not the one that I made for you). It doesn't work.

 

I think that when I received the file, it was in format xlsb and i save the file in the format xlsx because Talend can't read the format xlsb. It maybe broke the file.

But i don't understand why this line doesn't work and the others lines work very well. 

Sixteen Stars

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

The numberformatexception is because (I'm guessing) of the comma. Your environment settings probably have the decimal separator set to ".". As such, a comma "," as the separator will cause this. It's a pretty common error to come across when you are converting from a String to a number and your system is not expecting (for example) French number formatting in English.

Sixteen Stars

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

The line does not work because you are trying to remove characters that you do not know of. The best way to work this out is to use a tool to identify the characters and then remove those. I think you may be right with your assumption about the conversion of an xlsb to xlsx. Unfortunately, I cannot help any more than this unless I can recreate the issue. Everything I have tried has worked. 

I don't believe this is a Talend issue though, it is just simple Java. As I said, I think this is a case of just not knowing the characters that have been added to the number.

Seven Stars

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

Thank you for your answers. I am going to put aside this line, I shall speak with my colleague when he will come back from his holidays. Maybe, he will know how to identify the characters and then remove those.

Sixteen Stars

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

Try this site http://www.babelstone.co.uk/Unicode/whatisit.html

Just copy and paste from your Excel document into this tool and it will tell you.

Seven Stars

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

I entried 1346416 and i have : 

U+0031 : DIGIT ONE
U+0020 : SPACE [SP]
U+0033 : DIGIT THREE
U+0034 : DIGIT FOUR
U+0036 : DIGIT SIX
U+0020 : SPACE [SP]
U+0034 : DIGIT FOUR
U+0031 : DIGIT ONE
U+0037 : DIGIT SEVEN
Sixteen Stars

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

If that is the case, something is broken. Do you have another machine to test your job on? 

Seven Stars

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

No, unfortunately, I have no other available machine but i am going to reflect to find another machin to test this job