topic Re: Combine 2 Excel files into one in separate sheets in Talend Studio

Combine 2 Excel files into one in separate sheets

Anonymous — Tue, 16 Jan 2018 17:27:31 GMT

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

Re: Combine 2 Excel files into one in separate sheets

Anonymous — Wed, 17 Jan 2018 08:43:34 GMT

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

Re: Combine 2 Excel files into one in separate sheets

Anonymous — Thu, 18 Jan 2018 00:04:51 GMT

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.

Re: Combine 2 Excel files into one in separate sheets

Anonymous — Thu, 18 Jan 2018 01:32:04 GMT

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

Re: Combine 2 Excel files into one in separate sheets

Anonymous — Mon, 22 Jan 2018 16:14:36 GMT

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

tfileinput+tfilelist.PNG

Re: Combine 2 Excel files into one in separate sheets

Anonymous — Tue, 23 Jan 2018 03:09:03 GMT

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

Re: Combine 2 Excel files into one in separate sheets

Anonymous — Tue, 23 Jan 2018 16:58:40 GMT

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

Re: Combine 2 Excel files into one in separate sheets

Anonymous — Wed, 24 Jan 2018 01:35:12 GMT

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

Re: Combine 2 Excel files into one in separate sheets

sanjeev1 — Thu, 16 Apr 2020 19:33:17 GMT

Hi,

I have a similar problem and trying to find a solution. I'm trying to merge two excel files with different schemas into one. I'm tryng the following scenario but not achieving the desired result.

I have already set the two files in the tfilelist component, but what property do I need to set for tfileinputexcel?