Results 1 to 15 of 15
  1. #1
    dkelch
    Guest

    lots of rows (Excel97 SR-2)

    I have tab-separated text files that contain two-column tables of numbers with about 120K rows. Excel97 has a limit of 64K rows in one spreadsheet. The text import wizard (step 1) allows one to specify a starting row, but its upper limit is 32K. Although cumbersome, that gives me the ability to read only 96K rows. How can I read these long tables?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: lots of rows (Excel97 SR-2)

    dkelch

    Do you want to have the table in one worksheet? This is as you know impossible, but if you use some VBA code, then you can read the text file into two worksheets or even into two sets of Two-Column Ranges:

    Here is an example:

    Dim lLastRowReady As Long
    Dim iColumn As Integer

    Open "TESTFILE" For Input As #1 ' Open file for input Text file.

    lLastRowReady = 1
    iColumn = 1
    Do While Not EOF(1) ' Loop until end of file.
    Input #1, ActiveSheet.Cells(lLastRowReady, iColumn) '/Read data.

    lLastRowReady = lLastRowReady + 1

    If lLastRowReady = ActiveSheet.Rows.Count Then
    lLastRowReady = 1
    iColumn = iColumn + 1
    End If
    Loop

    Close #1 ' Close file.

    I did not really test this one, but you can get an idea...

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lots of rows (Excel97 SR-2)

    You could use a macro to load the file. If you want to do that, then I will help if you will answer one question. How do you want the file loaded? Do you want the file split to separate sheets, or do you want it to wrap columns on the same sheet?
    Legare Coleman

  4. #4
    dkelch
    Guest

    Re: lots of rows (Excel97 SR-2)

    Yup, that's what I want to do. I guess it doesn't matter if it is on one sheet or two. I'm anticipating that reading one row at a time is going to take a long, long time. Is there some way to do the equivalent of a "block read" that transfers many rows at a time?

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: lots of rows (Excel97 SR-2)

    dkelch

    You can read it into an array in memory and then dump it all in one or more shot(s) into a range(s).

    But you will need to loop through the file.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lots of rows (Excel97 SR-2)

    The following code assumes that the file name is C"MyDirMyFile.txt". The code below will flow the data from Sheet1 to Sheet2 to however many sheets are required. The code assumes that there are enough sheets to hold all of the data. Since I don't have the file, I haven't tested this code but it should be close:

    <pre>Public Sub LoadFile()
    Dim strLine As String
    Dim I As Long, J As Long, iFileNum As Integer, iLen As Integer
    Dim iSh As Integer, lL As Long
    I = 0
    iFileNum = FreeFile
    Open "C:MyDirMyFile.txt" For Input As #iFileNum
    Do While Not EOF(iFileNum)
    iSh = (I / 65535) + 1
    lL = I Mod 65535
    Input #iFileNum, strLine
    If Right(strLine, 1) <> Chr(9) Then
    strLine = Trim(strLine) & Chr(9)
    End If
    J = 0
    Do While Len(strLine) > 1
    iLen = InStr(strLine, Chr(9))
    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 #iFileNum
    End Sub
    </pre>

    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lots of rows (Excel97 SR-2)

    Reading a file in one row at a time is actually quite fast, especially if you use Wassim's suggestion of reading the contents into an array rather than transferring each row to the spreadsheet upon reading.

  8. #8
    dkelch
    Guest

    Re: lots of rows (Excel97 SR-2)

    Thank you Legare, Wassim, and JIMbythebay... I'm off to a good start.

    I've tried variations of your suggestions, and have found that (at least the way I coded it) it is MUCH faster (about 5x) to load the data into the worksheet one line at a time vs. reading into an array and then transferring the array to the worksheet (seems like there might be considerable overhead with an implied DO... maybe I'm doing something wrong. I'm using something like ...Range(Cells(1, 1), Cells(65536, 2)) = strArray...). It's still MUCH slower than I think it could be. For short files, the one-line-at-a-time code is about 4X slower than "Workbooks.OpenText...". But, at least I've got a solution.

    Again, thanks much!

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lots of rows (Excel97 SR-2)

    The array method should be much faster, because you can tell Excel, such-and-such range equals such-and-such array, without looping through the array (I assume this is what you meant by the overhead of the implied Do). However, Excel treats elements of one-dimensional arrays as columns. For example, if you have a one-dimensional array with 10 members, and try to put it into a range as follows:
    <pre>Range("A1:A10").Value = MyArray
    </pre>

    The whole range will be filled with the *first* member only. Instead, you have to do something like this:
    <pre>Range("A1").Value = Application.WorksheetFunction.Transpose(MyArray)
    </pre>


  10. #10
    dkelch
    Guest

    Re: lots of rows (Excel97 SR-2)

    I used a two dimensional array thusly:

    Dim strArray(bignumber,2) As String
    .
    .
    .
    Range(Cells(1, 1), Cells(bignumber+1, 2)) = strArray

    and it took forever... How may I code it so that it is faster?

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lots of rows (Excel97 SR-2)

    Hmmm... I dunno. How big is BigNumber?

    (sorry for assuming you were looping to dump the array - my mistake)

  12. #12
    dkelch
    Guest

    Re: lots of rows (Excel97 SR-2)

    64K... no problem, maybe I wan't clear...

  13. #13
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lots of rows (Excel97 SR-2)

    Sorry, I'm out of ideas.

  14. #14
    dkelch
    Guest

    Re: lots of rows (Excel97 SR-2)

    Took me a while to get back to you... Thought I'd share what I (re)learned about Excel behavior.

    When updating a worksheet from VBA, there is a certain amount of overhead associated with not only addressing a cell, but also a LOT of overhead consumed by what Excel does after a cell is updated. In my case I had to do all of the following to get reasonable speed:

    Application.Calculation = xlManual ' saved about a minute!
    Application.ScreenUpdating = False ' saved about 30% in time
    Application.EnableEvents = False ' saved about 20% more time

    Be sure to turn them all back on after you are done...

    I have learned these lessons individually many times, but seem to forget them when I need them most!

    Thanks for your help.

  15. #15
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lots of rows (Excel97 SR-2)

    Indeed! Makes a lot of sense. Thanks for the follow-up!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •