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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Split excel based on number of rows

viky_s
5 - Atom

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

4 REPLIES 4
Hamder83
11 - Bolide

Hi 

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

Ben_H
11 - Bolide

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
15 - Aurora
15 - Aurora

Hi @viky_s,

 

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

Jonathan-Sherman_0-1617880443976.png

 

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:

Jonathan-Sherman_1-1617880526937.png

 

 

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:

Jonathan-Sherman_2-1617880588314.png

 

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
7 - Meteor

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!

Labels