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

[mhsherif]

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

[LegareColeman]

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.

[macropod]

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

[mhsherif]

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

[mhsherif]

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

[macropod]

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

[HansV]

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.

[ColinBurrows]

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?

[JohnBF]

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.

[JohnBF]

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

[ColinBurrows]

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.

[JohnBF]

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.

[ColinBurrows]

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.

[mhsherif]

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

[HansV]

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