topic Re: creating multiple sheet in tFileOutputExcel in Talend Studio

creating multiple sheet in tFileOutputExcel

Anonymous — Fri, 12 May 2017 21:34:51 GMT

i am using talend openstudio 6.3. i want to read the employee,department data from oracle data base and data has to be populated into single excel file with 2 sheets ,one sheet for employee and second sheet for department.

what is the use of "Is absolute Y pos" and append existing file/ append existing sheet option

Re: creating multiple sheet in tFileOutputExcel

TRF — Fri, 12 May 2017 21:57:40 GMT

Hi,

"Is absolute Y pos" define the row (from 0 to n) you want the rows to be written.

"Append existing file / Append existing sheet" options mean you want to write at the end of the file / sheet if it exists.

Carefull, if you try to push data to the same xlsx file to 2 different sheets in the same subjob, you'll lost the 1st one.

Here is a working example:

and one of the tFileOutputExec (both are same except the sheet name):

Hope this helps.

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Sat, 13 May 2017 18:49:59 GMT

is there any alternative way to push data to the same xlsx file to 2 different sheets in the same subjob with out loosing data in any sheet? is this talend restriction?

Re: creating multiple sheet in tFileOutputExcel

TRF — Sat, 13 May 2017 18:54:29 GMT

@raffeemd, you can refer to the following post as a starting point

https://community.talend.com/t5/Design-and-Development/Create-Multiple-Tabs-with-single-excel-file-based-on-the-column/td-p/22746

I'll try to give a complete example as soon as possible.

Re: creating multiple sheet in tFileOutputExcel

TRF — Sat, 13 May 2017 21:36:07 GMT

Hi,

As expected, @shong answer is the good one to write into multiple sheets without having to use multiple subjob.

This solution is particulary usefull when number of sheet to be created is not known when the job start.

Here is what the whole job looks like:

tFixedFlowInput is here for the example.

It replace the normal input which can be a file, a database, and so on.

tFlowToIterate convert main flow to iterate flow and input fields into global variables:

tFixedFlowInput generate a new data flow for each iteration with only one row at a time, allowing to proceed with multiple sheets in the same subjob (and with a number of sheets which is not predefined).

The schema is the same (in this example) as it was for tFixedFlowInput and component is configured as it:

Finally, in tFileOutputExcel, you just have to check "Append existing file" and "Append existing sheet" use the global variables which contains the aggregate value as a sheetname (here the Flag variable):

Here is the result:

Hope this helps.

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Wed, 17 May 2017 15:30:55 GMT

hello, I need every output to be a sheet . how can I do this ?

when I do it now it just erase the first 3 and contains the last one.

Thanks!

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Wed, 17 May 2017 15:38:51 GMT

Are you using the "append" options?

Re: creating multiple sheet in tFileOutputExcel

TRF — Wed, 17 May 2017 15:51:06 GMT

I think (to be confirmed) you can't have more than 1 tFileOutputExcel for the same file in the same subjob or only the last writter will win.

In this thread I give a solution you can follow if each sheet has a specific schema.

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Wed, 17 May 2017 15:53:49 GMT

Ah yes, that is right @TRF. Very much like you cannot several versions of an Excel file open to edit at the same time, you cannot do it in Talend either. Better to write to a tHash and then write the results to the Excel file in subsequent sub jobs.

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Wed, 17 May 2017 15:54:05 GMT

each sheet has a specific schema but when I write to 4 fileoutput in a row,each one override the other and It just remain a single sheet, although I wrote a diffrenet sheet to every fileoutput.

Re: creating multiple sheet in tFileOutputExcel

TRF — Wed, 17 May 2017 16:33:55 GMT

Replace tFileOutputExcel by differents tHashOutput not linked one to each other.
Then, add as many subjobs as you have Excel files, using tHashinput (refering the good one tHashOutput) as input flow.
Each subjob linked by onSubjobOk, the 1st one linked to the main subjob.
Sorry for the poor details, just on my mobile.

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Thu, 18 May 2017 06:39:48 GMT

do you have a screenshot maybe?

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Thu, 18 May 2017 09:10:47 GMT

and can the opposite works? I can read from different sheets to the same excel ? I've tried , and it shows like it moving 30 rows but actually the content of the file is empty.

Thank@!

Re: creating multiple sheet in tFileOutputExcel

TRF — Thu, 18 May 2017 09:35:56 GMT

Did you try to tick the "Stop reading on encountering empty rows" option on Advanced settings tab?

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Thu, 18 May 2017 10:02:02 GMT

when I did , it just moved 0 rows

Re: creating multiple sheet in tFileOutputExcel

TRF — Thu, 18 May 2017 10:15:21 GMT

So, if the Excel file contains no row, it's normal, doesn't it?

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Thu, 18 May 2017 10:17:20 GMT

But it does .

when I choose for example only one sheet it "moves" rows on the flow , although the output file is empty

Re: creating multiple sheet in tFileOutputExcel

TRF — Thu, 18 May 2017 10:22:55 GMT

Difficult to explain whithout any access to the input file.
Can you share it?

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Thu, 18 May 2017 10:52:02 GMT

This sounds like it is because you have "activated" the cells but haven't put any data in them. To test this, select the top 100 or so cells from the sheet, right click and select delete. Then re run it.

Re: creating multiple sheet in tFileOutputExcel

Anonymous — Sun, 21 May 2017 12:21:28 GMT

how can I ?