Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Importing Huge Data Files

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.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Simon    01 Mar 2016, 14:44
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?
Barry    29 Jul 2014, 14:18
@ 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:\users\Baz\Documents\TestData.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:\users\Baz\Documents\TestData.txt" For Output As #122

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

Mark    28 Jul 2014, 13:57
@ Barry

Public Sub LoadFile() -

shows highlighted in yellow

Mark
Mark    28 Jul 2014, 13:48
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
Barry    20 Jul 2014, 02:32
@Mark

which line of code does it error on?
Mark Chater    19 Jul 2014, 02:57
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.
Barry    14 Jun 2014, 10:27
@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.

james    13 Jun 2014, 08:22
Can you help with the below?
james    09 Jun 2014, 11:25
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
james    09 Jun 2014, 11:20
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
james    09 Jun 2014, 09:34
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))
Barry    07 Jun 2014, 05:43
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.
james    06 Jun 2014, 11:42
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.

David Zondray    21 Apr 2014, 22:41
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
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.