One Star

Dynamic Excel Ouput

Hi,
I've received a request by my management team that I don't know how to acheive.
We have several reports to run every day. Data is coming from multiple system and I need is a way to generate those report automaticaly
The reports are actually SQL queries. We have dozen and dozen of these.
How would you do that. The queries to run ( + when + where,+destination) are all located in a table.
If I could just deal with that table one row at a time, dynamicaly run the sql and drop the result (with header) in a csv or xls file.
I have Open Studio version (that could be upgraded if needed).
Can it be done with the open studio anyway?
I'm opened to any work around that does not involve implementing all the schema one at a time (between 37 as of now and around 60 in the next month)
Another reason of doing it this way is the possiblity to add a row in the source table mentionned above (ie a new report to produce) without editing any jobs
Make sense?
5 REPLIES
Employee

Re: Dynamic Excel Ouput

Hi,
You can definitely do that, but with the subscription edition(Talend Enterprise Data Integration). In this edition, you are going to be able to use the Dynamic Schemas.
Here is how it would work:
tDbInput --row--> tFlowToIterate --iterate-> tDbInput ---> tFileDelimitedOutput
- In the first DbInput, you will select the queries, tables, etc...
- Then you will turn your flow of data into an iteration with the tFlowToIterate component (Thus be able to execute the next process for each query)
- In the next DbInput, you will use the current query being executed for your query, the table name, db, etc... being dynamic as well (Using the values from the current row for your iteration). You will then define the schema with a single column as dynamic (Therefore you don't have the constraint of having different schemas for the different queries).
- And then you can use a tFileDelimitedOutput to write the result of the query into a csv file, the name and path of the files can be dynamic as well if your original table returns these informations.
Hope that helps, let me know if you're interested, and we can take that conversation offline
PS: I'm French so we can exchange in French or English!
One Star

Re: Dynamic Excel Ouput

Let's keep it in english... for everybody else!
Thanks, this is what I'm looking for. So for I did not found any example on the net but I suppose I'll eventualy find one.
Before asking the business to pay for Talend, I have to convince them that it will work.
YOu have any tutorial or reference I could review?
Thanks again!
Manuel
One Star

Re: Dynamic Excel Ouput

Just found this.
http://www.talendforge.org/forum/viewtopic.php?id=18793
I have enough for now
Thank you

Re: Dynamic Excel Ouput

Another option would be to dynamically re-write your queries to insert a delimiter ( ";" ) between each column and concatenate them together into a single column.
so a query like:
SELECT COL1, COL2 FROM TABLE

would become (oracle syntax):
SELECT COL1 || ';' || COL2 FROM TABLE

The benifit of this approach over dynamic schema's is that Talend would not need to be aware of the schema of the queries. In your Talend job, there would be a single column in the component schemas and you would be relying on the queries to insert the delimiters for excel to read. The job would look something like this:
tDbInput
|
row
|
tFlowToIterate
|
iterate
|
tJava : prepare query by replacing comma (",") with concat ( "|| ';' ||" ) and storing query in context varible
|
tOracleInput (issue query) --row--> tFileOutputDelimited
One Star

Re: Dynamic Excel Ouput

Thanks JohnGarrettMartin,
Now that I've seen your post... it's seems obvious... and less expensive!
The tree that hide the forest.
I'll see where this is going to take me...
Thank to the both of you!
Manu