Copy Sheet from one Excel file to another

Five Stars

Copy Sheet from one Excel file to another

I have a talend job which is as below:


                       Read from Database ----> Write to Text File ----> Transform and output to xlsx file to a sheet called MTD1.


The reason I am doing it this way is because my query returns 280 columns. 


      There already exists a template file in the folder. I need to copy the MTD1 sheet to the template file.

Is there a way I can do this in talend? If not, should I use a java program to do this?


Any thoughts or ideas?




Eight Stars

Re: Copy Sheet from one Excel file to another

You easily can do this with just the standard tFileInputExcel and tFileOutputExcel components, as follows:


In your tFileInputExcel, set the format to Excel 2007, add "MTD1" to the Sheet list, and define the schema. With so many columns, I'd suggest you create it in Metadata first in order to use the wizards, but for my example I've just used two columns.




In your tFileOutputExcel component, specify the sheet name as "MTD1", and tick "Append existing file", but DO NOT tick "Append existing sheet" as we want to replace the entire sheet, rather than append or overwrite the data that's already there. This is important as if your source sheet has fewer lines, then the old data will be left underneath the copied data.





What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach


Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.



Introduction to Talend Open Studio for Data Integration.