How to export more than the maximum allowed limit of rows to Excel?

0
Hello, I'm currently searching to see what would be the best way to allow 1,000,000 rows to be exported at a time. I've currently set the limit in each of my data grids to 65,535 rows to be exported when clicking the "Export to Excel" button. I've noticed that some individuals go to Mendix Marketplace to download the "Export to Excel" module but I'm looking for a different approach,  if at all possible. I've thought about creating a microflow to export this data in batches in loop until all information has been exported. I've also considered completely changing the export to Excel to exporting to a CSV file. Before I dive into testing I was curious to see if anyone has been in this same situation and would like to share their thoughts on how they approached this situation and what worked best for a request like this. Thank you for your time.
asked
4 answers
1

Hi Michael,

 

You could probably export a million records, but why would you want to export a million records to Excel?  If it's a one time job you could also consider exporting the data from a database dump.

 

Best regards,

Martijn

answered
1

From Data grid 2 you can have an export functionality (calling a nanoflow and within that executing the Export_to_Excel JavaScript action) which exports in XLSX format, so I guess the row limit will be around 1M.

 

Also you might try Excel Exporter module and export to a newer Excel version.

 

I guess the 65535 limit is for older Excel versions and that is what old Data grid Export to Excel button creates.

answered
1

Hey Michael, we are using CSV module - SQLToCSV for exporting around 3 million records and it takes 5-7 secs.

Since, SQL is written with filters (if any) and runs native to database - it exports in very quick time.

 

It comes with drawbacks:

1. It does not follow access rules applied on entities, means you have to add it explicitly in SQL query

2. It is not database agnostic, means query might need to be changed with change in underlying database

answered
0

Hey Martijn, I appreciate the response. 1,000,000 isn't the magical number it's more so just a large number I wanted to put out to see if anyone else has had success in exporting more than the maximum default allowed through Mendix studio. I'm just curious on the different possibilities people may have used to achieve a task like this.

 

Very Respectfully,

Michael 

answered