Split excel based on number of rows

[viky_s]

Hello

I believe this is a simple question but I am new to Alteryx and have some problems.

I have an excel file with 365 000 rows (only one column), I need to get 37 separate excels with 10 000 rows in each maximum.

I tried to use Sample, but the only decision I managed to create is too messy

[Hamder83]

Hi 

I'm not sure this is the easiest way, but I guess it works. Let me know if knot 🙂

[Ben_H]

Hi @viky_s 

You could add a recordID to your data then create a group column with a formula tool using the following formula.

ceil([RecordID/10000)

Next do as @Hamder83 has done and use that value to construct a new output file name.

Regards,

Ben

[Jonathan-Sherman]

Hi @viky_s,

Here's a neat little trick many forget about. In the output tool, option 1 in the configuration pane:

I've set my value as 2, therefore Alteryx will output separate files, splitting my output into a maximum of two rows per output.

Let's say I have an input of 5 records:

The output with a maximum records per file of 2 (as the above example) would lead to three output files being created, with 2 records, 2 records and 1 record respectively:

This method will be fully dynamic also if the number of records to output changes. If your records increased to 25 you'd output 13 files, 12 of which would contain 2 records and 1 would contain 1 record.

Hope this helps! I've attached my workflow for you to download if needed.

Kind regards,

Jonathan

[athina]

Sorry to be reviving an old thread! Hope that's ok!

Just wanted to ask how would I go about splitting on rows, using the neat little trick you have kindly mentioned, but have Alteryx spit the data into one Excel with multiple sheets? (as opposed to separate files)

I have successfully used the Output tool to create a multi-tab Excel, or to split on x rows - haven't managed to do both at the same time haha!