Dump Database Tables to Excel Tabs

unityindigopwc

I'm trying to dump all database tables via sql queries to a single excel file with a new (to be created) tab for each table. I have a list of the tables so far and a connection to the database. I'm fairly certain this is possible, but haven't found the right combination of tools and settings after some time. Any help would be greatly appreciated!

danilang

Hi @unityindigopwc 

Here's a workflow that will connect to any SQL database, extract the data from a list of tables and write the results to separate sheets in an excel workbook.  As @MichalM alluded to in his post, if the schema of the tables are different, which is the case in every SQL data database, you can't use a standard workflow to extract the data.  This is because Alteryx will read data from multiple tables in a standard workflow, but as soon the schema differs from the 1st table, the input tool will skip the table.  To get around this, you'll need to use a batch macro.  

The batch macro will run once for every record that's passed in to the control parameter, but the runs will be completely independent.  This particular macro uses the control parameter to replace the tablename that's in the Text Input tool.  The Dynamic Input tool is configured like this

You'll need to change the connection to connect to your actual DB.  Leave the Query the way it is, "Select * from _TableName_".  You'll need this because the Dynamic Input does some modifications to this string

 

It replaces the "_TableName_" with the value that was set by the control parameter in the previous step.  It's also set up to pass the tablename field to use in the Output tool.  The invalid SQL string "Select * from _TableName_" is the reason the Dynamic Input tool displays an error in the Macro.  There is no table called "_TableName_" in my DB.  When the macro runs as part of a workflow, the SQL statement will be changed to "Select * from dbo.User", if you pass in "dbo.User" as the table name, and the SQL will execute correctly.

The Output tool is configured like this

The file name is standard stuff.  Change it as you see fit.  The customization is at the bottom.  "Take File/Table Name from Field" is checked.  It's set to "Change File/Table name" and the field name is set to "tablename" 

After this the main workflow is quite simple

"Tables names" just contains a list of the tables you'll want to query.

This solution has no safeguards whatsoever.  If you pass in 100 table names and return 2000 TB of data, that's what the workflow will attempt to write your excel file, corrupting it, crashing your hard drive and causing your DBA to send you a nasty email.

Dan

Main.yxzp