community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

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

Meteor
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.
Magnetar
Magnetar

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!

Meteor

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.

Meteor

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!

Meteor

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 :).  

Labels