Importing Huge Data Files

by Allen Wyatt
(last updated May 4, 2013)

16

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.

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

Using Correct Apostrophes

Word does a pretty good job of figuring out what apostrophes to use around your text. There may be times, however, when it ...

Discover More

Opening a Template

If you have a template stored on disk, you can open it and make changes to it just as you do other documents. This tip ...

Discover More

Counting Cells According to Case

Text placed in cells can either be lowercase, uppercase, or a mixture of the two. If you want to count the cells based upon ...

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 (menu)

Aligning Cells when Importing from CSV

When you import information from a CSV text file, Excel formats the data according to its default settings. Wouldn't it be ...

Discover More

Use Filenames That Sort Properly

When storing your Excel workbook, you need to specify a file name to be used for the workbook. Take a moment to consider how ...

Discover More

Sudden Increases in Workbook File Size

Workbooks can get rather large rather quickly. If you think your workbook has gotten too big too fast, here are some things ...

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. 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 seven more than 5?

2016-12-07 00:47:54

Julie

to solve error 9 or out of range, you should first add new sheet prior to executing the file.


2016-11-17 03:25:21

puja

Hi,
I want to Convert TEXT file to Excel its huge data but Excel line line item is not enough to dumb all data from Text.

so can you help me out! I don't want to work in Access File.

Thanks.


2016-03-01 14:44:23

Simon

Hello

i know this was posted a while ago, but looking at importing a txt file into excel that is 12 million lines long, code is fine but get a run out of memory.

any advise?


2014-07-29 14:18:34

Barry

@ Mark,

Did you correct the error in the code I mentioned on 7th June 2014? This would result in Error 438.

Here'sthe code I've used to test this out. The last two routines will clear the wprksheets, and the very last routine will create a "huge" file to import for testing purposes.

Also be careful where the data file is located as if your user permissions do alow access then this will cuase problems too.

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

Application.ScreenUpdating = True
sDelim = ","
MaxSize = 500000
i = 0
Open "C:usersBazDocumentsTestData.txt" For Input As #5
Do While Not EOF(5)
iSh = Int(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).Range("A1").Offset(lL, J).Value = _
Trim(Left(strLine, iLen - 1))
strLine = Trim(Right(strLine, Len(strLine) - iLen))
J = J + 1
Loop
i = i + 1
Debug.Print "Count is: " & Format(i, "#,###,000"), Format(lL, "#,###,000")
Worksheets("Sheet" & iSh).Activate
Worksheets("Sheet" & iSh).Range("A1").Offset(lL, J).Select
DoEvents
Loop
Close #5
Application.ScreenUpdating = True
End Sub

Sub ClearData()
Dim i As Integer

For i = 1 To Worksheets.Count
Worksheets(i).Range("A:Z").ClearContents
Next

End Sub


Sub CreateFile()
Dim i As Long

Open "C:usersBazDocumentsTestData.txt" For Output As #122

For i = 1 To 2000000
Print #122, "This is record, number," & i
Next i
Close #122
End Sub


2014-07-28 13:57:21

Mark

@ Barry

Public Sub LoadFile() -

shows highlighted in yellow

Mark


2014-07-28 13:48:03

Mark

Hi Barry,

Sorry for the late reply.

After I run the macro, it just shows me a popup on the screen that says -

Object doesn't support this property or method

I clicked on "Help" and it said -

Object doesn't support this property or method (Error 438)

I cant find which line the error is on.

Thanks


2014-07-20 02:32:00

Barry

@Mark

which line of code does it error on?


2014-07-19 02:57:27

Mark Chater

Hi,

I changed the max size to 100000, and file path.

I got this error:

Object doesn't support this property or method

Please help.


2014-06-14 10:27:42

Barry

@James

The problem lies with the line :

iSh = (I / MaxSize) + 1

iSh is dimensioned to be a LONG integer, when iSh is calculated I/MaxSize is getting rounded when assigned to iSh so I=500,001 iSh/Max is greater than 0.5 which is rounded to 1, and the sheet for entry is changed, but lL isn't changed so the entries continue from row 500,001 on sheet2 until I reaches MaxSize when the line "lL = I Mod MaxSize" makes lL drop from 1,000,000 to 0 which is where subsequent entries are put. This then repeats. Change the code it to:

iSh = Int(I / MaxSize) + 1

will fix the problem.


2014-06-13 08:22:24

james

Can you help with the below?


2014-06-09 11:25:27

james

Ignore previous comments...


Hi,

As I have excel 2010. I changed MaxSize to 1,000,000

The code seems to import only the first 500,000 rows in sheet 1

For the next sheets it seems to populate rows 500,002 - 1,000,000 and then rows 1 - 500,000 so the data is out of order.

Any chance you could amend the code so that it brings the data in correctly.

James


2014-06-09 11:20:17

james

Hi,

As I have excel 2010. I changed MaxSize to 500000

The code seems to import the first 500,000 rows into sheet 1 fine.

For the next sheets it seems to populate rows 500,002 - 1,000,000 and then rows 1 - 500,000 so the data is out of order.

Any chance you could amend the code so that it brings the data in correctly.

James


2014-06-09 09:34:28

james

Hi,

The code goes up to row number 32500 and then errors.

The error I got was run-time error 9. Subscript out of range and this related to the new line...

Worksheets("Sheet" & iSh).Range("A1").Offset(lL, J) = Trim(Left(strLine, iLen - 1))


2014-06-07 05:43:25

Barry

This is an error in the original code the "Offset" method needs a range to offset from and this is omitted. You can offset from a worksheet and hence the Error 438 that yuo are experiencing. I haven't tried this but replace this line with:

Worksheets("Sheet" & iSh).Range("A1").Offset(lL, J) = Trim(Left(strLine, iLen - 1))

Let us know how you get on.


2014-06-06 11:42:47

james

Hi,

It stops at this line...

Worksheets("Sheet" & iSh).Offset(lL, J).Value = _
Trim(Left(strLine, iLen - 1))


I get the following error message...

run-time error "438"

Object does not support this property or method.


2014-04-21 22:41:59

David Zondray

Hi Allen,

Thanks for sharing, very useful. I have worked with Aspose.Cells and I think they have also adopted a very useful approach to this problem. I think it will be useful to look into it, here is the link fo

http://www.aspose.com/docs/display/cellsnet/Optimizing+Memory+usage+while+working+with+Big+Files+having+large+Datasets


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.