topic Re: tFileInputExcel: Read real cell values in Talend Studio

tFileInputExcel: Read real cell values

Anonymous — Sat, 16 Nov 2024 12:26:08 GMT

Hi TALEND-community,
I'm having a problem with reading real (unformatted) cell values independent from the cell's data type.
Short scenario description:
The project is intended to import Excel data into an Oracle database. The layout of those Excel files is different from time to time, each file is imported along with a configuration file defining the position of some values (but not the data types). So what I basically do is importing the whole sheet with data type String.
Reading a cell as String obviously means reading the formatted value of a cell which causes several problems in my job design:
Problem: reading date and time
The formatted cell value for my date cell is "15. Dez 2009" (see talend-cell.png). The cell has a real cell value of "15.12.2009 07:05:15" (see talend-cell-value.png). As described above I have to read all cells as Strings which means I cannot access the cell's real value. Unfortunatelly this is exactly the value I need to parse it correctly (dd.mm.yyyy hh:MM:ss). Activating the setting "Read real values for numbers" (see talend-setting.png) does not help as it seems this has no impact on cells which are read as Strings.
>> Now my question:
Is there an option to force reading real values for ALL cells in an Excel files independently of the data type? Does anybody have another idea how to solve this issue?

Thank you for your support and ideas!
Kind regards,
Michael Hof

Re: tFileInputExcel: Read real cell values

Anonymous — Wed, 07 Mar 2012 12:44:04 GMT

Hi Michael
Sorry for delay, I have missed your topic.
What's the data do you get if you read it as a string now?
It is possible to send me a file for testing?
Best regards
Shong

Re: tFileInputExcel: Read real cell values

Anonymous — Wed, 07 Mar 2012 16:02:53 GMT

Hi,
thank you for your answer, see file in attachment...
Since the data is potentially sensitive, I've removed all cells but the ones concerning the issue at hand.
Regards.
It appears I may only upload pictures to the forum, I'll send you an email instead.

Re: tFileInputExcel: Read real cell values

Anonymous — Mon, 12 Mar 2012 12:17:02 GMT

Hello,
is there any solution to this problem? I have a very similar one as I have problems reading the real values of Excel cells. The setting "Read real values for numbers" does not seem to work. I guess I have a setting problem but so far no setting I tried worked. As an example Excel file there is a UNCTAD file that I tried to import:
http://www.unctad.org/Templates/Page.asp?intItemID=5823&lang=1
Annex Table 01, FDI inflows, by region and economy, 1990-2010, this is the last one in the column.
All numbers seem to be integer, but the real values are doubles.
Any idea how to import the real values correctly?
Thanks a lot!
Ralph

Re: tFileInputExcel: Read real cell values

Anonymous — Tue, 29 May 2012 16:40:39 GMT

HI. I tried with the link, but it seems outdated.
What excel format exactly are you trying to read? *.xls or *.xlsx?
Have you thought about converting the excel to *.txt/*.csv?
HTH

Re: tFileInputExcel: Read real cell values

Anonymous — Wed, 30 May 2012 10:26:31 GMT

hi slenk,
thanks for your answer! I am trying to read .xls files, .csv works fine but the number of files is quite big so I am looking for a way to read the original files properly. The UNCTAD website changed but the file is still available here:
http://archive.unctad.org/Templates/Page.asp?intItemID=5823&lang=1
(at the bottom Table 01)
Thanks for testing
Ralph