Combine 2 Excel files into one in separate sheets

Four Stars

Combine 2 Excel files into one in separate sheets

Hello all, 

 

This might be an easy question for some but I'm trying to combine 2 Excel file outputs into 1 file in separate sheets. Does anyone have a simple solution for this?

 

I tried a solution I found on Google but am still kind of lost. 

 

Any help would be greatly appreciated!

 

Alex


Accepted Solutions
Community Manager

Re: Combine 2 Excel files into one in separate sheets

Hi Alex
Thanks for your feedback. Please accept one of answers as solution if it fixes your problem, it will be useful for others to find the answers quickly if they has the same issues.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Moderator

Re: Combine 2 Excel files into one in separate sheets

Hello,

From your screenshot, we see that you use a tfilelist component before tfileinputexcel_1.

Do you want to combine a set of excel files?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: Combine 2 Excel files into one in separate sheets

Yes I want to combine 2 Excel files into one but are separated in different sheets. Both file paths are located in the tfilelist component.

Community Manager

Re: Combine 2 Excel files into one in separate sheets

Hi aphay89
Check the 'append existing file option' on tFileOutputExcel to output data to the same file, set a dynamic sheet name, for example, use current file name as sheet name.
tfilelist--iterate--tfileinputexcel--main--tFileoutputExcel

on tFileoutputExcel, set the sheet name as:
((String)globalMap.get("tFileList_1_CURRENT_FILE"))
and check the 'append existing file' box.

Hope it helps you.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: Combine 2 Excel files into one in separate sheets

Hi Shong, 

 

I'm now a bit confused on what the file directory and sheet names should be on the tfilelist and tfileinputexcel components as I am encountering errors after I run my job. 

 

Can you please clarify? 

 

Thanks,

Alex

Community Manager

Re: Combine 2 Excel files into one in separate sheets

Hi Alex
If there are only two files in the directory, you can set the file mask as: "*.xlsx"
or you can specify the file name in the file mask table if there are many files.
on tFileInputExcel, there is a global variable that store the current file path, so you can use it in the File name/stream field:
((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))

If you still have errors, please show me the errors.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: Combine 2 Excel files into one in separate sheets

Hi Shong, 

 

I was actually able to combine the Excel files using the "Append existing file" option box on my tfileOutputExcel of my last subjob. 

 

Appreciate your input though!

 

Best, 

Alex

Community Manager

Re: Combine 2 Excel files into one in separate sheets

Hi Alex
Thanks for your feedback. Please accept one of answers as solution if it fixes your problem, it will be useful for others to find the answers quickly if they has the same issues.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business