topic Refresh/update file before loading in Talend Studio

Refresh/update file before loading

Anonymous — Wed, 05 Feb 2014 23:04:32 GMT

Hi,
I have an excel-file which compares a specified date vs date of today; that means, the difference is changing at least every day. Excel is calculating or updating this formula only in the moment, when you are going to open that file.
My task is to load that excel-file with "tFileInputExcel". But you get only the last stored status of that file and not the actual calculated difference of the both dates.
Is there any function, I can let calculate excel?
My idea is: function "open excel-file" -> "save excel-file" -> "tFileInputExcel" -> ?. So I am sure that I always load an actual file.
Do you have any idea how I can realize the instructions "open file" and "save file"?
Thanks for help

Re: Refresh/update file before loading

Anonymous — Mon, 10 Feb 2014 06:44:34 GMT

Hi,

I have an excel-file which compares a specified date vs date of today; that means, the difference is changing at least every day. Excel is calculating or updating this formula only in the moment, when you are going to open that file.
My task is to load that excel-file with "tFileInputExcel". But you get only the last stored status of that file and not the actual calculated difference of the both dates.

What's the expected result, the result of comparison(compares a specified date vs date of today)? Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina

Re: Refresh/update file before loading

Anonymous — Tue, 11 Feb 2014 19:31:20 GMT

Hi,
yes, correct. In the excel file I have one column with a specified date and a second column with "specified date - datetoday()". This difference will only be calculated, if the excel file is opened. So I want to create a procedure "open excel file -> save -> close -> load data (tFileInputExcel)". The reason to do this is that I want to have every day the actual remaining days, but the excel will not be opened every day. The actual remaining days will be used by another procedure.
But I do not found any command for "open - save - close".
Best regards
Stefan

Re: Refresh/update file before loading

Anonymous — Wed, 12 Feb 2014 11:10:21 GMT

What do you want cannot be delivered by the help with the build in Excel components. The problem is, you expect the behavior from Excel: recalculate values after opening - in the worst cases with the help of macros.
The Excel components from Talend Exchange tFileExcelWorkbookSave (save also close the file) has an option to evaluate all formulas but fails in case of macros.
You could open a file with tFileExcelWorkbookOpen, do anything with the content (e.g. write formulas in cells) and save it under a different name, different place and evaluate the formulas before.
Take a look at the components tFileExcel* in Talend Exchange.