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

Error with Format Pourcentage from Excel to Number in new Excel

Anonymous — Mon, 14 May 2018 11:02:27 GMT

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)

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.

I run the job :

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 !

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

manodwhb — Mon, 14 May 2018 11:33:09 GMT

@Beauchamp_J, are you looking for the following?

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

Anonymous — Mon, 14 May 2018 12:57:36 GMT

@manodwhb

This is my consol log :

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.

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

manodwhb — Mon, 14 May 2018 13:06:58 GMT

@Beauchamp_J,can you write to tlogrow from tExcelfileinput?

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

Anonymous — Mon, 14 May 2018 13:17:38 GMT

@manodwhb

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

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

manodwhb — Mon, 14 May 2018 13:19:48 GMT

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

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

Anonymous — Mon, 14 May 2018 13:30:31 GMT

It works. Not an error.

It is the tMap which raised a problem. Why?

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

manodwhb — Mon, 14 May 2018 13:31:21 GMT

@Beauchamp_J,can you post tmap settings.

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

manodwhb — Mon, 14 May 2018 13:34:06 GMT

@Beauchamp_J,use below way in tmap

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

Anonymous — Mon, 14 May 2018 14:28:33 GMT

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

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

Anonymous — Mon, 14 May 2018 14:34:10 GMT

@manodwhb

With tMap settings :

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

Anonymous — Mon, 14 May 2018 15:26:34 GMT

@rhall

@manodwhb

Maybe you have an idea?

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

Anonymous — Mon, 14 May 2018 15:41:37 GMT

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());

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

Anonymous — Mon, 14 May 2018 15:45:48 GMT

@rhall

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

Anonymous — Mon, 14 May 2018 15:48:45 GMT

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.

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

Anonymous — Mon, 14 May 2018 16:06:46 GMT

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!