Execute all SQL files in a folder and output results to Excel

[jmstampe]

Good day,

I am completely new to Alteryx (on 11.0) and just started really diving into it a few days ago. I have .SQL files that I run and update regularly. I'm playing around with creating a workflow that does the following but keep hitting road blocks:

1. Point to the folder where all the SQL files exist
2. Take the SQL from the first file
3. Execute the SQL on a Database Server
4. Output the results in an Excel file
5. Repeat steps above for the rest of the SQL files in the folder
6. Output the results to the same Excel file in different worksheets.

I don't want to have to update my sql querying in multiple places (SQL file and Alteryx) so I'm trying to use the directory tool with my file specification as *.sql then have it run through the steps above using an iterative macro. Problem is I'm not sure exactly how to best set that up and was wondering if there was an example out there to achieve this.

[Claje · Accepted solution]

Hi,

The attached workflow isn't exact to your example (it currently outputs to individual workbooks) but you should be able to tweak it relatively easily.

This workflow takes advantage of a Batch Macro to execute SQL against a specified server.  You will need to update the macro so that it points to the correct SQL Server.  It then outputs based on a filename which is passed in from the workflow.  You could tweak this so it only updates a sheet name, or tweak the formula so that it creates the sheet name but keeps the filename consistent.

I hope this helps to explain the process/concept!

[jmstampe]

Thanks for the help.  Unfortunately, I'm unable to open it to look.  I'm on version 11 still and I'm guessing you may be on a more recent version.

[jmstampe]

Ah, nothing a little xml editing won't fix . . . ha-ha.  I got it to open and taking a look.  Will hit you up with any questions if I have any.  Thanks again!

[jmstampe]

Awesome, I got it working, for the SQL execution at least. After playing around with it I realized in the input data node you need to add a generic sql statement and in the macro action node set the replacement of that generic sql statement to the one your batching in.

Much appreciated....now to work the single Excel workbook package :).  

[NikhilVedula]

Hi Jmstampe,

Can you make your solution a little bit clear, i mean can you please help us with the steps that you have followed

to make the sql queries run, Any reply for this would be really appreciated.

Thanks in advance

[jmstampe]

Sure, I’ll see if I can find the original macro workflow I created for this and try to post some screen caps. Unfortunately, due to line of work I’m unable to send.  

I think I ended up going a different route on the project and loaded all my scripts to input tools as I only needed to run 8-10 and some overlapped in data/fields.  Still a cool workflow setup regardless.

[jmstampe]

I can’t seem to find the macro but I’ll keep looking.  In the meantime it sounds like you would want to use a directory tool, to get your list of sql scripts then  create one batch macro to drop the scripts into a table and another batch macro to read each script in either a dynamic input or normal input tool (replacing the table or sql with your sql in the actions menu).

I’ll keep looking though.

[NikhilVedula]

Yeah, Thanks a lot for your help!

I have done the same as you suggested, i have created a directory tool and placed all my SQL files in it and 

fed it to the batch macro and that seems to be good,i'm able to run my SQL files one after the other dynamically

with the help of control parameter tool followed up with the update tool.

one more question would be can i run my SQL quires all at a time instead of running it one by one.

If possible could you help me to get this.

Thanks in advance.

[jmstampe]

Great news!  I’m glad that worked.  

As for running all at once, I’m not aware of Alteryx being able to do that unless you were to create one combined query from all the scripts and loaded that into an input tool.  That’s one of the reasons why I ended up going a different route with my original workflow was that I was able JOIN and UNION a lot my queries together dropping the amount scripts I needed.  If you’re good with SQL I say go that route.