by Allen Wyatt
(last updated May 4, 2013)
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2533) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When working with multiple workbooks open, you may want a way to close them all with a single command. Here's the secret.Discover More
Does your macro need to add information to the end of a text file? This is called appending, and is done using the technique ...Discover More
Workbooks are loaded from disk files, but workbooks aren't the only type of files that Excel can load. This tip provides a ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."