Iterate through files individually

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?

Workflow

Accepted answers

Luke_C

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.

Active Workflow.yxmd

All comments

Luke_C

@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

echuong1

You'd create a batch macro, with a control parameter on the file/sheet name.

General information can be found here:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/ta-p/488165

Are you able to upload a copy of your workflow and a couple of the inputs? I can mock a workflow for you.

afv2688

Hello @user_7346 ,

I prepared a little example for you:

Regards

merge data.yxzp

danilang

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

NewTabsInSameFile.yxzp

user_7346

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.

Test Data1.csv

Test Data2.csv

Active Workflow.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703