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

[Bhavika]

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. 

Appreciate your help. 

Thanks.

[Bhavika]

Also, i created a sample access DB and tried using that instead of the server. It runs through now. However, the batch macro is creating different excel outputs with the default query used in pulling access DB. The 'Modify Sql Query' configuration doesn't work.

I wonder if i need to take a different approach. :( 

Attaching the access DB, modified macro and workflow.

[Bhavika]

Also, yes the SQL text automatically displayed under the 'Modify SQL' section.

But,i wonder when i'm pointing it to the access DB, the dynamic input tool doesn't pick up the replaced SQL string. 

I'm unable to attach the access DB here. So, attaching the screenshot of the data in CTXAMT_2018 

[LordNeilLord]

You're mdb wasn't included so I made a new mdb and used your extact workflow. The only thing a changed was to remove the current "replace specific string" and to add a new one.

All files attached for you.

[LordNeilLord]

Try removing the current "replace existing string" and adding a new one back in (after you've updated the db connection)

[Bhavika]

So, changing the 'Modify SQL' section works well and i get desired output in excel using access DB. thank you for that.

However, when i apply the same (recreating the entire package) to the server connection the macro takes long.(stays on 4% for almost 30 mins) with no output. :(
Do you think it could be because of the server connectivity? If yes, will there be an alternate faster way to make it work sql server tables. Should i try In-DB tool instead of dynamic input?

[LordNeilLord · Accepted solution]

Yay!!

I'm glad we got there :)

Do you usually find connecting to your database through alteryx is slow?

I also notice that you are using OLEDB to connect, can you use ODBC instead?

Alternatively yes! Try the Dynamic Input InDB and see if that makes a difference

[Bhavika]

Hey..thanks for the suggestion eith ODBC.
It worked for me now. :)

[Devaraj_Manimaran]

Hi, May I ask what does it mean when it says Error: Dynamic Input (2): No table chosen; Please select a Table from data source.