by Allen Wyatt
(last updated June 26, 2021)
Damodar has a CSV file he needs to import into Excel. Normally this wouldn't be a problem, but this CSV file has well over a million rows in it. He wonders if there is any way to get such a huge file into Excel.
Excel has a limit on the number of rows you can have in a worksheet—up to 1,048,576. As Damodar has discovered, it is very possible 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. 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 1.5 million rows you need to import into Excel, 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 750,000 rows) into one worksheet and the second file (also 750,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.
You could also 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 = 1048000 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. You should also 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.
There are a couple of potential gotchas that you need to be aware of when running a macro like this. The first is that it is going to run rather slowly—as in "take a break for lunch" or "go home overnight" slowly. The length of time it takes is directly related to the number of rows of data and the number of fields in each row. The second thing to be aware of is it is entirely possible that you will crash your system. This, again, depends on the amount of data you are importing. That data requires memory, and the amount of memory required increases with each imported row. If you run out of memory, well... (you get the idea).
Finally, if you are using the version of Excel provided with Microsoft 365, you could work with your data using Power Query. This will only work, though, if you can �'transform�" your data in some way. This means that you could identify ways in which your data could be segregated to differing worksheets based on a value that can be identified in the data you need to import. Perhaps, for instance, you might be able to separate data based on department, region, sales person, or some other value.
In this case, follow these steps:
Figure 1. The Import Data dialog box.
Working with Power Query is (as the name implies) powerful. It will take you a bit of getting used to, however, to make sure that you import everything you need from the source CSV file.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13876) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
You can use Excel for all types of data processing. You may want to work with filenames in a worksheet, but the first ...Discover More
It is often necessary to import information from other programs into Excel. Sometimes this can lead to challenges, such ...Discover More
As you are developing your workbooks, you might want a way to automatically create backup files that include a date and ...Discover More
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.