topic Re: tFileInputExcel and formulas in Talend Studio

tFileInputExcel and formulas

Anonymous — Thu, 16 Jan 2020 10:33:31 GMT

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 ?

Re: tFileInputExcel and formulas

fdenis — Thu, 16 Jan 2020 11:00:46 GMT

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

Re: tFileInputExcel and formulas

Anonymous — Thu, 16 Jan 2020 11:04:01 GMT

Hello @Hurek ,

I tested and it is working with me.

Could you share us more informations about your problem?

Regards,

Pierre

Re: tFileInputExcel and formulas

Anonymous — Thu, 16 Jan 2020 12:39:26 GMT

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") :

Re: tFileInputExcel and formulas

fdenis — Thu, 16 Jan 2020 14:19:09 GMT

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

Re: tFileInputExcel and formulas

Anonymous — Thu, 16 Jan 2020 19:29:52 GMT

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

Re: tFileInputExcel and formulas

fdenis — Fri, 17 Jan 2020 09:49:13 GMT

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.