Exporting Excel Workbook to Multiple Text Files Based on Formula

[knwoke]

Hello!

I would like to design a macro that will split an Excel workbook with 3 columns (Name, Account Number, Occurrence) into multiple text files. The first column (named Occurrence) contains a formula which assigns a number (an occurrence) to each row of data depending on how many times the name appears in the file. I would like to create a Macro which will create a separate text file for each Occurrence number. 

For example:

Occurrence Name AccountNumber
1 Bob 1234
2 Bob 1235
3 Bob 1236
1 Tom 1234
2 Tom 1235
1 Jim 1234

I should get three files:
File 1:
1 Bob 1234
1 Tom 1234
1 Jim 1234

File 2: 
2 Bob 1235
2 Tom 1235

File 3: 
3 Bob 1236

Thanks in advance for your help

[MichaelSu]

Hi @knwoke ,

Please check out this article: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Outputting-Multiple-Excel-Tabs/m-p/446...

In order to output to multiple tabs dynamically based on data within a column, please ensure the 'Take File/Table name from field is checked and Change File / Table Name is selected in the Output Tool. In your case, in the image below 'City' should be replaced with 'Occurrence.' Otherwise, all else should remain the same.

Thanks,

Mike

[ConnorK · Accepted solution]

Hi knwoke,

No need to create a macro. This functionality is already built into the join tool. Start off by just connecting an output data tool. Then when you browse to create a new output file, choose ".csv" extension. Once you choose the .csv extension you will then want to input ".txt" at the end of your new file name... see below.

Then once you create this file you will need to navigate to the bottom of the "Output" tools configuration. At the bottom you can choose "Take File/Table Name From Field" and "Append Suffix to File/Table Name". Once you choose the occurrence field you want this based on you will get three separate files based on occurrence. See below...

I have also attached a packaged workflow for reference.

I hope this helps!

[knwoke]

Perfect! Thank you @ConnorK and @MichaelSu for your assistance. 

[knwoke]

Is there also a way to replace the Occurrence formula in Excel into Alteryx? The formula I currently have is =IF(COUNTIF(D:D,D2)>=1,COUNTIF(D$2:D2,D2),"") it assigns a number next to each row depending on how many times a name appears in the file.

[ConnorK · Accepted solution]

Absolutely, please take a look at this tool mastery article for the Summarize tool. https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Summarize/ta-p/24944

In the Summarize tool you will want to group by the Occurrence and then count the amount of of values within that grouping. This will be done in the summarize tool.

[knwoke]

Thanks again!