topic Re: tFileInputExcel reads formatted value instead numeric value in cell in Talend Studio

tFileInputExcel reads formatted value instead numeric value in cell

mario_mirabile — Wed, 03 Apr 2013 06:11:23 GMT

I have a job which loops through an Excel spreadsheet reading one column at a time. The schema of the spreadsheet defines all columns as string, with the "Read real values for numbers" option checked.
One column contains floating point values formatted to display no decimal places. The data is read by a tJavaRow component, however I get only the displayed value passed to the tJavaRow from the tFileInputExcel.
If I change the display format in the spreadsheet so that at least one decimal place is displayed, I get the correct value, usually with more than one decimal place in the data.
Is there any way to get the tFileInputExcel to pass the underlying value in the cell rather than the displayed value?
thanks
Mario

Re: tFileInputExcel reads formatted value instead numeric value in cell

Anonymous — Wed, 03 Apr 2013 07:59:16 GMT

Hi,
Could you elaborate your case with an example with input and expected output values? You work flow is: tFileInputExcel-->tJavarow? What's the code in it? More information will be preferred.
Best regards
Sabrina

Re: tFileInputExcel reads formatted value instead numeric value in cell

Anonymous — Wed, 03 Apr 2013 20:47:10 GMT

All APIs I know, read only one type of cell value. There is no option to read "what the user see". If you change the format in Excel typically you change the cell data type and mostly the content.
Again, you do not change only the display format in Excel, you usally change the entiere content!

Re: tFileInputExcel reads formatted value instead numeric value in cell

mario_mirabile — Wed, 03 Apr 2013 22:47:25 GMT

I have further tested this behaviour by simplifying the job to contain only a tFileInputExcel and a tlogRow. The test file consists of two numeric columns containing floating point numbers (eg 118269.8)
When the column in the spreadsheet is set to display 0 decimal places (displaying 118269) , the data displayed in the tLogRow is just the displayed value.
If the display format in the spreadsheet is changed to display one decimal place (without changing the data), the data displayed in the tLogRow changes to 118296.8.
This happens if the schema definition for the column in Talend is either string or float.
Talend v5.0.1, Windows XP, Excel 2003.
Looks like a bug to me.
Mario

Re: tFileInputExcel reads formatted value instead numeric value in cell

Anonymous — Mon, 08 Apr 2013 03:31:47 GMT

Hi,
What is your expected result? Is your input 118269.8 in spreadsheet? In talend, there is no check in "String" type. If your input is 118269.8, the actual result in tlogrow is still 118269.8.
Best regards
Sabrina

Re: tFileInputExcel reads formatted value instead numeric value in cell

mario_mirabile — Mon, 08 Apr 2013 03:47:46 GMT

What I expect is the actual value in the cell. What I get is the formatted display value. I think my description of the problem is quite clear if you re-read my last post.
Mario

Re: tFileInputExcel reads formatted value instead numeric value in cell

Anonymous — Mon, 08 Apr 2013 05:44:04 GMT

Hi,
I have made a testing on Talend Open Studio 5.0.1 r 74687 (Excel2007, window7 64 bit).
My input data is:
118269.8
118269
In talend studio, the work flow is :tfileinputExcel-->tlogrow and the job works well. Your screenshots will be highly appreciated.
Please see my screenshots for details
Best regards
Sabrina

Re: tFileInputExcel reads formatted value instead numeric value in cell

mario_mirabile — Mon, 08 Apr 2013 06:38:15 GMT

Your example shows exactly what I've described when the display format in Excel is set to include decimals. In my first screenshot, the value in the cell clearly contains decimals, but they are not picked up by Talend because of the display format.
In the second screenshot, the values are unchanged, but talend now displays the value according to the Excel display format.
Mario

Re: tFileInputExcel reads formatted value instead numeric value in cell

Anonymous — Mon, 08 Apr 2013 07:04:12 GMT

Hi,
Have a try to check the box 'read real value for numbers' in the advanced settings tab of tFileInputExcel and read it with numeric data type and please give me your feedback.
Best regards
Sabrina

Re: tFileInputExcel reads formatted value instead numeric value in cell

mario_mirabile — Tue, 09 Apr 2013 00:52:11 GMT

That option is checked (as stated in my original post). Makes no difference to the behavior.

Re: tFileInputExcel reads formatted value instead numeric value in cell

alevy — Wed, 10 Apr 2013 00:26:32 GMT

I confirm this behaviour in v5.1.2. I'm afraid it's a bug and you'll have to submit a Jira.