XLM: Importing Text Files Larger Than 16384 Rows

ID: Q116232

The information in this article applies to:

Microsoft Excel for Windows, versions 3.x, 4.x, 5.0 5.0c
Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
Microsoft Excel for Windows 95, version 7.0

SUMMARY

Text files that contain more than 16,384 rows cannot be opened in their entirety in Microsoft Excel. You cannot open these files because a worksheet is limited to 16,384 rows. If you open a file that contains more data than this, the size will produce the following error message

   File not loaded completely

and the text file will be truncated at the 16,384th row. However, you can use a macro to open the file and automatically break the text into multiple worksheets.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/supportnet/refguide/

The following sample macro prompts the user for a text file name, opens the file into memory, and determines if the number of rows is larger than 16,384. If the number of rows is greater than the Microsoft Excel worksheet limit, the macro separates the file into multiple worksheets.

To use this macro, follow these steps:

1. Create a macro sheet.

    Microsoft Excel Version 5.0 or 7.0
    ----------------------------------

    Point to Macro on the Insert menu, and then click "MS Excel 4.0 Macro."

    Microsoft Excel Version 3.0 or 4.0
    ----------------------------------

    a. On the File menu, click New.

    b. Select macro sheet in the New box, and then click OK.

 2. Enter following macro into the macro sheet:

      A1:  Large_Text_File_Importer
      A2:  filename=INPUT("Please enter the Text File's name",2,"Large Text
           File Importer")
      A3:  =IF(filename=FALSE,RETURN())
      A4:  filenum=FOPEN(filename,2)
      A5:  =ECHO(FALSE)
      A6:  =NEW(1)
      A7:  Counter=1
      A8:  =WHILE(FPOS(filenum)<=FSIZE(filenum))
      A9:  =MESSAGE(TRUE,"Importing Row "&Counter&" of text file
            "&filename)
      A10: =FORMULA(FREADLN(filenum))
      A11: =IF(ROW(ACTIVE.CELL())=16384)
      A12: =NEW(1)
      A13: =ELSE()
      A14: =SELECT("R[1]C")
      A15: =END.IF()
      A16: Counter=Counter+1
      A17: =NEXT()
      A18: =FCLOSE(filenum)
      A19: =MESSAGE(FALSE)
      A20: =RETURN()

   Description of macro code:

      A1:  Name of the Macro
      A2:  Prompt user for the Text file's name (with complete path and
           file name extension).
      A3:  If input box cancel button was selected, then end macro.
      A4:  Open the Text file with Read access.
      A5:  Turn screen updating off.
      A6:  Create a new worksheet
      A7:  Initialize the row Counter name to the value of 1.
      A8:  Loop until we reach the end of the file.
      A9:  Display a status message at the bottom of Excel.
      A10: Read a line from the text file and place into the active cell.
      A11: Check to see if the current row is 16384.
      A12: If A12 is True, then create a new sheet.
      A13: If A12 is NOT True, then do the following...
      A14: Select 1 row down from the active cell.
      A15: End the IF block.
      A16: Increment the Counter name by 1.
      A17: Loop back to A9.
      A18: Close the text file (opened in cell A4).
      A19: Turn the status bar back to normal.
      A20: End Macro.

3. Next, define a name for the macro.

    Microsoft Excel Version 5.0 or 7.0
    ----------------------------------

    a. Point to Name on the Insert menu, and then click Define.

    b. Type "ImportFile" (without the quotation marks) in the "Names in
       Workbook" box.

    c. Click Command in the Macro box, and then click OK

    Microsoft Excel Version 3.0 or 4.0
    ----------------------------------

    a. On the Formula Menu, click Define Name.

    b. Type "ImportFile" (without the quotation marks) in the Name box.

    c. Click Command in the Macro group box, and then click OK

4. Run the macro.

    Microsoft Excel Version 5.0 or 7.0
    ----------------------------------

    a. On the Tools menu, click Macro.

    b. Click ImportFile in the "Macro Name/Reference" box, and then
       click OK.

    Microsoft Excel Version 3.0 or 4.0
    ----------------------------------

    a. On the Macro menu, click Run.

    b. Click ImportFile in the Run box, and then click OK.

The text file that you import is broken up into multiple worksheets.

NOTE: This macro does not parse the data into columns. After you use this macro, you may also need to do the following:

If you are using Microsoft Excel 5.0, use the Text To Columns command on the Data menu.
```
    -or-
```
If you are using Microsoft Excel 3.0, 4.0, or 4.0a, use the Data Parse command or the Smart Parse command FLATFILE.XLA add-in macro.

Note also that, because this is a macro, it may be significantly slower than clicking Open on the File menu.

Additional query words: 3.00 4.00 5.00 import ascii

Keywords          : xlloadsave 
Version           : WINDOWS:3.0,4.0,5.0,5.0c,7.0; MACINTOSH:3.0,4.0,5.0,5.0a
Platform          : MACINTOSH WINDOWS

Last Reviewed: June 1, 1999