Error reading excel file having formula cells in it.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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).