Exporting to Excel, multiple workbooks, some will have >1,000,000 rows

[TeamTJ]

I have a .csv file I'm reading into Alteryx for the purpose of spitting it out into more manageable chunks in Excel.

I already know how to break it into different files from this KB: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-to-separate-Excel-files/ta-p/1247

My issue is that some of my chunks may have over a million rows, and Excel doesn't like that.

I'm stumped as to how to handle these...any suggestions?  

Thanks!

[Claje · Accepted solution]

Can you split the chunks into multiple sheets on the same file?

I've written something like that before, using a similar method to that KB article.

Basically, I used a Multi-Row-Formula to create a new RecordID field.  You can group by the column you're using to break out your files, with the following formula:

[Row-1:RecordID]+1

This will get you an identifier for each row of data.

Then you can create your filename with a formula that looks sort of like

"YOURPATH\"+[YourFileNameField]+'.xlsx|Sheet'+TOSTRING(CEIL(RecordID/1000000))

This will output files to your expected path and filename, and the first million records will go in Sheet1, then the second million in Sheet2, etc.

[TeamTJ]

Took me a while to implement this but most of that time was spent looking for a silly typo on my part ( ' vs " ) but this worked like a champ, thank you VERY much!