Split excel based on number of rows

Question

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

Untitled.png

athina · Answer

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!

Jonathan-Sherman · Answer

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

20210408 - MaxRecordsPerFile.yxzp

Ben_H · Answer

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