I have a query iterator with a substitution variable for my queries, which runs and creates the excel file for the first query, having an Overwrite option. The second query is also an Excel File format, same file name, with the Add New Sheet option, but it does not produce add a new tab to the file.
When I switch from Excel File to Excel Instance, using the same substitution variable, no file is generated. Same thing if I set the variable to a literal. However when I then hard code it in the query instead, then it does create a file, with tabs.
Is there a way to just create an Excel file with a query having a substitution variable?
Hi Ken,
Which Toad version are you using?
-John
hi John
I'm using version 17.1
Hi Ken,
Hm. This works fine for me with 17.1. Here's what I did:
When I run the query iterator action, the file SCOTT.XLSX is created with a separate tab for each row in the table.
The difference between XLS File and XLS Instance is:
- The XLS File does not require Excel to be installed. Toad directly creates the file.
- The XLS Instance option communicates directly with the Excel application. There some pros and cons to this, but one of the cons is that you have to save the file in Excel yourself, which makes it not so great for automation.
-John
please post a screen shot of your actions in Automation Designer.
The simplest way to do this is with a Delete file action, followed by the QI, with export dataset nested under it, as opposed to multiple export dataset actions.
Also note that the Export Dataset should be nested under the Query iterator, as shown above. Not just after it, as shown below.
Yes, but it's hard to suggest something without seeing how you are using the query iterator and sub variable.
thanks John. I'm not trying to create a separate tab for each record, but run a series of queries with each dataset on a separate tab.
Hi Ken,
Sorry I didn't get back to you yesterday. I see a couple of problems here:
- The query iterator cannot accept variables within the SQL. It can only create variables from query results
- Since you are creating the unit_code variable on your own, there is no reason to also put it in the query iterator. Just put the sysdate part of the query in there.
- You are referencing the unit_code variable incorrectly in your export dataset action.
Try this:
1st export dataset action looks like this (overwrite)
dataset tab of export dataset action - use a sub variable with any name, then link it to the %UNIT_CODE% variable. You could also have the yymmdd variable in there too if you wanted.
2nd and 3rd actions look like this (new sheet)
Run the app.
I provided aaa as the unit code, and here is my xlsx file with expected data on each tab.
I updated the variable in the query to "&x", and enclosed the environment variable with "%" symbols. I ran the app bu got the same result - only the first tab is populating. while the first export is set to Overwrite, the subsequent exports are set to Add New Sheet:
So where is it going wrong?
- Are your variable actions set to create global or local variables? If global, try local.
- Is that Excel file coming from your first action (and then the subsequent ones are not running), or is it a later action, and are all overwriting?
- After you run the app, so all actions show a green status of "completed"?
- I don't recall where that is viewable, but I believe they are Local
- Excel file is saved in the specified location, after the first action - none of the subsequent actions are overwriting that
- After I run the app, the green Completed indicator shows up on the iterator, not on the Export Dataset actions:
- Double-click the "Create variable" action and "Prompt Variable" actions to see the scope.
- That means that all of the exports ran. I am guessing that they aren't returning data. You can get a message when they don't return data by unchecking this:
You can turn on spool SQL to see the queries (after substitution) to see what Toad is running. Then you'll know if the substitution is happening correctly.
Main menu -> Spool SQL -> Spool to screen.
If you export your app and email it to me, I'll take a look.
that does help see what is going on. The variables are local, and I checked "Allow Empty Files", which created the tabs correctly, but with no data returned, even though there is data. The Spool SQL was revealing. The first export had a substitution variable that looked like this:
(SELECT UPPER('%unit_code%') unit_code
FROM DUAL)...
The subsequent actions looked like this:
WHERE... s1.unit_code = UPPER('&x')
even though in the environment variable is the same in both:
Can you right-click on your app and export it, then send it to me?
I am not sure why the substitution is not happening right on the subsequent exports. I just noticed that If I click the dropdown, none of the other environment vars have %'s around them. It might be worth a try to take them out. I know I told you before to put them in. Sorry about that. It was definitely working for me having them in there though.
Here ya go…
(attachments)
The problem is:
If the SQL contains either whitespace at the end of a line or comments before the substitution variable, then when the action runs, Toad fails to perform the substation.
For example, this will fail:
Select * -- hello
from dual
where '&x' = '&x';
and this will fail (you can't see it but there is trailing whitespace after "select *")
Select *
from dual
where '&x' = '&x';
This will work just fine because the comments and trailing whitespace are AFTER the substitution variable.
Select '&x' as data
from dual -- hello, the next line has whitespace
where 1=1
An easy workaround is to use bind variables instead of substitution variables.
For example:
Select *
from dual -- hello, the next line has whitespace
where :x = :x
I will have this fixed for next beta (29-Jul-2024)