Toad World® Forums

Data Point, Excel Pivot Tables


#1

Hey All,

I need to automate report creation where the data is in a CSV file and some data is also stored in an SQL Database. I currently make the reports manually but its very time consuming. Is it possible to use data point to do this? I have looked at other software and normally it requires a programming or scripting background - of which i don’t have.

I am asking because i normally use Excel and Pivot Table to make my reports.

Thanks,

Robert


#2

Robert,

I will suggest the following workflow

  1. import your csv file as a table to your SQL Database,

  2. write a query against both tables to get the result set,

  3. export it to a Excel file

  4. design a pivot grid in the Excel file, then add a macro to refresh it, save the Excel file with .xlsm extension.

Now, you can automate the whole process by adding step1 as import activity and step 2, 3, 4 as export activity (export query to xlsm file and run macro at the end). Save it and schedule the job. Done.


#3

For step one you could connect directly to the csv file. Just use the Excel type and point to csv file.


#4

Hi Debbie,

Is there a video of how to create the pivot grid in conjunction with the macro?


#5

Actually, no. I never got into training on writing macros. I know many of our users do this. I would check the internet for sample code.

I only have a video on how to refresh pivot grids with Automation. www.toadworld.com/…/1142.aspx

And for macros I only show are to run the ones that exist.

www.toadworld.com/…/1143.aspx