Importing Huge Data Files

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:

1. Display the Data tab of the ribbon.
2. In the Get & Transform Data group (left side of the ribbon), click From Text/CSV. Excel displays the Import Data dialog box. This looks very much like a standard Open dialog box.
3. Using the controls in the dialog box, locate and select the CSV file you want to import.
4. Click Import. Excel �'connects�" to the data file and, shortly, displays a portion of your data in a large dialog box.
5. Click the Transform button. Excel displays the Power Query Editor dialog box.
6. Click the column by which you want to filter your data.
7. Use the drop-down list at the top of the column to specify the criteria by which the data should be filtered.
8. In the Navigation Pane, double-click the query and give it a name appropriate to your filtering. For instance, you might name the query �'Sales Department�" or �'First Two Years.�"
9. Click File | Close & Load To. Excel displays the Import Data dialog box. (See Figure 1.)



Figure 1. The Import Data dialog box.

10. Make changes in the import settings, as desired. (For most uses, the defaults of Table and New Worksheet should be fine.)
11. Click OK. Excel imports the filtered data from the CSV file, placing it in a new worksheet. Also, the Queries & Connections pane should be visible at the right side of your data.
12. In the Queries & Connections pane, right-click on the query you just executed. Excel displays a Context menu.
13. In the Context menu, choose Duplicate. Excel makes a copy of the query in the Queries & Connections pane and displays that duplicate query in the Power Query Editor.
14. Repeat steps 6 through 13 for each of the other data sets you want to extract from the CSV file. (Make sure your data set criteria are comprehensive enough that all records eventually end up being imported.)

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 2021. You can find a version of this tip for the older menu interface of Excel here: Importing Huge Data Files.