Exporting Excel Workbook to Multiple Text Files Based on Formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Macros
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Senior Sales Engineer
Alteryx
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Perfect! Thank you @ConnorK and @MichaelSu for your assistance.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Senior Sales Engineer
Alteryx
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks again!
