Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    76K+ records (Excel 97 +all others)

    I have a text file with 76885 records containing 6 columns of data, but an Excel worksheet has only 65536 rows. <img src=/S/heavy.gif border=0 alt=heavy width=40 height=34>. This spreadsheet would be circulated to around 24 outside parties who will be using various versions and operating systems.

    I realise that I can create two sets of 6 columns in one sheet, two worksheets in one file or two files but if I can create a single one this would be a lot better for the users.

    Any ideas? <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 76K+ records (Excel 97 +all others)

    If the file is going to be used in various versions and OS'es, why not leave it as a text file. Most programs on most OS'es can open text files with fixed width columns and with separators.

  3. #3
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 76K+ records (Excel 97 +all others)

    If you are dealing with this many records, I would recommend you bite the bullet and use a database program such as Access or Paradox.

    Regards

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 76K+ records (Excel 97 +all others)

    Excellent (no pun intended Charlotte) idea Hans! In fact I first asked the 24 users what format they wanted. Fourteen said text, six said excel and four said both! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>.

    OK - I have 41% of my customers (whatever happened to the 80-20 rule) that want an excel file <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> and I suspect that at least half of them would struggle trying to stick 76K+ records into a worksheet. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> The error message you get tells you what happened but not why. <img src=/S/shocked.gif border=0 alt=shocked width=15 height=15>

    As for Access or Paradox! Well this is a paradox because I don't have either application <img src=/S/compute.gif border=0 alt=compute width=40 height=20>!

    But I guess the bottom line is that you cannot have more than 65536 rows in a spreadsheet.

  5. #5
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 76K+ records (Excel 97 +all others)

    The 65536 truly is a magic number. While you can split the information over multiple worksheets, it then makes it more difficult to do anything useful with it.

    Regards

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 76K+ records (Excel 97 +all others)

    The limit of 65,536 rows is a hard limit and there is no way to exceed it. If some of your customers have older versions of Excel (95 definitely, don't know about 97), then their limit is going to be 32,768 rows. You have listed all of the things that can be done without going to a database program.
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 76K+ records (Excel 97 +all others)

    Excel 97 limit: 65,536 rows.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 76K+ records (Excel 97 +all others)

    Just as another suggestion...

    You could try a text file and Excel.

    Use MSquery to retrieve the records into Excel.
    This still has the limitation of 65536 rows in Excel, however if only a filtered version is shown, it may be sufficient.

    Note that this will incur a significant speed trade off as the text fiel will have no indexing to help the querys.

    HIH

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 76K+ records (Excel 97 +all others)

    Prior to XL97: 16384 rows
    Quattro pro: some odd million rows....

    You can have both text and an excel file if you make a query that searches the text file for records one needs.... (Data, Get External data).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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