How to output 2 Million records in excel

Hello,

I have a workflow that generates an outuo with more than 2M records. I used "RecordID" and then a formula to create "sheets" in the excel based om records, in this case 1M per sheet. This report is taking 29 min to execute. If I remove the splitting functionality this report execute in 2min. Do you know any other way to split the data bases on amount of records that can work faster?

Any help would be much appreciated.

Thanks,

Sara

1548EF16-64F7-4E67-BF28-7999DBB6237F.png

Workflow

Dynamic Processing

Accepted answers

All comments

PanPP

Hi @saralisset

Excel has a hard 1M row limit per sheet.

You can try to use the Block Until Done tool for the 1M records per sheet to see if the performance will be impacted.

You can also try to enable AMP engine to see if performance will improve.

Hope this helps.

apathetichell

As a reminder - Excel is not a database. It's a spreadsheet. There is never a case to output 2mm rows to Excel.

Having said that - add a record id tool. create a partition using something like ceil ([RecordID]/1000000) - add this column to your filename so that you now have a fully formed filename like "myincrediblelargeexcelfile_"+tostring([partition])+".xlsx|sheet1" - now use this field in your output data tool under "change entire path" - your error may be in your selection in your output data. Your filenaming convention seems reasonable.

NOTE - I'd use a number below 1,000,000 like say 700,000 because performance really starts to drag on larger excel files.

Did I mention that excel is not a database and you should not be storing your data there? perhaps you can look at a nice csv file - or json?

also your screen grab is your sql error. You need a datasource named in odbc64 and a configured driver. Configure/text in ODBC 64 and then add it to Alteryx.

saralisset

thanks for answering. Your solution is exactly what I did… I’ll try with 700k but I don’t think that will help. I debugged it and performance issue is because RecordID. Any other idea?
thanks anyways!

apathetichell

Hi - can you share the error message? Assuming you have a normal size amount of ram (16gb or greater) - I highly doubt the issue is RecordID. If it is in performance profiling - turn off AMP. I just generated 100,000,000 rows. I ran RecordID. I created a partition. It took 21.6 seconds.

apathetichell

Can you confirm there is only one record in the your text input which you are appending? Can you share more of your workflow? Are you by any chance running it on a Mac in a VM setting?

saralisset

Oh no. I missstated, I think the error is the splitting of data in different tabs. How do you output that 100M records? CSV file ?

qurstions:

RAM in my personal computer? Or the server? What would be the minimum required for a report like this?

What is AMP? Where should I turn it off

how do you create a partition?

saralisset

Yes, that only is replace by the location where the output will be save

saralisset

Running in VDI windows.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703