Very bad performance read from excelsheet Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
mrdigitalis
Posts: 67
Joined: 23 May 2022, 01:17

Very bad performance read from excelsheet

Post by mrdigitalis » 23 May 2022, 16:06

Dear all,

I am new to AHK and I made a little script that reads an excelsheet row by row. But the performance is very bad. It takes 39 seconds to get a result
that is on row 2190. The 39 seconds is with closed excelsheet. When the file opend, it takes 14 seconds is also to long.
Is there a way to get the result faster? Who will help me out? Thanks in advance!

Code: Select all

^m::
LookForDate := "10.04.2022"
LookForName := "Name bla bla (UK) N+G"
LookForHours := 

FilePath :=  A_Desktop "\Uren Uitzendkrachten\Alle UKs 01.01.2022 t-m 14.05.2022.xls"	
oWorkbook := ComObjGet(FilePath)		
i = 2
loop
{ 
    FoundName := oWorkbook.Sheets(1).Range("A"i).Text
    FoundDate := oWorkbook.Sheets(1).Range("D"i).Text
    FoundHours := oWorkbook.Sheets(1).Range("F"i).Text

    if (FoundName = LookForName AND FoundDate = LookForDate)
    {
        MsgBox De gewerkte uren van: %FoundName% in Week %FoundDate% = %FoundHours%
        break
    }
    i++
}

Xl.Quit() ; closes the excel object

dipstick5000
Posts: 31
Joined: 21 Jan 2020, 22:01

Re: Very bad performance read from excelsheet

Post by dipstick5000 » 23 May 2022, 16:52

Do you have a fast computer? I built a fast computer and it's a huge difference. Don't know what to tell you. It's just going to take a few seconds to go through thousands of lines, depending. How much is it worth to you to go from 8 seconds to 2, or something. You might be able to figure out a better way, but how much time will that take? Just my 2 cents. Of course sometimes I like to figure things out no matter how much time it takes. My latest (today's) plea for help is with a simple API request, which I already made work properly, just not as well as it could, and definitely not like a smart person would do. But it works. I could use it, and it would take no additional seconds out of my day, so I understand your deal. Good luck. Hey, maybe you could help with mine... Oh, you're new to AHK, so probably not, but I welcome all thoughts.

dipstick5000
Posts: 31
Joined: 21 Jan 2020, 22:01

Re: Very bad performance read from excelsheet

Post by dipstick5000 » 23 May 2022, 16:58

Oh, I just had a thought which people will tell me is ridiculous. You could turn all 2,000 lines into a single string, which I'm pretty sure would be much faster to search, then once fixed, or whatever you're trying to do, paste it back in using invert or not (rows to columns or vice versa, or again, not). I think outside the box and the brain.

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Very bad performance read from excelsheet

Post by flyingDman » 23 May 2022, 17:23

Going through thousands of lines will take time if you loop through a range like that. So, either you load all lines in the clipboard and search the clipboard or you use a safearray of the entire range and loop through the safearray. Both are significantly faster that what you have (and most of the time will be use to open and close the file). This is the clipboard method:

Code: Select all

LookForDate := "5/22/2022"					; use your date format
LookForName := "EDUJHEVJ"
LookForHours := ""

FilePath :=  A_ScriptDir "\testfile123.xlsx"				; use your path
xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open(FilePath)
xl.activesheet.range("A1:C3000").copy				; or use "usedrange"
for x,y in strsplit(clipboard, "`n", "`r")
	if (strsplit(y,"`t").1 = LookForName) and (strsplit(y,"`t").2 = LookForDate)
		msgbox % strsplit(y,"`t").1 " " strsplit(y,"`t").2 " " strsplit(y,"`t").3
clipboard := ""
wrkbk.close()
xl.quit()
3000 rows: 1.6 seconds.
14.3 & 1.3.7

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Very bad performance read from excelsheet

Post by flyingDman » 23 May 2022, 17:38

14.3 & 1.3.7

dipstick5000
Posts: 31
Joined: 21 Jan 2020, 22:01

Re: Very bad performance read from excelsheet

Post by dipstick5000 » 23 May 2022, 18:14

flyingDman wrote:
23 May 2022, 17:23
Going through thousands of lines will take time if you loop through a range like that. So, either you load all lines in the clipboard and search the clipboard
It's nice to know that people with 1600 posts sometimes agree with us morons that have 20 posts.

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Very bad performance read from excelsheet  Topic is solved

Post by flyingDman » 23 May 2022, 18:26

@dipstick5000 :lol: Yeah, it's not much different than creating a huge string. But my string is structured so I know where to search.

This is the 2nd method I mentioned, using a safearray (no string here). This is a better method as it does not use the clipboard and is just as fast:

Code: Select all

LookForDate := "5/22/2022"
LookForName := "EDUJHEVJ"
FilePath :=  A_ScriptDir "\testfile123.xlsx"
xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open(FilePath)
sarr := xl.activesheet.range("A1:C3000").value
loop, % sarr.maxindex(1)
	if (sarr[a_index,1] = LookForName and sarr[a_index,2] = LookForDate)                   ; adjust to your need A = 1 D = 4 and F = 6
		{
		MsgBox % sarr[a_index,1] " " sarr[a_index,2] " " sarr[a_index,3]
		break
		}
wrkbk.close()
xl.quit()
Last edited by flyingDman on 23 May 2022, 22:39, edited 1 time in total.
14.3 & 1.3.7

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Very bad performance read from excelsheet

Post by flyingDman » 23 May 2022, 18:57

This is the 3rd method. It's by far the fastest (<200 ms) but requires the Microsoft Access Database Engine

Code: Select all

LookForDate := "5/22/2022"
LookForName := "EDUJHEVJ"
FilePath :=  A_ScriptDir "\testfile123.xlsx"
array := getXcells(FilePath,  "sheet1")					    ; probably "blad1" in Dutch
for x,y in array
	if (y.1 = LookForName and y.2 = LookForDate)			; adjust to your need A = 1 D = 4 and F = 6
		msgbox % y.1 " " y.2 " " y.3 						; adjust to your need A = 1 D = 4 and F = 6
return

getXcells(datasource, sheet := "sheet1") ;  requires https://www.microsoft.com/en-us/download/details.aspx?id=54920 
	{
	global colcnt, rowcnt
	arr := [], rowcnt := 0
	objConnection := ComObjCreate("ADODB.Connection"), 	objRecordSet := ComObjCreate("ADODB.Recordset")
	try objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" . dataSource . "; Extended Properties='Excel 12.0 xml;HDR=no;IMEX=1';")
	catch
		{
		msgbox,48,, Error! Data could not be retrieved, 2
		return
		}
	try objRecordset.Open("Select * FROM [" Sheet "$]", objConnection, 3, 3, 1) 			; adOpenStatic = 3 , adLockOptimistic = 3 , adCmdText = 1
	catch
		{
		msgbox,48,, Error! %Sheet% does not exist, 2
		return
		}
	pFields := objRecordset.Fields
	while !objRecordset.EOF
		{
		row := [], ++rowcnt
		Loop, % colcnt := pFields.Count
			row[A_Index] := pFields.Item(A_Index-1).value
		arr.push(row)
		objRecordset.MoveNext
		}
	objRecordSet.Close()
	objConnection.Close()
	objRecordSet := ""
	objConnection := ""
	return arr
	}
14.3 & 1.3.7

garry
Posts: 3743
Joined: 22 Dec 2013, 12:50

Re: Very bad performance read from excelsheet

Post by garry » 24 May 2022, 02:01

is it possible to save as 'xy.csv' and read this textfile ?

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Very bad performance read from excelsheet

Post by flyingDman » 24 May 2022, 10:37

@garry Yeah, you could, but the fact that you have to saveas and then to read the csv file is not very efficient. It takes about 2 seconds. That does not include the parsing part. In addition you end up with a extra file.
14.3 & 1.3.7

mrdigitalis
Posts: 67
Joined: 23 May 2022, 01:17

Re: Very bad performance read from excelsheet

Post by mrdigitalis » 24 May 2022, 13:34

@flyingDman I am using method 2 now and it works perfect! Thank you for your perfect examples! :bravo:

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Very bad performance read from excelsheet

Post by flyingDman » 24 May 2022, 14:24

You are welcome. If "need for speed" becomes more of an issue, try the 3rd version. I am still in disbelief when it comes to how fast it retrieves data. But I agree that for most purposes, the safearray method should be fine. Note that one of the differences between a regular array and a safearray is that the safearray does not allow you to do "for-loops".
14.3 & 1.3.7

Post Reply

Return to “Ask for Help (v1)”