I am trying to automate some excel exports from Toad Data Point. I have various tables which I need to export to similarly named excel spreadsheets. I am trying to use Select to File in automation.
I’m struggling with the syntax in the SQL (i.e. the table name). So for example - say my table name is STWL167 and the spreadsheet I need to write to is STWL167 too.
Is it possible to insert a variable (using a loop) into the SQL statement to select which table to extract and which spreadsheet to write to?
I believe the latter is possible but I’ve not seen any examples of using the variables for a table name?
Thanks for any help you can give
I have attached an example of how to do this. I put a list of tables in an Excel file. (C:\temp\tables/xlsx) I then connect to the file and query for a list of tables. In my loopsta set I have one SelectToFile. I replace the name of the table in the select with the current table in the loop. I also use that for the name of the excel file as well as worksheet name.
TestTables.zip (7.58 KB)
Many thanks Debbie - I can see how that works. My SQL includes specific column names and a where clause. I’ve tried just substituting the variable name in the SQL like you have and then continued to substitute the table name throughout the query. But it doesn’t seem to like it - any ideas?
My original SQL was as follows - STWL167 being the table name which needs to be substitued with the variable.
SELECT STWL167.ELEMENT_NAME,
STWL167.ELEMENTSTATUS,
STWL167.PARAMETER_NAME,
STWL167.PARAMETER_UNIT,
STWL167.VALUEAVG,
STWL167.WARNINGMINVALUE,
Left(STWL167.TIMECODE, 10) AS RECORD_DATE
FROM TDP.STWL167 STWL167
WHERE (STWL167.PARAMETER_NAME IN (‘CPU Usage’,
‘Memory Free’,
‘Page Fault Rate’,
‘Pages Paged In’,
‘Pages Paged Out’))
AND (STWL167.TIMECODE BETWEEN ‘2013-01-01 00:00:00’
AND ‘2013-12-31 00:00:00’)
I don’t see in this query where you are using any variables. Please post your automation script (*.tas) and all dependent files. Also include a log file showing what error you are getting. I will take a look.
Debbie - all I did was replace STWL167 table name in the above example with the variable name surrounded by hashes - the same as you did with your example code. Sorry for my limited SQL skills - hoping to get on a course to improve that soon 
As a follow up to our discussion I have tried running your original example and it works fine. One question - I have an ‘average values’ column and when exported many of the double values i.e. the values with decimal points have been truncated (rounded?) to no decimal places. Is there any way of stopping this? This is not a formatting problem - it just appears to have dropped the decimal part all together.
As another afterthought - is it possible to use similar code to use the variable with a view - I’ve tried it but keep getting errors - any ideas as to the syntax for this (if possible)?
Anyone have any ideas on this - I’m so close to fully automating monthly data extraction - just stuck with this last stumbling block?
I never saw you post your automation script, supporting files and last log file. If you would like to send them to me by email you can send to debbie.peabody@quest.com
The latest Beta release seems to have fixed my problem and I am now getting full double precision.
