Way to generate automated Excel templates?

kmare · Post by **kmare** » Thu Mar 31, 2016 3:07 am

Champs ,

Team is generating an excel report with the batch cycle run details manually which takes 30-45 mins every day and I am in a process to automate that report.

Except cosmetic changes (headings/font size/Textcolour/background colour) .
I am able to generate the report with a simple parallel job and a simple script.

For example :-
Heading

Below is my question:
Can we generate any Excel dashboard template (with cosmetics) using DataStage that also will reflect the same when moved to Linux to windows either via email or manually using winscp?

Your valuable inputs really helps.

[Note: Title changed to be more descriptive - Andy]

chulett · Post by **chulett** » Thu Mar 31, 2016 7:18 am

That's not really what ETL tools are meant / designed to do. A more typical solution would be for the ETL 'report' to be data loaded into a table and then something else, a BI tool for example, to do the dashboard Make Pretty part.

kduke · Post by **kduke** » Thu Mar 31, 2016 2:56 pm

It can be done. You need to build your header or read it in from sequential file. Then build table tag.

Then row tag. Field tag. Field value. Close field tag. Close row tag. Next row. Loop.

Close table tag and all header tags.

Each row needs a value to sort by. Start header rows with 10,000. Start rows with 20,000. Close table with 90,000. Then send all these to funnel stage and sort by this value. All rows have one column named HTML_ROW. Output only this column to a sequential file.

Is this worth it? Probably not. Can it be done? Yes.

kmare · Post by **kmare** » Thu Mar 31, 2016 11:22 pm

Thanks craig & mamu kim .

@ Mamu Kim , Could you please explain this in little more details ?..

build table tag. Then row tag. Field tag.

Field value. Close field tag. Close row tag.

what I doing is pulling our needed data from oracle (5 sqls ) and creating 5 csv files and finally appending in one tab in a csv .

I can able to give text colour with unix scripting able to view @ linux but when moved to windows it wont reflect as OS format level issues may be .

Your inputs helps us

chulett · Post by **chulett** » Fri Apr 01, 2016 6:23 am

Kim is talking about building a formatted HTML page rather than an Excel spreadsheet by creating all of the needed "tags" manually, which could then be opened by a browser... and technically Excel as well. If that's not already in your wheelhouse (building web pages and the like so you are familiar with the HTML markup language) then it's not worth considering. IHMO.

What about some kind of Excel macro that they run after import that would do all of the fancy formatting for them?

I'll mention this again and actually ask this time, does your company not have some kind of BI / Reporting tool?

qt_ky · Post by **qt_ky** » Fri Apr 01, 2016 6:35 am

In case you are not aware, a useful dashboard is included in the web-based DataStage and QualityStage Operations Console. It's very pretty.

chulett · Post by **chulett** » Fri Apr 01, 2016 8:57 am

Okay... but isn't that limited to canned reports based on metrics of job stats that the tool automatically captures for this very purpose? Or is it more... open... than this and something anything can be published to?

kmare · Post by **kmare** » Fri Apr 01, 2016 8:25 pm

Thanks craig / qt_ky ,

I am not familiar with HTML language and in this HTML approach can we merge 5 different layouts ?..

I am exploring this , once my data is ready in a tab , need to move another csv(xls/xlsx doesnt work) which has that macro but again when we move the to windows does this reflects the same , not sure .

We do not have reporting tool for this application .

kduke · Post by **kduke** » Sun Apr 03, 2016 8:56 pm

Explain 5 different layouts you want to merge. If you have 5 different tables then have 5 different jobs. If you have 5 tables that you want to merge into 1 report then sure you can do that.