Is it possible to export or download a large dataset of more than 3 million records to an Excel or CSV file? The maximum number of rows in an Excel sheet is 1,048,576. And If so, Could you provide a sample or any suggestions? I am using OutSystems 11 Reactive Web.
Hi @ROMMEL SIMUNDO ,
1) you are answering your own question, the maximum is 1 mio.
2) A sample of what exactly do you need ? do you not understand how to export things to excel ? Do you have trouble coming up with a solution to deal with the size limits ?
3) For anybody to help, maybe it is interesting to also explain why you even want to put 3 mio records in an excel
4) If I would have to guess, I'd say the limit is one of excel as a tool, not the file
Dorine
Thank you for your response. Our client has requested to retrieve all records Transaction, which amount to millions of records due to banking Regulatory. Can you recommend how we can fulfill the client's request compliantly?
Oh no, I can absolutely not tell you how to 'fullfill client's request compliantly',
(and wouldn't even want to, thats a 'paid engineering or consulting job' kind of thing, not a 'help the community with technical questions' kind of thing)
I just did a quick test of this assumption
using RecordListToExcel, you can't go above 1048576, so that's not an option
If it's just a matter of exporting, why does it have to be in a single file? You can export to separate files. Other options is to create a database clone provided its on premise ( btw you need to know which tables stores what data inside the database per applications).
I've considered this idea but I'm unsure how to do it. Can you assist me in providing an OML sample for download as a separate file if the total count exceeds one million records?
My suggestion is using advanced query to get the data in CSV format directly from the database in batches and store this in a Result table ( Id, Start, End, Data(binary) ) and download each of them as individual files. if required you can combine it using a Powershell script.
Sample SQL query you could consider is
SELECT @CSVResult = @CSVResult + CAST(Id AS NVARCHAR) + ',' + FirstName + ',' + LastName + ',' +Email + CHAR(13) + CHAR(10) FROM Employee ORDER BY Id OFFSET @Offset ROWS FETCH NEXT @BatchSize ROWS ONLY;
this will return a single string of all the records ( as per batch size) from the Offset. You may tweak the values of BatchSize depending on the configuration of your server.
You may create simple page to enter the offset and batchsize or automate it with a timer. Once completed your CSV's will be in the Result table which you can download individually and combine if required.
Hi,
export to an csv file and let the client import it.
Like you said, its out of range for an xls file.
Best regards.
In my opinion, you should consult your client and give a better solution.
files doesn't need to be over millions record. We can seperate the files by year, quarter or month. this will also be more effective for error handling when importing or tracing the record.
Indeed, that's correct. We've applied a filter based on the start and end dates, but the volume of transactions within a single month exceeds one million.
I would go on exporting by quarter, its more manageable and easier to track down if any problem arrives.
CSV files don't have a strict row limit, but the file size may become unmanageable or slow to process if too large.
In that case, break the month in half's, I bet that would be the best solution in this particular case.