Five Stars

creating multiple sheet in tFileOutputExcel

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

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:

Capture.PNG

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

Capture.PNG

Hope this helps.


TRF
19 REPLIES
Twelve Stars TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

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:

Capture.PNG

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

Capture.PNG

Hope this helps.


TRF
Five Stars

Re: creating multiple sheet in tFileOutputExcel

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?

Twelve Stars TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

@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-b...

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


TRF
Twelve Stars TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

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:

Capture.PNG

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:

Capture.PNG

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:

Capture.PNG

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):

Capture.PNG

Here is the result:

Capture.PNG

Hope this helps.


TRF
Four Stars

Re: creating multiple sheet in tFileOutputExcel

 

 

 

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!

 

Picture1.png

Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

Are you using the "append" options?

Rilhia Solutions
Twelve Stars TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

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.


TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

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.

Rilhia Solutions
Four Stars

Re: creating multiple sheet in tFileOutputExcel

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.
Twelve Stars TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

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.

TRF
Four Stars

Re: creating multiple sheet in tFileOutputExcel

do you have a screenshot maybe?

Four Stars

Re: creating multiple sheet in tFileOutputExcel

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@!
Twelve Stars TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

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

Capture.PNG

 


TRF
Four Stars

Re: creating multiple sheet in tFileOutputExcel

when I did , it just moved 0 rows

Picture1.jpg

Twelve Stars TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

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

TRF
Four Stars

Re: creating multiple sheet in tFileOutputExcel

But it does .

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

Re: creating multiple sheet in tFileOutputExcel

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

TRF
Twelve Stars

Re: creating multiple sheet in tFileOutputExcel

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.

Rilhia Solutions
Four Stars

Re: creating multiple sheet in tFileOutputExcel

how can I ?