I am pulling data from database and storing it in Excel. This output excel is having some formulas in it. The transmission is taking so much of time to complete (more than 30 min to complete the transmission). Please suggest how to make the transmission faster. When i tried to load in normal excel with multiple sheets, it transmits fast. But when i use this attached excel which has formulas then the transmission is slow. Is it the issue with excel? Or is there any solution in talend to increase the speed?
Would you mind showing us your formula cells? Does it works fast when you use t<DB>Input-->tLogrow without excel output component in your work flow?
When i use normal excel without any formula or tLogrow, it works fast.
Only the excel with attached formula is taking so much of time.
I have the same issue with tFileOutputExcel.
I was using Talend 6.4.1 and the job was running in 2-3 minutes.
Now I've updated to Talend 7.1.1 and immediately the running time jumped to 30 minutes.
The component is having this behavior only when the excel file has formulas that needs to be recalculated.
Please advice how to solve this issue.
Did you try to do the same rules through Talend before loading the excel sheet?
I know that excel rules like graph creation cannot be done through Talend but I would suggest to move any possible computation formulas to Talend itself before loading.
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)
Unfortunately it is not an option. I have 2 sheets that consist only of formulas.
All I need to do is to update the sheet containing the raw data.
can you please check and confirm what is calculation mode in excel , is it automatic or manual?
If it is automatic, try to change it to manual and let me know if it improve performance.
To switch to manual mode in excel, go to Formula Tab –> Calculation Options –> Manual
I've changed the excel formula calculation to Manual.
In the Talend job: advanced settings, I've unchecked "Recalculate formulas".
Now the job runs in 1 minute.
As I've already mentioned, there was no issue with the previous Talend version that I was using... Runtime was 1 min also with formulas recalculated.
The moment I've switched to 7.1.1, this issue appeared.
I need the formulas to be recalculated as I am sending this report over email to the managers.
Could you please open the error log generated automatically by the Studio to see whether you have error stack trace there?
Watch the recorded webinar!
Accelerate your data lake projects with an agile approach
Create systems and workflow to manage clean data ingestion and data transformation.
Introduction to Talend Open Studio for Data Integration.