Excel Loop Speed - (Moved)

[Lights_On]

Hi,

I am looping through a list of excel files, and opening each one to read a specific cell.  The aim is to then keep a list of only the excel files that have the field in them I require.

Below is an example of the code used.

 

Local   $FieldWanted            =   $arr[1]
        Local   $oExcel                 =   _Excel_Open(False)
        Local   $NumberOfRows           =   UBound($AList)-1
        
        While $NumberOfRows <> 0

            Local $File         =   _Excel_BookOpenEX($oExcel, @ScriptDir & "\The_File.xlsx", False, True)
            Local $Field        =   _Excel_RangeRead($File, Default, "C23", 3)
            _Excel_BookClose($File, False)
            If $Field <> $FieldWanted Then
                _ArrayDelete($AList, $NumberOfRows)
            Else
                $AList[$NumberOfRows]=StringRegExpReplace($AList[$NumberOfRows], "\..*", "")
            EndIf

            Local $OldNumberOfRows = $NumberOfRows - 1
            $NumberOfRows = $OldNumberOfRows
        WEnd
        
        _Excel_Close($oExcel, False, True)

 

What I have in my code works – but as more files are added it slows to a crawl as it has to open each file, read it, make a decision, then close it again.

Is there a way to make this process faster? A way I am not aware of? 

Thank you in advance.

[Melba23]

Moved to the appropriate forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions. If it's super geeky and you don't know where to put it - it's probably here.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[Lights_On]

Hi Melba23,

Thank you for moving the post accordingly and apologies for using the incorrect location - have read an understood your post.

 

[Melba23]

Lights_On,

No probs - more people are likely to see it here anyway.

M23

[Lights_On]

Appreciated. Thank you.

[SlackerAl]

I'm not sure if this helps you, but you can directly check the cell values in unopened .xlsx files from a separate workbook.

Open a new book and try this as a formula to get F4 in sheet 1 of the un-opened file. Note, as this does not open the target workbook there will be no-recalc, so time dependent functions will not have updated since it was last saved. Additionally, the square brackets ARE required.

='C:\Full\path\to\file\[target.xlsx]Sheet1'!$F$4

Perhaps you could use AutoIt to build this master sheet from a directory search if your list of files is not static?

I have not bench-marked the two methods, but at the very least this method spares you an auto-recalc on each workbook load.

Edited May 20, 2019 by SlackerAl

[Lights_On]

Hi SlackerAl,

 

Thank you for taking the time to help.

 

So if i understand you correctly I can open a workbook instance, then use something like:

 

Local $Field = 'C:\Full\path\to\file\[target.xlsx]Sheet1'!$F$4

 

to get the cell value of a file.  If so then I wont need to open and close each file - which is likely what is taking the time.  I would juts need to modify the call / path dynamical from the array in the loop.

 

I will be back at my desk in 15 so will try.

 

Thank you.

[BrewManNH]

Perhaps something like this would work faster.

Global $FieldWanted = $arr[1]
Global $oExcel = _Excel_Open(False)
Global $NumberOfRows = UBound($AList) - 1
Global $File, $Field
For $loop = $NumberOfRows to 0 Step - 1
    $File = _Excel_BookOpenEX($oExcel, @ScriptDir & "\The_File.xlsx", False, True)
    $Field = _Excel_RangeRead($File, Default, "C23", 3)
    _Excel_BookClose($File, False)
    If $Field <> $FieldWanted Then
        _ArrayDelete($AList, $loop)
    Else
        $AList[$loop] = StringRegExpReplace($AList[$loop], "\..*", "")
    EndIf
Next

_Excel_Close($oExcel, False, True)

First, don't keep redeclaring your variables inside the loop, it's not necessary and slows things down.

Second, when deleting rows from an array, you should always go from the highest to the lowest rows or you'll end up with an array element that doesn't exist eventually.

Third, unless this is inside a function, don't use Local for your variables, as they're not Local unless they're in a function. It won't affect speed any, it's just not good coding.

Fourth, this is totally untested

 

[Lights_On]

Hi BrewManNH,

 

Quote

First, don't keep redeclaring your variables inside the loop, it's not necessary and slows things down.

Did not know that - thank you - will implement.

Quote

Second, when deleting rows from an array, you should always go from the highest to the lowest rows or you'll end up with an array element that doesn't exist eventually.

Yes agreed - it did not break yet and I had not noticed i did it that way, would not normal - will change that. thank you.

Quote

Third, unless this is inside a function, don't use Local for your variables, as they're not Local unless they're in a function. It won't affect speed any, it's just not good coding.

All in a function so should be all okay.  

 

I will try this also - I think the main lag is the need to open and close each file.  I should have coded the file name to hold the field I need then the could have just read all names to the array and sorted them there - would have been far faster.  If I cant get a suitable work around I will likely re code and make broader changes.

[Lights_On]

@ SlackerAl

I miss understood you earlier - I see now what you mean and have tested this - but sadly the sheets are also password protected etc and this method - while an interesting view - it not best suited to my need.  Thank you for the idea however.

 

@ BrewManNH

Thank you for the suggestions.  I played with my code making the suggestions you stated but sadly no real 'considerable' speed increase was seen.

 

It seems to me that the 'open' and 'close' of each respective document is what is taking the time.  As such I think I need to change the naming convention of the file(s) so I can asses in an array of data without the need to open each document.

 

[BrewManNH]

I have a question, is this the actual code you're using, or is there more to this section? What I mean is, you're opening and closing the same file over and over again and checking the same spot in the same file every time for the number of loops it's doing, why?

Post your actual code so we can see what is happening in your real script.

[Lights_On]

Hi BrewManNH,

The code was just an example and yes it is part of a far larger script.

The only main difference is list of files, they are all different, hence the need to open each one separately (not the same one over and over). some are relevant, some are not - the code assesses what I need.

The list of files is ever expanding - hence why initially it was not really that slow but as the number of files gradually increases so does the time it takes to asses things. Understandable of course.

An updated example to better show this could be:

 

Local   $FieldWanted            =   $arr[1]
        Local   $oExcel                 =   _Excel_Open(False)
        Local   $NumberOfRows           =   UBound($AList)-1
        
        Local $NumberOfRows = _FileListToArray(@scriptdir) ; in this directory a new file is added daily thus it is ever increasing in size
        While $NumberOfRows <> 0

            Local $File         =   _Excel_BookOpenEX($oExcel, @ScriptDir & "\The_File.xlsx", False, True)
            Local $Field        =   _Excel_RangeRead($File, Default, "C23", 3)
            _Excel_BookClose($File, False)
            If $Field <> $FieldWanted Then
                _ArrayDelete($AList, $NumberOfRows)
            Else
                $AList[$NumberOfRows]=StringRegExpReplace($AList[$NumberOfRows], "\..*", "")
            EndIf

            Local $OldNumberOfRows = $NumberOfRows - 1
            $NumberOfRows = $OldNumberOfRows
        WEnd
        
        _Excel_Close($oExcel, False, True)

 

The above has not adopted the suggestions you made in the prior three points but does hopefully address your question.

 

 

[BrewManNH]

3 minutes ago, Lights_On said:

Local $NumberOfRows = _FileListToArray(@scriptdir) ; in this directory a new file is added daily thus it is ever increasing in size

This returns an array of file names, the rest of your code won't run as is. Please post the real code you're using because this clearly isn't it. 

[Lights_On]

Hi BrewManNH,

As requested:

Local   $List                   =   _FileListToArrayRec(@ScriptDir & "\Path_To_Folder\", "*.xlsx|~$*.xlsx|", 1, 0, 0, 0) 
        Local   $Person                 =   "Me"
        Local   $oExcel                 =   _Excel_Open(False)
        Local   $NumberOfRows           =   UBound($List)-1
        Local   $Incriment              =   100 / $NumberOfRows
        While $NumberOfRows <> 0
            Local $File                 =   _Excel_BookOpenEX($oExcel, @ScriptDir & "\Path_To_Folder\" & $List[$NumberOfRows], False, True)
            Local $Field                =   _Excel_RangeRead($File, Default, "C23", 3)
            _Excel_BookClose($File, False)
            If $Field <> $Person Then
                _ArrayDelete($List, $NumberOfRows)
            Else
                $List[$NumberOfRows]=StringRegExpReplace($List[$NumberOfRows], "\..*", "") 
            EndIf

            Local $OldNumberOfRows = $NumberOfRows - 1
            $NumberOfRows = $OldNumberOfRows
        WEnd
        _Excel_Close($oExcel, False, True)

 

Edited May 20, 2019 by Lights_On

[SlackerAl]

Another thought.... whilst I'm not helping 🙂 

Do all your files change regularly? If not, you might want to log the data and the file timestamp each time you run your script, then only check the files with new time stamps.... Edit: Or even just log the last time you ran your script and check for timestamps after that.

Edited May 20, 2019 by SlackerAl

[Lights_On]

Hi SlackerAI,

You are indeed helping - and I appreciate it! :-)

This too crossed my mind but sadly yes - file content changes daily so a full re asses is required each time.

[BrewManNH]

Using _ArrayDelete with a single number to delete also means that it's using ReDim repeatedly which will slow things down. Try building a delete string instead and only call _arrayDelete once. Or don't call it at all seeing as how the array probably isn't used more than one tim through the loop.

[Lights_On]

Interesting.  I had not considered this. So I could either list all elements that need deleting and delete them at the end in one go or perhaps if a match is found simply copy that match to a new array; both options I assume would be a faster options.  Thank you - I will try this.

[SlackerAl]

Quote

The list of files is ever expanding

This makes me think that the long term strategy is not to open and parse each file - what will be the state of play in 5 years time? How about adding some vba to the files so that they automatically post the filename, date stamp and data to a central repository whenever they are saved?

[Lights_On]

Hi SlackerAI,

I agree - long term even changing the file name and then reading these to an array so it is faster to asses is somewhat flawed.

 

I like the idea of VBA with a central repository but I have not yet tried any au3 with external data stores.  I did do some searching on this once before but i did not seem to get anywhere too fast and at the time the need was less.  if you have any references to point me in the right direction I am happy to go do some more reading.