Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Output to another sheet when exceeding max row count in Excel

Highlighted
7 - Meteor

Good afternoon! 

 

I have a workflow that outputs to several .xlsm files, and some of the files have just over 1 million rows, exceeding the Excel limit. When it reaches a file that exceeds the limit, it doesn't output anything else after it. How can I make it so when the rows exceed the max it outputs to a different file or tab? 

 

I got a sum tool that counts how many rows there are, and can edit the filename, but if I just add a filter to change the filename variable to +1, it'll just cap out at the same spot under a different filename. How do I get it to put the extra rows onto another sheet or workbook?

 

Exceed max rows question.JPG

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @cdahl001 ,

 

I'm attaching an example where I generate an excel file with multiple sheets based in the number of lines.

fmvizcaino_0-1582168919539.png

 

Take a look and let me know if it is that what you were looking for.

Best,

Fernando Vizcaino

 

Highlighted
7 - Meteor

Hi @fmvizcaino

 

Can you post a screenshot of what you selected in the tile tool configuration pane? I can't download workflows due to company computer restrictions. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hello @cdahl001,

 

I applied this formula to have the sheetnames changing:

 

Untitled.png

 

You only need to switch the 100 to 1000000 and that would be it to have the name chainging.

 

Untitled.png

After that you need to switch the ouput and click on the "Take file name from field" and select the field (in my case Filepath)

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @cdahl001 ,

 

fmvizcaino_0-1582208209996.png

Each line of the cutoffs would be the number of max lines, max lines * 2 and so on

 

Best,

Fernando Vizcaino

 

Highlighted
7 - Meteor

I appreciate the input, but we've yet to come to a solution. 

 

If I just set the sheet name to change every 1 mil rows, it'll output with odd sheet names. D1 will have Sheet1, D2 will have Sheet2, ......... D21 will have sheet21, etc. It looks strange to open a document and see only one sheet for that department called Sheet56. There are only 2 or so departments that have over 1 million rows that I need output.

 

Here's what I've got now at the end of the day. It still doesn't work, but I took inspiration from both of you and feel like I'm closer. 

 

How do I get the RecordID to set back to 0 when the Department# changes? If I could do that, it would work with what I've got below. Right now all it does is set recordID to 0 the row the department changes, then goes back to counting what it was originally at, which messes up the sheet naming.

 

 

 

Multi Row function (updates recordID): IF [Dept] = [Row-1:Dept] THEN [RecordID] + 1 ELSE [RecordID] = 0 ENDIF

Formula Tool 1 (creates Sheet# field): IF [RecordID] > 1000000 THEN 'Sheet2' ELSE 'Sheet1' ENDIF

Formula Tool 2 (creates Filename field): Filename = "\\...\Item Alignment D" + ToString([Dept]) + ".xlsm|||" + ToString([Sheet#])

 

 

Exceed max rows question.JPG

Labels