This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!
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!
If you're lucky enough and all the files have the same data structure (schema) you can accomplish this with 2 tools.
OverviewInput 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.
#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.