How to add multiple data to one excel in different positions in a simple way

[Anonymous]

Hello, all.

I would like to put multiple data from DB into one excel file in different positons.(ex: A1 for the first data, C3 for the second, D9 for the 3rd, E10 for the 4th,....)

I know the next way . Is there any other simple way ?

the way I know:

  tDbOutput→tFlowToIterate→((onSubJobOK))

 →tFixedFlowInput→(1row)→tFileOutputExcel(with adding option)→((onSubJobOK))

→tFixedFlowInput→(1row)→tFileOutputExcel(with adding option)→((onSubJobOK))

→tFixedFlowInput→(1row)→tFileOutputExcel(with adding option)→((onSubJobOK))

...............

I'm using talend open studio for data integration.

Best regards,

[DataTeam1]

Check additional component tFileExcel which you can download from Talend Exchange site here: tFileExcel

It has many various capabilities:

The component suit constists of: 
tFileExcelWorkbookOpen - Opens a workbook by reading a file or simply create an empty new one 
* detect automatically the file type - no need to declare it as setting 
* encrypt password secured files 
tFileExcelWorkbookSave 
* write a workbook 
* recalculate all formulas 
* encrypt the file with a password 
* automatically set the necessary file extension 
tFileExcelSheetInput - read an Excel sheet with lots of comfort functions 
* Automatic adjustment of the columns to read 
* Read comments 
* Read cell style 
* Read and interpret date formats very tolerant 
* Read only the columns you need 
* Can skip erroneous cell content 
tFileExcelSheetOutput - write Excel sheets 
* write vertically (optional) 
* write only the columns you need 
* recalculate tables if they are affected (and update this way pivot tables) 
* recalculate conditional cell styles 
* apply cell styles from the first (or two) rows 
* apply cell data validation 
* write formulas 
* apply data validation to the new row (taken from the first written row) 
* shift existing row before writing the new row 
tFileExcelSheetList 
* List all sheets 
* provide meta information about the sheets 
tFileExcelNamedCellInput 
* read named cells 
tFileExcelNamedCellOutput 
* write named cells 
tFileExcelReferencedCellInput 
* read cells be absolut references 
tFileExcelReferencedCellOutput 
* write into absolut referenced cells

[akumar2301]

Hello

If you donot want to use custom component

Use tfileoutputexcel

Append existing file /existing sheet

Specify Y poistion , from where you would like to start writing like A1 ,C3 etc.

[Anonymous]

Hi all, thanks for reply.

but I don't want to use custom one.

I know the way of specifying the x, y position of excel in tFileOutputExcel.

I'm new to talend.

I would like to know whether or not I have to just write repeated components :

  tDbOutput→tFlowToIterate→((onSubJobOK))

 →tFixedFlowInput→(1row)→tFileOutputExcel(with adding option)→((onSubJobOK))
 →tFixedFlowInput→(1row)→tFileOutputExcel(with adding option)→((onSubJobOK))
 →tFixedFlowInput→(1row)→tFileOutputExcel(with adding option)→((onSubJobOK))
 →tFixedFlowInput→(1row)→tFileOutputExcel(with adding option)→((onSubJobOK))
 ...............

Is there any other way to write less component ? ( like tmap or tLoop ??
I suppose tmap is useless for this objective.)

[Anonymous]

Hi,

    If the number of rows from both input DB and files are equal and are in sorted order, I will try another method.

    I will add a sequence for all incoming data from DB and lookup files after reading the data. Then I will do inner join on the sequence number. So the first record of DB input record will match with first record of lookup file. Since its inner join, you can drag and drop all the columns to output in straightforward manner. Once the output mapping is completed, we can directly write the data to file. 

   Could you please try it out and let me know the results?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Thank you for the reply.

 >  If the number of rows from both input DB and files are equal and are in sorted order

in my case, thease are not equal and not sorted order...

[Anonymous]

Ok. If that is not the case, the only other chance where my method will work is when there are columns which can be matched with both data sets.

If that also is not there, I would suggest to try the methods outlined by others in this post.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[nsivakumar]

Hi, I'm trying to use these custom components in my talend jobs.

But I'm not sure how to use the components tFileExcelNamedCellInput.

I wanted to read the cell details from the excel like cell name (like A1 etc) and cell value.

Please help me out with any documentation of instruction on how to achieve this.

@Jan Lolling​ 

[Rajender]

Hello

Is there any documentation available, I have been trying to use tFileExcelNamedCellInput to read following data.

Y3

D4

E6

It will be very helpful.

Best Regards,

Rajender Prasad