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

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!

Claje_ExecuteSQLFiles.yxzp