Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Split Output Into Multiple Files with Specific Record Counts

DanC
Moderator
Moderator
Created

How To: Split an Output into Multiple Files with Specific Record Counts

Sometimes, you may want to write data to multiple files, with each file containing a specific number of records. You can leverage a Record ID column to do exactly that!

Procedure

  1. Add a Record IDtool at the end of your workflow and leave the default settings

  2. Add a Formulatool
    1. Add a new field called 'GroupID' and change the type to Int32
    2. In the Expression box, type in the formula below,replacing 25 with your desired number of records perfile
       CEIL([RecordID]/25)
  3. Add an Outputtool
    1. At the bottom of the Configuration window, check the box that says 'Take File/Table Name From Field'
    2. Leave the option 'Append Suffix to File/Table Name'
    3. Select the GroupID field in the 'Field Containing File Name...' and choose whether or not you want to 'Keep Field in Output'

A sample workflow is attached.

Attachments
Comments
yashd
8 - Asteroid

Big relief,  1000 STARS to this post.

Arvi_Singh
6 - Meteoroid

This is helpful. Thanks !

 

KylorI
Alteryx
Alteryx

This just saved me a bunch time @DanC!

 

 

DanC
Moderator
Moderator

Great, @KylorI! Glad to hear it!

Meena
8 - Asteroid

Hi... Appreciate for this time-saving solution.

 

In addition to this I want to split the records into separate sheets based on a record count with a dynamic file name.

 

For example I have records which need to go to different files based on an input column, say City

(each file will have separate sheets with the # of records I set using Group ID).

 

I already use a variable FileName which has "C..\.."+[City] which I use in Change Entire FilePath.

Within each city how to split into different sheets based on Group ID?

arce88
6 - Meteoroid

In my project I have a column of the name of individuals, these individuals each have a different number of flags.

Instead of putting 25, how do you go about putting the number of flags each person has correspondingly?

CJShyne
5 - Atom

Hello,

 

I have a bit of a twist on this.

 

I have a summarized data set that shows an account and the number of transactions they had. My goal is to split this set of 90,727 accounts as evenly as possible based on 2 factors; # of Accounts as well as sum of transactions for the set.

 

Not sure of how to go about doing this. One of these variables is obviously easy but splitting as evenly as possible based on record count and sum of their transactions I am unsure in a perfect world based on my data the breakdown would be 3 sets of ~30242 Accounts and ~5,886,395 Transactions as I have 90,727 Accounts and 17,659,185 Transactions between them.

 

Thanks!

 

 

HangliderUK
5 - Atom

Thanks @DanC ! Saved me a lot of time.

mdgajes1
7 - Meteor

I have a similar problem. Need to split my data into separate files by the state field. This would be easy except each file needs to have a header and trailer record.  The trailer should contain a count of records within the file.  Not sure if this is possible but would like to avoid saving the workflow 50 times to account for each state.  Currently the workflow builds a file that looks like this.  

 

Example:

HDR#############

BDY#############|| CA

BDY#############|| NY

BDY#############|| AL

TRL#############[count]

 

Any suggestions?