Jump to content

Optimizing reading data from Excel


Recommended Posts

Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average :(

Here is my testing setup that I made:

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global $iTotalTime = 0

Test()

Func Test()
    Local $oExcel = _Excel_Open()
    Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel)
    Local $sSheet = "Sheet1"
    If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel")

    Local $iNum
    For $iRow = 1 To 6
        Time()
        Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow))
        If ($iNum = 1) Then
            ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow))
        Else
            ConsoleWrite("Row " & $iRow & " is not 1")
        EndIf
        ConsoleWrite(". Reading took: ")
        Time()
    Next
    ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF)
EndFunc

Func Time()
    Local Static $hTimer
    Local Static $bRunning = False
    If $bRunning Then
        Local $iTime = Round(TimerDiff($hTimer), 2)
        $iTotalTime += $iTime
        ConsoleWrite($iTime & @CRLF)
    Else
        $hTimer = TimerInit()
    EndIf
    $bRunning = Not $bRunning
EndFunc

And Test.xlsx in CSV format:

1,-1
-1,1
1,-1
1,1
-1,-1
1,1

Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6

And finally output from my script:

Row 1 is 1 and value of column B is -1. Reading took: 276.06
Row 2 is not 1. Reading took: 163.36
Row 3 is 1 and value of column B is -1. Reading took: 302.58
Row 4 is 1 and value of column B is 1. Reading took: 294.65
Row 5 is not 1. Reading took: 152.33
Row 6 is 1 and value of column B is 1. Reading took: 284.92
The whole operation took 1473.9 milliseconds.

 

Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible :(. It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script :D

 

Thanks for the help in advance!

Edited by SDL
excel -> Excel

EasyCodeIt - A cross-platform AutoIt implementation - Fund the development! (GitHub will double your donations for a limited time)

DcodingTheWeb Forum - Follow for updates and Join for discussion

Link to comment
Share on other sites

7 minutes ago, FrancescoDiMuro said:

Does this file need to be in XLSX or you can convert it in CSV format as you did above? :)

Good question, the only reason I am using XLSX is because I need Excel to automatically update those values via an addon. Otherwise I would simply have used CSV or anything else which is appropriate :)

I gave the CSV because it is easier to paste it here... and I could not provide the xlsx file permanently without using up my attachment space.

EasyCodeIt - A cross-platform AutoIt implementation - Fund the development! (GitHub will double your donations for a limited time)

DcodingTheWeb Forum - Follow for updates and Join for discussion

Link to comment
Share on other sites

As you are only reading a few rows/columns I would use _Excel_RangeRead to read the whole used range and then process the returned array.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

@water & @kaisies I see, I will try adapting my script to reduce calls to RangeRead and see how it performs :)

Will report back once I test it!

EasyCodeIt - A cross-platform AutoIt implementation - Fund the development! (GitHub will double your donations for a limited time)

DcodingTheWeb Forum - Follow for updates and Join for discussion

Link to comment
Share on other sites

I have modified my original script to read all the data once instead of one by one for every cell... and boy are the speed gains huge! :D

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global $iTotalTime = 0

Test()

Func Test()
    Local $oExcel = _Excel_Open()
    Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel)
    Local $sSheet = "Sheet1"
    If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel")

    Local $iNum
    Time()
    Local $aData = _Excel_RangeRead($oBook, $sSheet, "A1:B6")
    Time()
    _ArrayDisplay($aData)
    For $iRow = 0 To 5
        Time()
        Local $iNum = Number($aData[$iRow][0])
        If $iNum = 1 Then
            ConsoleWrite("Row " & $iRow + 1 & " is 1 and value of column B is " & $aData[$iRow][0])
        Else
            ConsoleWrite("Row " & $iRow + 1 & " is not 1")
        EndIf
        ConsoleWrite(". Reading took: ")
        Time()
    Next
    ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF)
EndFunc

Func Time()
    Local Static $hTimer
    Local Static $bRunning = False
    If $bRunning Then
        Local $iTime = Round(TimerDiff($hTimer), 2)
        $iTotalTime += $iTime
        ConsoleWrite($iTime & @CRLF)
    Else
        $hTimer = TimerInit()
    EndIf
    $bRunning = Not $bRunning
EndFunc
120.15
Row 1 is 1 and value of column B is 1. Reading took: 0.07
Row 2 is not 1. Reading took: 0.02
Row 3 is 1 and value of column B is 1. Reading took: 0.02
Row 4 is 1 and value of column B is 1. Reading took: 0.02
Row 5 is not 1. Reading took: 0.01
Row 6 is 1 and value of column B is 1. Reading took: 0.02
The whole operation took 120.31 milliseconds.

From ~1500 ms to ~120 ms, that is a huge 12.5x improvement in speed :thumbsup:

And even most of that is the first initial call to Angered, definitely going to try this method of caching in my project... I actually feel a bit dumb not having tried this before asking :lol:

EasyCodeIt - A cross-platform AutoIt implementation - Fund the development! (GitHub will double your donations for a limited time)

DcodingTheWeb Forum - Follow for updates and Join for discussion

Link to comment
Share on other sites

You learn something new each day :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I discovered another way to hugely cut up the speed... by using the inbuilt AutoIt transpose function! Just set $bForceFunc parameter to true and enjoy the gains :)

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global $iTotalTime = 0

Test()

Func Test()
    Local $oExcel = _Excel_Open()
    Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel)
    Local $sSheet = "Sheet1"
    If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel")

    Local $iNum
    Time()
    Local $aData = _Excel_RangeRead($oBook, $sSheet, "A1:B6", 1, True)
    Time()
    _ArrayDisplay($aData)
    For $iRow = 0 To 5
        Time()
        Local $iNum = Number($aData[$iRow][0])
        If $iNum = 1 Then
            ConsoleWrite("Row " & $iRow + 1 & " is 1 and value of column B is " & $aData[$iRow][0])
        Else
            ConsoleWrite("Row " & $iRow + 1 & " is not 1")
        EndIf
        ConsoleWrite(". Reading took: ")
        Time()
    Next
    ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF)
EndFunc

Func Time()
    Local Static $hTimer
    Local Static $bRunning = False
    If $bRunning Then
        Local $iTime = Round(TimerDiff($hTimer), 2)
        $iTotalTime += $iTime
        ConsoleWrite($iTime & @CRLF)
    Else
        $hTimer = TimerInit()
    EndIf
    $bRunning = Not $bRunning
EndFunc
24.44
Row 1 is 1 and value of column B is 1. Reading took: 0.04
Row 2 is not 1. Reading took: 0.01
Row 3 is 1 and value of column B is 1. Reading took: 0.01
Row 4 is 1 and value of column B is 1. Reading took: 0.02
Row 5 is not 1. Reading took: 0.01
Row 6 is 1 and value of column B is 1. Reading took: 0.01
The whole operation took 24.54 milliseconds.

From 1500 ms to 120 ms, and finally to 25 ms, that is an astonishing 60x improvement in total :D

The excel function for transposing must really suck... or it accounts for a lot of things that our array transpose function cannot. Either way, I am happy with the outcome.

EasyCodeIt - A cross-platform AutoIt implementation - Fund the development! (GitHub will double your donations for a limited time)

DcodingTheWeb Forum - Follow for updates and Join for discussion

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...