Outputting a single Excel file with multiple works

Question

Is there a component in Talend Open Studio for Data Integration to be able to output a single Excel file but with 2 separate sheets in it?

I want to separate some columns in the original file into another sheet and another set of columns to the second sheet.

Answer 1

You'll need to output your data into two separate tFileOutputExcel components with the second one set to append the data to the file as a different sheet.

A quick example has some name and age data held against a unique id that needs to be split into two separate sheets with id and name on one sheet and id and age on another sheet.

I'm generating this data using the tRowGenerator component configured to generate a sequence for the id and random first names and ages between 18 and 75:

I then split this data using a tMap component:

The first flow of data can go to the first tFileOutputExcel component to create the file with a "Names" sheet:

Unfortunately we can't just output the second sheet of data straight away to the next file as Talend will need to open a write lock on the Excel file. So instead we stash the data into memory using the tBufferOutput component in this case (although we could also use a tHashOutput component or potentially stash the data on disk in either a temporary file or database if this is likely to exceed total memory).

Once the first sub job is completed writing the names data to the Names sheet of our target file we can then read the Age data out of the buffer and into the second tFileOutputExcel which is then configured to append the sheet of data to the target file: