topic Re: Error reading excel file having formula cells in it. in Talend Studio

Error reading excel file having formula cells in it.

Anonymous — Fri, 31 May 2013 08:03:54 GMT

Hi,

I am supposed to write the data of excel file into db table.
but some of the cells in excel are dependent on formulas like
=IFERROR($F$30*E35/$F$26,0)
while reading these cells tFileExcelInput giving a runtime exception as below attached image.
Is there any option like read only the content of those cells ignoring(without evaluating) the formula on those cells.
so that i will get all the data without any errors.

Re: Error reading excel file having formula cells in it.

Anonymous — Fri, 31 May 2013 08:24:30 GMT

Hi,
Perhaps you don't set the column for the formula cells in schema setting.
Best regards
Sabrina

Re: Error reading excel file having formula cells in it.

Anonymous — Fri, 31 May 2013 08:25:41 GMT

Sabrina,
What do you mean by "set the column for the formula cells in schema setting"
Can you guide me on the same.
Thanks

Re: Error reading excel file having formula cells in it.

Anonymous — Fri, 31 May 2013 08:33:18 GMT

Hi,
I mean in schema setting, don't set a Column in which formula cells exist so that tfileinputExcel will not read formula.
For example, in my excel file, I have Column1, Column2 and Column3. The formula cells exist in Column 3. In schema setting window, I will only set Column1 and Column2 to avoid to read formula cells.
See my screenshots
Best regards
Sabrina

Re: Error reading excel file having formula cells in it.

Anonymous — Fri, 31 May 2013 09:32:38 GMT

Sabrina,
Ok i will take only the columns which does not have formula on them, but how to get the value of the last column.
And one more thing is if my columns are A,B,C,D and formula is on C column how can i avoid only C column and read column D as it is ?
Is there any alternative for this kind of issues.
Thanks

Re: Error reading excel file having formula cells in it.

Anonymous — Fri, 31 May 2013 10:00:33 GMT

Hi,

Ok i will take only the columns which does not have formula on them, but how to get the value of the last column.

You can define the range of the columns to be processed through setting the first and last columns in the First column and Last column fields respectively.
See the component reference tFileInputExcel.

And one more thing is if my columns are A,B,C,D and formula is on C column how can i avoid only C column and read column D as it is ?

You can set A,B,D as your schema, for it is manual. The input component read the file content using schema.
Best regards
Sabrina

Re: Error reading excel file having formula cells in it.

Anonymous — Sat, 01 Jun 2013 13:56:18 GMT

IFERROR is not implemented means exactly what we can read. In the version of Apache POI which is used for the tFileInputExcel component this function is not implemented. Currently there is no way to read such cells.

Re: Error reading excel file having formula cells in it.

Anonymous — Sat, 01 Jun 2013 15:40:02 GMT

Good new: The current version of tFileExcelWorkbookOpen supports this function now!
http://www.talendforge.org/exchange/index.php?eid=623&product=tos&action=view
This component opens the excel file and you can simply read the sheet with tFileExcelSheetInput.

Re: Error reading excel file having formula cells in it.

Anonymous — Mon, 03 Jun 2013 05:49:12 GMT

Jlolling,
Thanks , I do have a question regarding tFileExcelSheetInput component i.e
why is it like this component does not take numeric column names , when we try to use numeric columns in excel and process it will get an error column name does not exist.

Re: Error reading excel file having formula cells in it.

Anonymous — Mon, 03 Jun 2013 21:22:12 GMT

This should work. The component has two overloaded methods to get the mapping between schema column and excel column. You should be able to write "O" or 14 (without " of course).