How to combine multiple Excel in once

[NBen15]

hi,

I'm a new in Talend and need some help.

I have an excel file that contains multiple sheets, and each sheet has different headers, I have to load data into each sheet

For this I used a tmap with 10 outputs and each output is linked to a tFilOutputExecel, I also used tFilecopy to initialize each sheet.

The problem is that in my job I have 10 different Excels and 10 tFileCopy, and I have to copy the data from the 10 Excels into a single Excel file manually, can you help me to do it in a single file please.

here are the screenshots:

- Example of my excel file template file

• My job

• The excel file that I want to generate with the data

[Mr_B]

Hello NBen15,

To replace the tFileCopy, you just have to set up a tFileList and put in the "File mask" part, the extension concerning the excel files, so for example "*.xlxs" or "*.xls". With an iterate link on your tFileInput, this will allow you to read each excel file in the input directory.

You don't specify if the files are present in the same directory or not but I assume that is the case.

To process the tabs of your files, you can filter your tab names using the current_sheet variable (((String)globalMap.get("tFileInputExcel_2_CURRENT_SHEET"))) in your tFileInputExcel (All Sheet) 

which will allow you to target the tab of your choice. We can even imagine if the names do not change, looping over these tab names (a predefined list or other) of each file to recover the data.

In output file, to check but it would be enough to use this same variable (((String)globalMap.get("tFileInputExcel_2_CURRENT_SHEET"))) to specify the output sheet. This would allow you to loop through all your files with the same tab name and feed your output file and create as many output tabs as those present as input in your files.

[NBen15]

@Ben C​ Thank you for you feedback

For tFileCopy I think it is not very useful in my job because I write in the same Excel file

I modified my job to write in a single file with different sheet names, but my problem here is that it writes data only in the first page and not in the other sheets. (below the screenshot)

how can i use (((String)globalMap.get("tFileInputExcel_2_CURRENT_SHEET")))  here please ?

[Mr_B]

In your main statement, you say you put a tFileCopy to load the data from each tab, so in your example ty loads it 10 times.

The goal is to optimize the processing, so my idea is the following:

- A tFilecopy to load the file (for example all the Excel files present in the input directory).

- An iterate link to your inputFileExcel

- An automatic reading of the tabs of your file, intended for a file or a variable for example.

- And then you feed your output file as you already do.

You are on the right path because you manage to read the first tab, wouldn't you have put a limit on your file.

 

without thinking I would say that to read all the tabs of your Excel file, you can proceed like this (for example):

tFileInputExcel1 ---> Iterate stream ----> Tjava ----> Tjavarow.

you must choose the "All sheet" option of your inputFileExcel as input.

 

You don't put anything in tJava, and in tJavarow you put: output_row.newColumn=(((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET"))) or

newColumn is the name of your column in the schema and tFileInputExcel_1 which corresponds to the name of your file.

This should list the tab names for your input file.

In your tMap you can retrieve the name of the tab being processed with the variable (((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET"))).

This will allow you to process all of the tabs in your file instead of just one.

In the output file, you must mention the variable (((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET"))) in the name of the tab.

 

Hope this helps you.

[NBen15 · Accepted solution]

hello @Ben C​  thank you for the time you spent to write the comment, I found another solution using tHashOutput and tHashinput but I will test your solution

[Mr_B]

Hello Nben15,

Feel free to post the solution you found, it may help someone else who is in the same situation as you.

Don't forget to set your status to solved 😉