topic Re: Excel output from multiple SQL databases in Talend Studio

Excel output from multiple SQL databases

Anonymous — Tue, 12 Feb 2019 15:41:48 GMT

Hello,

I'm very new to Talend (was using FME), and here's my problem.

I'm reading datas from different databases, and to help users to realise some QCs, I have to push those datas on an excel file.

I've already done that with talend, but to improve their QCs, I would like to present my excel file like that :

Column1	Column2	Column3
Obj1Attr1FromDb1	Obj1Attr2FromDb1	Obj1Attr3FromDb1
Obj1Attr1FromDb2	Obj1Attr2FromDb2	Obj1Attr3FromDb2
Obj1Attr1FromDb3	Obj1Attr2FromDb3	Obj1Attr3FromDb3
Obj2Attr1FromDb1	Obj2Attr2FromDb1	Obj2Attr3FromDb1
Obj2Attr1FromDb2	Obj2Attr2FromDb2	Obj2Attr3FromDb2
Obj2Attr1FromDb3	Obj2Attr2FromDb3	Obj2Attr3FromDb3

Can someone help me to find the procedure with talend to present datas like that ?

Thanks by advance,

Slop

Re: Excel output from multiple SQL databases

fdenis — Tue, 12 Feb 2019 16:39:32 GMT

Hi,

We cannot help you if we do not know the actual format of your data.

Did you mean row to columns?

Or something else?

Regards,

Re: Excel output from multiple SQL databases

Anonymous — Tue, 12 Feb 2019 17:50:09 GMT

Hello,

If you have the same schema in all three DBInputs, you can build one flow of them using the tUnite-Component. I suppose you have the attributes already in columns?

If there are not already columns you can sort by, you can add pseudo columns before joining the flows. I would do it in the SQL-Statement. You can do that in a tMap.

My Suggestion:

- DBInput1 (Obj1, Attr1, Attr2, Attr3, "DB1") --

----> tUnite ----> tSortRow (Object, DB) ---> tFilterColumns ---> tFileOutputExcel

- DBInput1 (Obj1, Attr1, Attr2, Attr3, "DB2") --

Re: Excel output from multiple SQL databases

Anonymous — Wed, 13 Feb 2019 07:50:44 GMT

Hello,

Thanks for your reply, yes the attributs are already in columns. But schema is not the same for all DbInputs.

Currently, I've one sheet per database, and I would like to aggregate that in one.

Re: Excel output from multiple SQL databases

fdenis — Wed, 13 Feb 2019 09:29:17 GMT

first use tMap to format all your sourses on the same format.
then merge all of them using tUnite.

Re: Excel output from multiple SQL databases

Anonymous — Wed, 13 Feb 2019 13:22:50 GMT

These configure database SQL Server instance that hosts the database which contains the data.In this demo, I am connecting to a SQL instance using Windows authentication as my authentication type and connecting to the server. MBA Dissertation Writing Help

Re: Excel output from multiple SQL databases

fdenis — Wed, 13 Feb 2019 15:09:10 GMT

and then?

Re: Excel output from multiple SQL databases

Anonymous — Thu, 14 Feb 2019 15:27:42 GMT

So, I've done what you suggest, here's my workflow :

But I can't connect both lines on a tUnit (the schema is now identical for db1&db2).

Re: Excel output from multiple SQL databases

Anonymous — Thu, 14 Feb 2019 21:16:57 GMT

You can't connect them because they have the same source. In Talend you can't connect flows from the same origin to avoid circles.

What are you using the tFileInputExcel_2 for? Are you using data from it for the SQL-Queries?

Then it is a bit more difficult. To avoid a common source I would use two jobs.

- In the main job you can get the relevant data from Excel and pass it to the second job (tRunJob-Component in DataFlow, you can pass the whole context with a Checkbox and add specific Parameters from the flow to a context-variable in the subjob in the grid context-variables).

- In the child-job you can then select the data from you two databases (just copy your components, both have no in-flows or events). Then you can connect them to tunite.

- If you Need the data in the main job, you can pass it to "tBufferOutput" in the child-job. In the main-job you have to activate the Option "propagate the child result to the output schema" from the advanced settings of the tRunJob

Re: Excel output from multiple SQL databases

Anonymous — Fri, 15 Feb 2019 08:44:54 GMT

Yes I'm using tFileInput for SqlQueries (end users specified id of object they want to QC on it).

I'll try to make you're solution and let you know the results.

Re: Excel output from multiple SQL databases

Anonymous — Mon, 18 Feb 2019 14:36:38 GMT

Hi,
So I've applied your suggestion and it worked perfectly.
Thanks a lot.