Must export data into excel with >65000 rows (Access 2002)

Topic

New Reply

I am working with a 745 meg database with several hundred thousand records in about a dozen tables. I need to export this data to excel but can’t because Excel only allows 65,000 rows per worksheet.
Is there a way to set a report to automatically wrap to the next worksheet when it reaches the 65,xxxth row? If not, can anyone suggest any possible way to get all this data into Excel?
Unfortunately, it absolutely must get into Excel somehow or another ASAP. Can anyone help me???

Reply | Quote

Replies

Dear me – you have a nasty problem. Without spending several hours writing lots of fancy SQL statements and VBA to do transfers to spreadsheets, there isn’t anything you can do to automate the process. You are also going to end up with some really sizeable Excel workbooks. Now you can do multiple worksheets in Excel, and I believe the limit of 65000 applies to each worksheet, though I think there is also a maximum workbook size as well. In addition, for someone to open a really huge workbook, they need lots of memory either RAM or virtual, and it will get really slow.

I would approach it manually by writing a query that let me specify the beginning and ending record, assuming there is some sort of counter on each table, and then export the results of that query to a workbook starting with the first 65000, then the second 65000 and so on. That will be the quickest, though it isn’t very elegant. If you want more details on how to set up a query to do that, post the question and I or one of the other folks on the lounge can give you a hand. Good Luck!

Reply | Quote

… Check out the Microsoft Knowledgebase. There is a way to export in excess of 65,000 rows of data into excel workbooks. Excel will import the data on continuous worksheets. Of course, this is not the best way to do this, but it works.

If you are trying to put the data into excel to transfer to another application, try saving the data as a .csv and import from there.

Reply | Quote

Wow, that’s wonderful news. I looked around on the KB a little, and I think I’m looking with the wrong search parameters. Can you point me in the correct direction? Is it under Excel or Access, and what search term do I enter. OR, even better, I don’t suppose you happen to know the article number?
Rebecca

Reply | Quote

Sorry, I can’t find the post but it was on using Excel. It may have been with a prior version but I used it before to import large quantities of data.

Reply | Quote

Thank you for trying to find it. I actually finally found it (the KB seemed to be down a good chunk of the day on Tuesday). I was able to copy the code Microsoft had on the site and use it. I’m still working on the data. (if anyone wants to humble the abilities of their computer, try exporting a 1.5 million row table with 30+ fields to a text file).
However, I thought I would post the KB article numbers here for the next person that has to face this tortuous task.
There are two. The first one titled XL98: Importing Text Files Larger than 65536 Rows, is Article number .">Q119770.
Thank you to everyone that helped me. My head would still be spinning in disbelief if it weren’t for y’all’s help!

Edited by Charlotte to insert hyperlinks

Reply | Quote