Dynamically generate Excel sheets and output a set number of records to each

[jtleryx]

Hi, I was wondering if below is doable.

I get a set of data every month with different record counts but always in the millions. After processing/calculation, I want to output all the data to Excel; however, because Excel sheets get slow when containing large amount of data, I want to limit each Excel sheet to 500,000 records. For example, if my initial record set has 1.7 million records, i would output them to four Excel sheets with 500,000 records each in the first three sheets then 200,000 records in the last sheet. If next month my initial record set has 2.4 million records, I would output them to five Excel sheets with 500,000 records each in the first four sheets then remaining 400,000 records in the last sheet.

Currently every month, I manually adjust the number of outputs/Excel sheets after doing a record count on the initial data set. Is there a dynamic way to do the above, so I can pass this on to a user that can just click and run without really knowing Alteryx at all?

Thank you.

[Bren_Spill]

Hi @jtleryx - you could use the Tile tool to tag each record with a tile number that will represent the sheet name. See configuration below - you would need to update the cutoffs to be 500,000, 1,000,000, 1,500,000 etc.. Then you can use the Take File/Table Name From Field option in the output tool to write to sheets based on the tile number/sheet name

 

 

[Bren_Spill]

@jtleryx - another option is an iterative macro to do the sheet name tagging. More complex set up but will allow you others to update the number of records per sheet more easily than in the Tile tool. Same setup in the output tool.