Importing Huge Data Files

by Allen Wyatt
(last updated June 26, 2021)

1

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.)
  10. Figure 1. The Import Data dialog box.

  11. Make changes in the import settings, as desired. (For most uses, the defaults of Table and New Worksheet should be fine.)
  12. 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.
  13. In the Queries & Connections pane, right-click on the query you just executed. Excel displays a Context menu.
  14. 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.
  15. 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 Excel in Office 365.

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

Determining the Month of the Year

Your macro code may need to determine the month of the year represented by a particular date. You can find the desired ...

Discover More

Adding a Statement Showing an Automatic Row Count

If you want to add a dynamic statement to a worksheet that indicates how many rows are in a data table, you might be at a ...

Discover More

Quick and Dirty Paragraph Count

Need to know how many paragraphs are in a document? You can use Word's Find and Replace feature to get a count quickly.

Discover More

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!

More ExcelTips (ribbon)

Pulling Filenames into a Worksheet

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

Importing CUSIP Numbers Correctly

It is often necessary to import information from other programs into Excel. Sometimes this can lead to challenges, such ...

Discover More

Creating a Dated Backup File

As you are developing your workbooks, you might want a way to automatically create backup files that include a date and ...

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}] 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 8 - 8?

2021-06-26 05:50:31

Rohn S, MVP 2012-2018

Loading large data to a "traditional" DB is an option Then use powerquery to connect to the DB to extract into the data model.
.
PowerQuery is not limited to 365. I've been using it in Excel 2010. The old version obviously won't have all of the features in the 365 version, but it works for the "fundamentals".
.
2016/2019/365 PowerQuery is "built in" on the ribbon
.
The Complete Guide to Installing Power Query (2013 2010)
https://www.excelcampus.com/install-power-query/
Power Query is a “free” add-in from Microsoft for Excel 2010 and 2013. This guide explains which versions of Excel you need, and how to install it.
.


This Site

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.

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.