XL: Text Import Wizard Maximum Row Limitation of 32767

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0, 7.0a
• Microsoft Excel 97

SYMPTOMS

In Microsoft Excel, if you specify a number in the Start Import At Row box in the Text Import Wizard, and the beginning row number that you specify is a value greater than 32767, you may receive the following error message.

   In Microsoft Excel versions 5.x and 7.x:

      Number is not valid.

   In Microsoft Excel 97:

      Your entry cannot be used. An integer or decimal number may be
      required.

WORKAROUNDS

To work around this problem, follow the appropriate procedure below for your version of Microsoft Excel.

Versions 5.x and 7.x -- Importing Text Files with Fewer than 49,151 Rows

The maximum number of rows in a Microsoft Excel 5.0 or 7.0 worksheet is 16,384. If you open a text file with 16,384 rows or fewer, all rows of the text file will be imported into one worksheet. However, if you attempt to import more rows in a text file than are available in the worksheet, only 16,384 rows of the text file will be loaded, and you will receive the following error:

     File Not Loaded Completely

1. Click Open on the File menu, select the text file to open, and click OK.

2. In the Text Import Wizard, select 1 for "Start Import at Row" and click Finish.

3. The first 16,384 rows of the text file are imported into a worksheet, and you receive the error "File Not Loaded Completely." Click OK. Save this workbook.

4. Repeat step 1. In the Text Import Wizard, select 16385 for "Start Import at Row," and click Finish.

5. The next 16,384 rows of the text file are imported into a worksheet, and you receive the error "File Not Loaded Completely" (if the text file contains more than 32,768 rows). Click OK. Save this workbook.

6. Repeat step 1. In the Text Import Wizard select 32767 for "Start Import at Row," and click Finish.

7. The last 16,384 rows of the text file are imported into a worksheet.

8. You can delete the first two rows in this workbook because these rows were also imported into the workbook you saved in step 5. Save this workbook.

Version 97 -- Importing Text Files with Fewer than 98,303 Rows

The maximum number of rows in a Microsoft Excel 97 worksheet is 65,536. If you open a text file with 65,536 rows or fewer, all rows of the text file will be imported into one worksheet. However, if you attempt to import more rows in a text file than are available in the worksheet, only 65,536 rows of the text file will be loaded, and you will receive the following error:

     File Not Loaded Completely

1. Click Open on the File menu, select the text file to open, and click OK.

2. In the Text Import Wizard, select 1 for "Start Import at Row," and click Finish.

3. The first 65,536 rows of the text file are imported into a worksheet, and you receive the error "File Not Loaded Completely". Click OK. Save this workbook.

4. Repeat step 1. In the Text Import Wizard, select 32767 for "Start Import at Row," and click Finish.

5. Note that 65,536 rows of the text file starting at row 32766 are imported into a worksheet. Save this workbook.

6. You can delete the first 32,770 rows in this workbook because these rows were also imported into the workbook you saved in step 5. Save this workbook.

Importing Larger Text Files

If your text file is too large to import using the Text Import Wizard as described earlier, you will need to create a macro to import your text file that determines the size of the text file and breaks it up into individual worksheets.

For an example of a Visual Basic for Applications macro that accomplishes this, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q120596
   TITLE     : "XL: Importing Text Files Larger Than 16384 Rows"