Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Importing > 64K records

    Out of curiosity, what do other people do when you have to import a text file and it turns out to have > 64K records? Excel pulls in the first 64K records, then stops and gives you a warning message.
    I think that 1-2-3 (for a while now) has handled large files OK - by the simple expedient of moving to a 2nd. (3rd., etc.) sheet. After finding out the problem with Excel 97, I just assumed that it would be "fixed" in Excel 2000. Not so, however. Maybe in Excel 2002?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Importing > 64K records

    I resort to Access before I get anywhere near 65000 records.

    As Excel has a maximum capacity of 65536 (=64k) rows per sheet and each row represents one record the limit is 65563 records.
    Incorporating the data into more than 1 sheet would make any manipulation of it very difficult.

    Prior to Excel 97 the max was 16k !!!

    regards

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Importing > 64K records

    Thanks. I was actually trying Access while I was waiting(!). I do still need to get the data into Excel, though. This should really be in the Access forum, but how do you write a query to select just the first 65000 records? Actually what's probably trickier is how do I extract records 65001 to 130000 (etc.)? Thanks...

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Importing > 64K records

    Hi Colin,
    Do these records need to be in a particular order? Access doesn't really have record numbers as such - does your original data have any sort of numeric sequence in it that you could use? If not, you could assign an autonumber after importing to access and use that to sort - as long as it's a one-off you shouldn't encounter any of the pitfalls of autonumbers.) Failing that, you could use automation to select all the records and then programmatically populate the spreadsheet, while keeping track of the rownumber so it knows when to start a new sheet. If you don't actually need all the records to show in the spreadsheet (e.g. you want a summary) I'd use Access to manipulate them and simply retrieve the answers to the spreadsheet (sorry if I'm telling you something you already know! [img]/w3timages/icons/grin.gif[/img] )
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Importing > 64K records

    The autonumber idea sounds good. When I first opened it in Access it didn't offer me the choice - I think because it was somehow a linked file(?). I just imported it into an existing database, though, and that works fine.
    What I then have is approx. 127,000 records that I want to manipulate into 2 sheets of an Excel workbook. I can create a query to select just the first 64,000 records (using the autonumber field), but if I then use "Office Links" | "Export to Excel" it creates an Excel 95 workbook, I think, so I would only get 16K records(?). Not sure about this last part... that's what I just tried and Access is now frozen! If I'm right about the XL95 part, presumably there's a better way to accomplish what I'm trying to do?
    Thanks for your help.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Importing > 64K records

    Colin,
    Access2000 seems to create a spreadsheet of 64k rows (just tested as I'd never really thought about it before!). Perhaps your machine is unhappy about dealing with that many records at once? (or maybe Access doesn't handle the operation terribly well - I've never had to do anything on that scale) Automation might be a better bet.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Importing > 64K records

    You could import direct into 2 or more Excel sheets using the import wizard. After Excel imports the first 64k, do a 2nd import using the wizard telling it to start import at the row following the last one imported first time, say 65537. You then have a second workbook from which you can move the worksheet into the first workbook.

    You will then have all your data in Excel without the use of Access.

    regards,

    Andrew C

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Importing > 64K records

    Nice try. You would think that would work, but the field where you specify the record to start at must be internally defined as an integer (I would guess). In any event, entering 65537 generates an error message to the effect that the number is not valid. Thanks anyway.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Importing > 64K records

    OK, never tried it with so many records so did not realise the limitation - sorry.

    One last desperate suggestion - have you an easy way to remove the first batch records once they have been imported - say in Wordpad ? or some other way to split the data file ?

    Something to explore anyway.

    AC

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing > 64K records

    Have you thought about doing your calculations in Access?

    FWIW

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing > 64K records

    The answer has to be to split the source data and then do separate imports. e.g. in Access make two tables, onew with the first 64000 records and another with then next lot. Then use analyse to export these tables to excel to different spreadsheets.

    If you must!
    David Grugeon
    Brisbane Australia

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Importing > 64K records

    Thanks to everybody for your help and suggestions.
    I do realize that with that many records, I really should use Access rather than Excel to manipulate the data. However, a (fairly complicated) process is already set up in Excel to manipulate and process the data. So rather than try and rewrite the whole thing in Access (which I'll do one day) (maybe), I've been focusing on getting the Input data into a couple of Excel worksheets.
    Using Rory's suggestion of letting Access create an autonumber field, I'm now OK. For some reason, my installation of Access is somehow configured so that the "Analyze It with MS Excel" function creates an Excel 5-7 format file - hence the 16K record limitation. Rather than use that, though, I just tried using File|Export instead, and with that you get to choose the file format.
    So, problem solved...
    Thanks again.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Importing > 64K records

    While you apparently are ok now from your last post, and you've been given a number of solutions, you might also want to consider an array-oriented language as another option. The one of choice that I would suggest is APL. This computer language also has an interface to Excel. I'm working on a large financial model for a client and doing the modeling in APL because of the size issue, performance, and sophistication of the math in APL -- then the summary data goes to Excel.

  14. #14
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Pune
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing > 64K records

    Access is more stable at these levels of records; further, you can always use SQL Queries to import selected data into Excel and also create Pivot Tables. Pivot Tables are the hammer with which you can hit every problem-nail.

Posting Permissions

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