Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston, Massachusetts, USA
    Posts
    205
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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???

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Wendell

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

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

    ... 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.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston, Massachusetts, USA
    Posts
    205
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

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

    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.
    Regards,

    Gary
    (It's been a while!)

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston, Massachusetts, USA
    Posts
    205
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!
    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Edited by Charlotte to insert hyperlinks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •