ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

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

Hamder83
8 - Asteroid

Hi 

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

Ben_H
10 - Fireball

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

 

 

Labels