Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Importing Huge Data Files.

Importing Huge Data Files

Written by Allen Wyatt (last updated June 26, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


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 Microsoft 365. You can find a version of this tip for the older menu interface of Excel 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

E-mailing from a Mail Merge

Word has a great mail merge capability. Inherent in this capability is the option to send your merged documents via ...

Discover More

Pulling Formulas from a Worksheet

The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can ...

Discover More

Tools on Developer Tab are Unavailable

Want to add some macros to your workbook? What do you do if you try to add the macros but the program has disabled the tools?

Discover More

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!

More ExcelTips (ribbon)

Creating a CSV File

Need to get your data into a format that can be easily read by other programs? Chances are good that a simple CSV file ...

Discover More

Determining the Length of a Text File

When processing plain text files in a macro, it is often helpful to know how much data the file contains. The normal way ...

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
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 2 + 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.