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

Alteryx designer Discussions

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

Multiple Excel files to multiple database tables

Atom

Hi,

 

I just started using Alteryx and found this tool amazing! However, I've been struggling with this one task: I have multiple Excel files (hundreds of them) and I want to convert them into multiple SQL Server tables (one Excel for one SQL table). I know how to do this one file at a time, but how can I automate this process? I feel like this is a very common task that a lot of Alteryx users would like to perform, but just couldn't find any resource showing me how to do it. Please help!

 

Thanks all :)

 

-Aaron

Highlighted
ACE Emeritus
ACE Emeritus

So one way to do this would be with a Batch macro.

 

You could use a directory tool to read in your list of Excel files, and then batch the list into a Batch macro.   A batch macro is like a loop with Variables, and in each loop you would simply read in the file and then write the results to a database table

 

Did you need some more specific examples of how to go about this, or did you just want the hint into which path to take?  

 

Let me know if you would like some more information here!

 

 

Atom

Thanks so much for helping out!

Yes an example would actually be great as I never used batch macro before :P

Alteryx Alumni (Retired)

Hi @newbie,

 

If you're lucky enough and all the files have the same data structure (schema) you can accomplish this with 2 tools.

2017-01-20_11-04-46.jpgOverview2017-01-20_11-07-25.jpgInput tool - Options

The Input uses a wildcard to look for any file that starts with "Co Store File" and ends with ".yxdb" within the directory i have it pointing to. I've also set the option to output the file name as a field. * = any character zero ore more times, ? = any character zero or one time.

 

The Output you'd configure normally, but you'd select the option to "Take a File/Table Name From Field" (#1) option at the bottom of the config window.

2017-01-20_11-11-36.jpg

 

#2 has 4 options, but since you want to create new tables within the same database (assuming), you'll use the option shown here.

#3 select the "FileName" field which was created when we told the Input tool to include it.

#4 tells the output tool to NOT include the field selected (which is "FileName" in this instance) in the output.

 

That said, if the schema is different from file to file, than this wouldn't work and the batch approach suggested by @bsharbo would be the way to go, but this process can be utilized within the macro.

 

Hope this helps!

Alteryx Partner

Hi bsharbo, 

 

I am running into the same issue. Mainly how I can update the table name for each of my 600 tables. 

 

Would you have an example macro of how I can update the table name? Do I use %user.variables% ? 

 

cheers!

 

Labels