Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Middletown, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excluding data from a large file to fit (Excel 2000)

    How can I select the data to be imported into an Excel sheet? The data exceeds the 65K size limit on the number of rows. The prompt indicates that I can continue to import the data in a new worksheet and exclude the data that have been imported but I could not figure out how to do that.
    Any help would be appreciated.
    Thanks

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

    Re: Excluding data from a large file to fit (Excel 2000)

    Import the first group into the first sheet. Go to a new sheet and import again. In the first Import Wizard dialog box enter the row number of the next row to be imported in the spot the arrow is pointing to below.
    Attached Images Attached Images
    Legare Coleman

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excluding data from a large file to fit (Excel 2000)

    Hi Legare,

    Microsoft also developed a solution for this back in the days of Excel 5, which was limited to 16384 rows/sheet. Their solution involved the use of a macro that automatically added new sheets as needed. KB article Q120596 refers. The macro example there could easily be converted to accomodate the 65536 rows allowed in later versions. One limitation noted in the article was that the example macro did'nt parse the data into columns, so you'd need to modify it to do that or use the Text To Columns command on the Data menu to do the parsing.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Lounger
    Join Date
    Feb 2001
    Location
    Middletown, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excluding data from a large file to fit (Excel 2000)

    Thanks for the useful hint.
    You are very helpful (as usual)

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    Middletown, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excluding data from a large file to fit (Excel 2000)

    is this article in the lounge data base? I could not find it. Thanks for guiding me

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excluding data from a large file to fit (Excel 2000)

    It's a Microsoft Knowldge Base article, not a Woody's post. You may be able to find it on the MS site, using the reference # I cited. Some time later (around Jan '95 I think), Microsoft released a Help file with this and a number of other VB articles in it. You might still be able to get a copy of that too.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Excluding data from a large file to fit (Excel 2000)

    Here is a link to a Microsoft KnowledgeBase article on importing large text files in Excel 2000: XL2000: "File Not Loaded Completely" Error Message When Running Text Import Wizard.

    And here is the link to the Microsoft KnowledgeBase article with a macro to automate importing large text files: XL: Importing Text Files Larger Than 16384 Rows. For Excel 2000, replace 16384 by 65536, as suggested for Excel 97. The maximum number of rows is 65536 in both Excel 97 and Excel 2000, whereas it was 16384 for Excel 95. As stated in the article, you'll have to parse the imported data by selecting Data/Text to Columns after running this macro.

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

    Re: Excluding data from a large file to fit (Excel 2000)

    Maybe they fixed it in Excel 2002, but in Excel 2000 the largest value you can enter is 32767. I guess that would allow you to retrieve the rows from 32 K to 96 K, but records beyond that would be inaccessible by this method, I think. Or am I missing something?

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excluding data from a large file to fit (Excel 2000)

    Without looking at the macro, I guess it defines the rowcount as an Integer, which has from -32,768 to 32,767 maximum value ranges. The row count variable needs to be defined as Long which has from -2,147,483,648 to 2,147,483,647 maximum value and will handle the maximum per worksheet row count of 65,536 which Excel has used in '97, '00 and '02 (XP) versions.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excluding data from a large file to fit (Excel 2000)

    Oops, now that I've looked at the code, the Counter for lines of source data is declared as Double. The code in KB Q120596 should work for 97+ if the line

    If ActiveCell.Row = 16384 Then

    is changed to

    If ActiveCell.Row = 65536 Then
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Excluding data from a large file to fit (Excel 2000)

    I thought the same thing (about the variable being declared as integer rather than long, say). However, I was responding to Legare's posting about using the built in functionality of Excel's Text Import Wizard.

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excluding data from a large file to fit (Excel 2000)

    Once in a while <img src=/S/flatcat.gif border=0 alt=flatcat width=61 height=21> mode messes me up. So it looks like MS forgot to declare the variable as double or larger in the text import wizard? I don't have a big enough source file to test with.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Excluding data from a large file to fit (Excel 2000)

    Actually I don't have that large a file either. Even if you try to open a small text file, then enter 32768 (or higher) in the Start row field, you get the tell-tale error message "Integer is not valid". Alternatively if you enter 32765, say, then click the up arrow, you can't get past 32767.

  14. #14
    Lounger
    Join Date
    Feb 2001
    Location
    Middletown, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excluding data from a large file to fit (Excel 2000)

    Thanks again to all for the help. I now have enough leads to go an study and see if this works.

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

    Re: Excluding data from a large file to fit (Excel 2000)

    Colin, John:

    I think MS declared the Start Row field as an integer indeed. This means that you can import only 32,767 + (65,536-1) = 98,302 rows of a text file using the Wizard (rows 1...65,536 in one sheet, rows 32,767...98,302 in another sheet). Until MS fixes the Wizard, rows 98,303 and up are inaccessible.

    The macro works fine, although it's slow. I successfully used it to import a text file with 193,000 records into 3 sheets. Another question is whether it's useful - Excel isn't the tool of choice to work with that many records; I'd rather use a database program (Access).

    Regards,
    Hans

Posting Permissions

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