Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Importing Huge Data Files.

Importing Huge Data Files

Written by Allen Wyatt (last updated July 18, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003


1

Excel has a limit on the number of rows you can have in a worksheet—up to 65,535. It is very possible, however, to have a raw data file that has more than this number of rows. If you need to import that file into Excel, then doing so can appear almost impossible without upgrading to Excel 2007 or a later version. (Those later versions broke the 65,535 row limit.) There are a couple of things you can do, however.

One possibility is to make copies of the raw text file (the one you want to import) and then cut the size of each file down. For instance, if you have a total of 110,000 rows you need to import into Excel, and you are operating under the 65,535-row limit, you could make two copies of the raw text file. Delete the second half of the first text file and the first half of the second. Thus, you can import the first file (now 55,000 rows) into one worksheet and the second file (also 55,000 rows) into the second.

If you don't want to break up your input files, you might consider importing the file into Access. Unlike Excel, Access has virtually no limit on the number of rows you can import. You could then either work with the file in Access, or export portions of the file to use in Excel.

Finally, you could use a macro to import the records in the large source file. There are many ways you can do this, but the basic idea behind any approach is to fetch each row from the source file and place it in a new row of a worksheet. The macro must keep track of how many rows it's placed, and switch to a new worksheet, if necessary.

Public Sub LoadFile()
    Dim strLine As String
    Dim I As Long
    Dim J As Long
    Dim iLen As Integer
    Dim iSh As Integer
    Dim lL As Long
    Dim sDelim As String
    Dim MaxSize As Long

    sDelim = Chr(9)
    MaxSize = 65000
    I = 0
    Open "C:\MyDir\MyFile.txt" For Input As #5
    Do While Not EOF(5)
        iSh = (I / MaxSize) + 1
        lL = I Mod MaxSize
        Line Input #5, strLine
        If Right(strLine, 1) <> sDelim Then
           strLine = Trim(strLine) & sDelim
        End If
        J = 0
        Do While Len(strLine) > 1
            iLen = InStr(strLine, sDelim)
            Worksheets("Sheet" & iSh).Offset(lL, J).Value = _
              Trim(Left(strLine, iLen - 1))
            strLine = Trim(Right(strLine, Len(strLine) - iLen))
            J = J + 1
        Loop
        I = I + 1
    Loop
    Close #5
End Sub

The macro assumes you have enough worksheets already in your workbook to contain the data, and that they are numbered Sheet1, Sheet2, Sheet3, etc. Two variables you'll want to check in the program are the settings of sDelim and MaxSize. The first specifies what character is used as a field delimiter in the information that is being read. The second specifies the maximum number of rows you want on each worksheet. (Don't set MaxSize greater than whatever your version of Excel will allow.)

Finally, note that the macro opens the text file MyFile.txt. You'll want to change this Open statement so that it opens the real source file you want to import.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2533) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Importing Huge Data Files.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Too Many Rows or Columns in a PivotTable

PivotTables are often used to aggregate lots of information, and they do it beautifully. What do you do if Excel starts ...

Discover More

Trimming Spaces from Strings

Need to get rid of extraneous spaces before or after the text in a string? VBA provides three different functions you can ...

Discover More

Selecting Text in Linked Text Boxes

Text boxes are often used as design elements in a document layout. If you have linked text boxes, you may have noticed ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Sorting Files

The Open dialog box allows you to sort the files it presents to you. How you do the sorting depends on the version of ...

Discover More

Saving a Workbook with a Preview

When you save your workbooks, Excel can also save a preview image (thumbnail) that can be displayed in the Open dialog ...

Discover More

Can't Open Multiple Workbooks from the Desktop

Having trouble opening a group of workbooks selected on your desktop? The reason is probably due to Windows, not Excel.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 - 8?

2020-10-07 02:19:11

Jakub

there is debug in line
Worksheets("Sheet" & iSh).Offset(lL, J).Value = _
Trim(Left(strLine, iLen - 1))
can u give any advise to solve the problem?


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.