You are currently offline, waiting for your internet to reconnect

Text Import Wizard Maximum Start Row Limitation of 32767

This article was previously published under Q119770
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SYMPTOMS
In Microsoft Excel, when you use the Text Import Wizard to import a textfile, you may receive one of the following error messages.

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.
CAUSE
This error message occurs when the value you specify in the "Start importat row" box is 32,768 or higher. The highest value you can enter in thisbox is 32,767.
WORKAROUND
To work around this problem, use the method appropriate for your version ofMicrosoft Excel.

Excel 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 is16,384. If you open a text file with 16,384 rows or fewer, all rows of thetext file will be imported into one worksheet. However, if you attempt toimport more rows in a text file than are available in the worksheet, only16,384 rows of the text file will be loaded, and you will receive thefollowing error message:
File Not Loaded Completely
If your worksheet contains more than 16,384 but fewer than 49,151 rows,you can use the Text Import Wizard to import the text file into multipleworksheets by following these steps:
  1. On the File menu, click Open. 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 message
    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 message
    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.

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

The maximum number of rows in a Microsoft Excel 97 worksheet is 65,536. Ifyou open a text file with 65,536 rows or fewer, all rows of the text filewill be imported into one worksheet. However, if you attempt to import morerows in a text file than are available in the worksheet, only 65,536 rowsof the text file will be loaded, and you will receive the following errormessage:
File Not Loaded Completely
If your worksheet contains more than 65,536 but fewer than 98,303 rows,you can use the Text Import Wizard to import the text file into multipleworksheets by following these steps:
  1. On the File menu, click Open. 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 message
    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 asdescribed earlier, you will need to create a macro to import your text filethat determines the size of the text file and breaks it up into individualworksheets.

For an example of a Visual Basic for Applications macro that accomplishesthis, please see the following article in the Microsoft Knowledge Base:
120596 "Text files that are larger than 65,536 rows cannot be imported to Excel 97, Excel 2000, Excel 2002 and Excel 2003"
REFERENCES

Excel 97

For more information about the Text Import Wizard, click Contents And Indexon the Help menu, click the Index tab in Microsoft Excel Help, type thefollowing text
importing data, text files
and then double-click the selected text to go to the "View or save filesfrom other programs as Microsoft Excel workbooks" topic. If you are unableto find the information you need, ask the Office Assistant.
xl97 7.00a 5.00c XL
Properties

Article ID: 119770 - Last Review: 05/11/2007 00:45:33 - Revision: 1.2

  • Microsoft Excel 97 Standard Edition
  • kbprb KB119770
Feedback