Combine 2 Excel files into one in separate sheets

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[sanjeev1]

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?