Results 1 to 9 of 9
  1. #1
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Excel vba - slow down while reading text file (2000)

    Hi Folks,

    Attached is some code I've cobbled together for analysing a large amount of data (about 12million records) in a series of 8 text files, varying from ~120,000 records to 4,250,000 records each. The largest file is about 78Mb. Basically the code reads in a block of data, typically around 500 records, processes it, clears the worksheet and starts over with the next set of records.

    The problem is that the time taken to process the files (on my system - AMD 2500 Athlon, Win 2K & 512Mb RAM) increases by around 22 seconds per 100,000 records - eg the 1st 100,000 takes about 26 seconds, the 2nd takes 48 seconds, the 3rd takes 70 seconds, and so on.

    I'd be grateful if someone could take a look at the code and suggest some efficiencies - especially with a view to eliminating the increasing cycle times.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel vba - slow down while reading text file (2000)

    The line Do While Seek(FileNum) <= LOF(FileNum) is very inefficient, it forces VBA to calculate the position within the file continuously. Instead, use Do While Not EOF(FileNum). It's about 7 times as fast on my PC.

    Your code now fills a worksheet, then clears the UsedRange repeatedly. I wonder if ActiveSheet.Cells.Clear might be faster than ActiveSheet.UsedRange.ClearContents, and also whether creating, filling and discarding a new workbook for each run might be better. I haven't tested these ideas.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel vba - slow down while reading text file (2000)

    Hi Hans,

    I've just run a few tests, using a 300,000 record text file. What I've found is that using:
    . Do While Not EOF(FileNum) with ActiveSheet.Cells.Clear is about 4 seconds faster than what I was already getting.
    . Do While Not EOF(FileNum) with ActiveSheet.UsedRange.ClearContents (which is what I was originally using) is about 7 seconds faster than what I was already getting.
    This would seem to confirm what you said about Do While Not EOF(FileNum) being more efficient than Do While Seek(FileNum) <= LOF(FileNum). Conversely, it seems that ActiveSheet.UsedRange.ClearContents is more efficient than ActiveSheet.Cells.Clear.

    The faster execution is also accompanied by an apparent overall reduction in the increasing cycle times, which is a good thing. I'd have to test with a much larger file to confirm this.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel vba - slow down while reading text file

    This code is a bit complicated. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Does Excel have an Undo buffer you can clear? I wonder whether an accumulation of changes could explain part of the "slowing down" phenomenon.

    Also, if the file reading has anything to do with the slowing down, you could try using a TextStream object and its ReadLine method (part of the Scripting library's FileSystemObject family of tools).

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel vba - slow down while reading text file

    Hi Jefferson,

    Thanks for the feedback. AFAIK Excel doesn't have anything equivalent to Word's UndoClear statement. In any event, since Excel's Undo only remembers the last 16 actions, I doubt that's having a material effect.

    Unfortunately, I don't know enough about the "TextStream object and its ReadLine method" to make use of, let alone whether it might make a difference.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel vba - slow down while reading text file

    The TextStream object is part of the Scripting library. Working with it is generally slower than using the "old-fashioned" file handling methods, so there is no point in trying it.

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel vba - slow down while reading text file

    Hi Hans,

    I decided to try a different approach, by reading the source data into an array, instead of into a worksheet, evaluating the items in the array and only writing out the items I am interested in.

    For what turned out to be 11.6 million records, the process took 16 minutes to complete (on a slower PC than the one in my original post) compared to 7.6 hours for only 4.9 million of the same records on the faster PC.

    Obviously, true array processing is much faster than using a worksheet as an array ...

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel vba - slow down while reading text file

    Excellent!

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel vba - slow down while reading text file

    Better still, the process takes barely 5 minutes to complete processing 11.6 million records on the PC in my original post, or just 129 seconds for the previously-timed 4.9 million records, making the new code over 200 times faster than the original code!
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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