Lights_On Posted May 20, 2019 Share Posted May 20, 2019 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. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted May 20, 2019 Moderators Share Posted May 20, 2019 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 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 Hi Melba23, Thank you for moving the post accordingly and apologies for using the incorrect location - have read an understood your post. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted May 20, 2019 Moderators Share Posted May 20, 2019 Lights_On, No probs - more people are likely to see it here anyway. M23 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 Appreciated. Thank you. Link to comment Share on other sites More sharing options...
SlackerAl Posted May 20, 2019 Share Posted May 20, 2019 (edited) 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 Problem solving step 1: Write a simple, self-contained, running, replicator of your problem. Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 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. Link to comment Share on other sites More sharing options...
BrewManNH Posted May 20, 2019 Share Posted May 20, 2019 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 If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 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. Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 @ 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. Link to comment Share on other sites More sharing options...
BrewManNH Posted May 20, 2019 Share Posted May 20, 2019 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. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 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. Link to comment Share on other sites More sharing options...
BrewManNH Posted May 20, 2019 Share Posted May 20, 2019 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. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 (edited) 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 Link to comment Share on other sites More sharing options...
SlackerAl Posted May 20, 2019 Share Posted May 20, 2019 (edited) 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 Problem solving step 1: Write a simple, self-contained, running, replicator of your problem. Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 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. Link to comment Share on other sites More sharing options...
BrewManNH Posted May 20, 2019 Share Posted May 20, 2019 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. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 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. Link to comment Share on other sites More sharing options...
SlackerAl Posted May 20, 2019 Share Posted May 20, 2019 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? Problem solving step 1: Write a simple, self-contained, running, replicator of your problem. Link to comment Share on other sites More sharing options...
Lights_On Posted May 20, 2019 Author Share Posted May 20, 2019 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now