tFileInputExcel and formulas

[Anonymous]

Hi,

I'm reading an xlsx with tFileInputExcel
Everything is perfect, except for all cells of a column, containing formulas like : "=VALUE(40)"

When I write the result (without any transformation) the result is always 0

If I convert manualy in the xlsx the cells format from "General" to "Text" it works. (but I have 900+ files, so I can't do it)

Then I deduce there is a problem in the reading with the component tFileInputExcel

Anyone has an idea ?

[fdenis]

there is no problem. Talend fill excel with data.
after, excel can execute formula but there is no data type named excelFormula.

I have solve this problem using template containing formula, if you know the number of data line.
and using tjavaflex with excel lig to convert string to formula and then execute formula.

good luck

[Anonymous]

Hello @Hurek ,

I tested and it is working with me.

Could you share us more informations about your problem?

Regards,

Pierre

[Anonymous]

Here is how it looks like.

I delete my output, copy a template

Then I parse several hundred of xsls.

I let my default mapping :

But in the result file, cells from "newColumn1" containing "=VALUE(40)" became "0". But I expected to have 40.

Here is a screen from the xslx (Column J is the "newColumn1") :

[fdenis]

excel can be configured to keep or to not keep formula results.
it depend on xlsx file setting.
if file is generated results can be 0 if the formula wasn't executed and stores into file.
Excel file are made to be open with excel. they are not made to store data.

Regards,
good luck

[Anonymous]

> if file is generated results can be 0 if the formula wasn't executed and stores into file.

If my formula is not interpreted, I would have "=VALUE(40)", no ?

> Excel file are made to be open with excel. they are not made to store data.

The fact is this a Talend use case to read an Excel file. So we cannot argue that it should not work.

[fdenis · Accepted solution]

formula and value are not stored in the same place.
so
If my formula is not interpreted, I going to have default value?

if excel execute and store the value it's going to work fine.
but you cannot insert or read formula from talend.
you can read stored resut/data.