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!

[MichalM]

It is indeed possible. Could you please share some more detail on the database and the tables? What database do you want to connect to? And do the tables have same schema (column names and column numbers)?

[danilang · Accepted solution]

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

[unityindigopwc]

I'll check this out, thanks guys! It's a OLE DB connection to a MS SQL Server v11 with varying database tables/ columns. So yes I think the macro is what I will need. I'll check it out tonight!

Also while this is a production database, I don't believe the database size to be very large as I normally use Qlik to access the most data-heavy portions of it on the fly within less than a minute. Nevertheless, I understand your caution. I'll try it on a smaller portion of the database first just to be safe. Thanks again!