multiple sheet output in one excel file from MSSQL with multiple Tables

[Anonymous]

Hi, Talend beginner here. I need to create job where I need to read data from MSSQL server with multiple tables with different schema. and get output in one excel file with different sheets depends upon tables in sql server. for example i have two sql tables name like students data, Faculty data. and in that tables there are 3 columns like  below.

Table 1 - students data

Roll No     Name     standard

12               A             10

13               B             10

14               C             12

__________________________

Table 2 - Faculty Data

Name       Salary(in $)      Experience (In Year)

AB           1500                     1

BC           2000                     2

CD           2500                     3              

_____________________________

i need output like this.

_

there may be number of tables so it can append in one excel with new sheet.

and if it is possible in one line without adding multiple tFileOutputExcel as well as tMSSQLinput components it would be great.

It will be very helpful to me if anyone give the solution for it.

Thank you.

[Anonymous]

Hi,

    Since you are beginner, I would recommend you to create multiple subjobs in Talend job where each subjob will read the data from one table and load it into corresponding sheet in excel file. You can link these subjobs by On SubJobOk condition to orchestrate them one by one.

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]

Thanks for valuable reply. i appreciate your suggestion but i need to design the job with one job because there are number of tables and also need in single design. if you have any solution please suggest me. it would very helpful to me. thank you.

[Anonymous]

Multiple sheets are possible but not with the build-in Excel components.

I suggest you take a look at the tFileExcel* components in Talend exchange. They can do this easily and much more like formulas, conditional formats etc. by the help of a template file.

Look here:

https://exchange.talend.com/

The components I mean are one of the most used.

[Anonymous]

Thank you for your valuable reply. i found the component but i don't know the way to achieve the solution for my question. could you please explain how to do it.

i will be very helpful to me.

thank you. 

[Anonymous]

First of all decide if you want to use an excel file as template.

Regardless if you want to do that you need the component tFileExcelWorkbookOpen. This component holds the actual excel document, all others are manipulating it or write it as file.

Take a look into the documentation of the tFileExcelSheetOutput component - pdf is linked in Talend exchange.

You can have plenty of them. E.g. you have 3 different queries read with tMSSQLInput components and now simply send these flows to tFileExcelSheetOutput components. You can configure a header with names just as you like and not limited to the schema column names and you can apply existing styles to the new data rows and so on... 

This component is what you need to create sheets and write data into it.

The last one is tFileExcelWorkbookSave to write the excel document as file to the local file system.

tFileExcelNamedCellOutput_complex.png

[Anonymous]

Thank you for the very useful suggestion. i have one question that is it possible to do the job using one  tFileExcelSheetOutput component. instead of adding multiple  tFileExcelSheetOutput component?  

[Eleven]

Hi,

On the contrary, I want to load one excel with multiple sheets into Sql Server with multiple tables,I download some components: tFileExcelworkOpen,tFileExcelsheetInput,,but i cannot figure it out .

So,could u give me some advise about the Steps?