I have the workflow below which loops through all files in a folder and outputs to an Excel sheet. Currently, it looks like it is joining all the data from every file in the folder then running the rest of the workflow. The output is correct, but all the data appears in two column total instead of 2 columns for each file. Ideally, I'd like the workflow to loop through each file, create two columns for each of these files, and output the result to a separate sheet. How would I go about doing this?
Solved! Go to Solution.
@user_7346 This is a textbook use case for a batch macro. You would use a directory tool to pass each filename into the macro one by one, and then output the results separately. Below is the training on this.
https://community.alteryx.com/t5/Interactive-Lessons/Creating-a-Batch-Macro/ta-p/657923
You'd create a batch macro, with a control parameter on the file/sheet name.
General information can be found here:
Are you able to upload a copy of your workflow and a couple of the inputs? I can mock a workflow for you.
Hi @user_7346
While you can use a macro, it doesn't mean that you should use a macro. In your case you can meet your requirements with a few simple modifications to your existing workflow. Since you're inputting all the .csv files with single Input tool, make sure that you have "Output File Name as Field" set to "File Name Only".
This will give you a field that you can use in your Output to direct each file's output to a different tab in Excel. In your output tool check "Take File/Table Name From Field" , set the dropdown to "Change File/Table Name". Set the field drop down to the name of the file name field from Input and uncheck "Keep Field in Output" so the tab name doesn't show up as an extra column in your output data
You'll also have to modify your Select tools to ensure that the filename field makes it all the way to Output tool.
I would have attached some screen shots but Community isn't allowing me to upload any at the moment. I was allowed to attach the sample workflow though!
Dan
Hello, I have attached two example files and the workflow. The workflow outputs the screenshot below. The output is in the format I would like; however, the results are stacked on top of each other. For example, I'd like records 1-3 and 4-7 to be on separate sheets in an excel workbook instead of on top of each other. I'd like to be able to do this for at least 10 files.
Hi @user_7346
See attached, I updated the workflow slightly to input the file names as a field (in the input tool), and added an output tool that will break out the data to different tabs based on the file name.