Error with Format Pourcentage from Excel to Number in new Excel

Seven Stars

Error with Format Pourcentage from Excel to Number in new Excel

Hello, 

 

I have an error that i don't unterstand : 

I did : a fileInputExcel => tMap => tfileOutputExcel.

I wanted retrieve a column with pourcentages (with a single line to unterstand the error : header : 5 Limit : 1)

FormatPourcentage.PNG

 

Then, in tMap, I put Pourcentage in String because in log console, it indicates the error For input : String. At the beginning, I put in Double and also in Float.

tmap_pourcentage_erreur.PNG

 

I run the job : 

tlogerreur.PNG

Normally, I should have : 0,47 (or 0,468). From where goes out 4.475? 4.7? 0.8?

There is a single line....

 

 

Thanks in advance ! 


Accepted Solutions
Sixteen Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

Right, that is your cause. Your values are read as Strings with a "." as the decimal separator, but your system is expecting to convert using "," as the decimal separator. 


All Replies
Thirteen Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

@Beauchamp_J, are you looking for the following?Untitled.png

 

Manohar B
Seven Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

@manodwhb

 

This is my consol log : 

tlogrow.PNG

 

We can see "0" in tLogRow, but it isn't possible.....

 

 

Little information : My excel file was in format xlsb and i save the same file in format xlsx. I hope that the matter isn't from that format xlsb to xlsx. Talend can't read the format xlsb.

Thirteen Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

@Beauchamp_J,can you write to tlogrow from tExcelfileinput?

Manohar B
Seven Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

@manodwhb

 

Write to tlogrow from tExcelfileinput? Sorry, i don't undestand....Smiley Frustrated

 

 

Thirteen Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

@Beauchamp_J,without tmap connect from tFileInput excel-->tLogRow,and check is this working?

Manohar B
Seven Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

tlogrow1.PNG

 

It works. Not an error.

It is the tMap which raised a problem. Why?

Thirteen Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

@Beauchamp_J,can you post tmap settings.

 

Manohar B
Thirteen Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

@Beauchamp_J,use below way in tmapUntitled.png

Manohar B
Seven Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

@manodwhb

Forgive my previous post and read here. 

 

So, i take the file with all columns and Talend only keep 0.0% but not the others.

(Dupont Adele, Tintin Amerique, David Xavier, Vin Rouge aren't in the console log (for input string))

 

I show that to you : 

tfileinput.PNG  

 

 

 

resume.png

 

Seven Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

 @manodwhb

With tMap settings :

 

tmap1.PNGlogrow1.PNG

Seven Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

@rhall_2_0  

@manodwhb 

 

Maybe you have an idea?

Sixteen Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

I have a suspicion that your system settings are wrong for the data you are working with.

 

Can you run this code in a tJava in a blank Talend job and show us what you get?....

java.text.DecimalFormat decFormat = new java.text.DecimalFormat();
        java.text.DecimalFormatSymbols decSymbols = decFormat.getDecimalFormatSymbols();           
        System.out.println("Decimal separator is : " + decSymbols.getDecimalSeparator());
        System.out.println("Thousands separator is : " + decSymbols.getGroupingSeparator());
Seven Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

Sixteen Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

Right, that is your cause. Your values are read as Strings with a "." as the decimal separator, but your system is expecting to convert using "," as the decimal separator. 

Seven Stars

Re: Error with Format Pourcentage from Excel to Number in new Excel

You are right. i check in advanced settings on the fileinputexcel : 

Thousands Separator : ""

Decimal Separator : "." instead of ",".

And i put all columns in string

And Bingo ! It works! 

phew!