Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Loop through SQL queries in an excel file to fetch data from a SQL database

Bhavika
8 - Asteroid

I'm new to Alteryx!

Having said that, i'm in a situation where i have an excel file (.xlsx) with 10-20 SQL queries to be executed to fetch data from a SQL database and produce separate excel outputs for each query results. Below is an example of the sql query i the excel file :

Select top 10 'def' as 'DEF', CRP as 'Entity', ACCT, ADJ form CTXAMT_2018

I'm also attaching a screenshot of my approach. But, i am stuck in how to make it loop through all the SQL queries. 

batch macro.jpg

 

Appreciate your help. 

 

Thanks.

17 REPLIES 17
LordNeilLord
15 - Aurora

Hey @Bhavika

 

You'll need to make this a batch macro and have your output tool within the macro.

 

Something like this:

 

SQLLoop.PNG

Bhavika
8 - Asteroid

Hey,

 

Thank you so much for your reply. But unfortunately, my organization has Alteryx designer 11.0 and so i'm unable to open the alteryx package you shared. :(

Can you please help me with any screenshots depicting the way you've configured the tools. Alternatively, is there a way i can open the package in my version of alteryx.

 

Appreciate your help.

LordNeilLord
15 - Aurora

Try this:

 

 

Bhavika
8 - Asteroid

Hi,

 

So the package opened now. Thanks for that. I tried configuring the workflow tool and the macro as per the situation. But, i get an error. I am clueless what i'm i missing.

Also, to get multiple excel outputs (each for every sql query) i tried using the formula tool to formulate the file name. I wonder if i can somehow pass the iteration no. to differentiate the file name so that the excel is not overwritten rather there is one excel file for each sql query. But, unable to figure out. 

Error : 

batch macro error.JPG

Also, i am attaching the workflow and the batch macro. 

 

Please help me sail through this. :(

 

Thanks.

LordNeilLord
15 - Aurora

Hey @Bhavika

 

Ok, so with a batch macro you need to define how it will group...to do this you click on the batch macro and set the values like this:

 

Batch Grouping.PNG

 

If you wanted to add the iteration number you can add it in like this:

 

Iteration.PNG

You could be even more advanced and split apart your select query and then append on the "Top" part and the table name and use to define your workbook name...there are loads of options. It just depends on how much effort you want to put in:

 

Appendname.PNG

Bhavika
8 - Asteroid

Thank you for helping me with this. So, i followed the steps. Thankfully, no errors anymore. But, i wonder why the macro takes like almost 30-40 mins and still doesn't progress from 4%. I fail to understand how the batch macro is working here...i even reduced the query results to "TOP 5 & TOP 2" . But, still no help.

 

Also, i agree to your point that i can make it more advanced. But, as a newbie i'm trying to get a hold first. :) But, i really appreciate your help.

 

I'm attaching the workflow. Could you please take a look. 

 

Thanks again :)

LordNeilLord
15 - Aurora

So the batch macro ensures that it does one query at a time. Dynamic inputs default method is to union all of the outputs together, but we're trying to prevent that here.

 

Make sure your dynamic input has been configured like this:

 

ModifySQL.PNG

 

As I can't access your DB it's hard to diagnose issues but I have set you up a version using an access db

Bhavika
8 - Asteroid

So, though I couldn't open the Superstore file through my MS Access  version which has accdb as default format. But, what I understand is i need to configure the Dynamic input tool to replace the default sql query that i'm passing while configuring the sql database with the SQL column that comes in through the text input/macro input. 


dynamic input config.JPG

 

I did the same. But, still no luck! :( The macro runs for about 8% for almost 45 mins with one output to excel (named the file as 0, which i assume it is the first iteration.number)  with all data form CTXAMT_2018 (select * from ctxamt_2018) so it is executing the default database query and not considering the modify sql query of the dynamic input. 
I wonder if my approach is correct. Please help me understand if i'm going wrong somewhere.

LordNeilLord
15 - Aurora

Hey @Bhavika

 

The only thing I can see that is different is your text next to the "replace specific string" is different to the text in the "Table or Query" window...whilst this shouldn't have an effect it may be causing some issue.

 

When you add the the "Replace specific String" condition, the SQL text should have automatically populated with the SQL you set up...did it?

Labels