Exporting large number of records to several sheets in one excel file

AyeshDilshan

Forums

Hi All,

I have a large number of transactions (around 5 million) and would like to export them to an Excel file. Since Excel has a limitation of nearly 1 million records per sheet, I need to export the records in chunks of 1 million into separate sheets manually.

Do any of you have a script that can save all the transactions into several sheets within the same Excel file, breaking the transactions into 1 million per sheet?

I look forward to your support.

Regards.

Brian Element Thu, 02/20/2025 - 15:50

Hi Ayesh,

I have attached a script that will allow you to create multiple excel files for IDEA files with a large number of transactions.  Let me know how it works out.

Brian

AyeshDilshan Tue, 02/25/2025 - 03:29

Hi Brian, thanks for your prompt response as always.
When running your script I'm getting an Access Denied error in line 162 "task.PerformTask dbName, "Database", "XLSX", iStart, iEnd, eqn"

could you guide me to solve this please.

thanks.
Ayesh.

Brian Element Tue, 02/25/2025 - 11:58
Hi Ayesh, I think the problem is I had hardcoded the variables names in when I was doing the development and testing and forgot to remove them when it was complete. So it worked on my computer but would give you an error since you didn't have the same files. Here is an updated script that will hopefully work for you.

Brian Element Tue, 02/25/2025 - 16:59
Can you give me some info, like which version of IDEA you are using, your file size, how many rows you are breaking it into, I am not sure what the problem is so just checking if this info will give me any insight in what might be going on.

AyeshDilshan Wed, 02/26/2025 - 06:40

Hi Brian, thanks for your reply.

I'm using IDEA Version 12.2 and my file has 1.7 million records. I'm breaking the file into 900000 records each.
Let me know if you need any further info.

Thanks.
Ayesh.

Brian Element Thu, 02/27/2025 - 06:53

Hi Ayesh,

Ok, I figured out the problem, looks like it is probably because the folder I am saving the file to doesn't exist on your computer.  I have added an option for you to select the folder that you want the files to be saved to and this should hopefully fix the problem.

Brian

Files
Split large files for excel_1.iss (6.24 KB)