replace items from arrays very slow due to 65000 rows capacity

[kemo1987]

Hello
I have a problem when executing this code it takes very long time 

i even run it on hp server with 12g ram  and 12 core cpu still very slow
after i run it it loads the 2 sheets into arrays in seconds then keep loading forever

the main usage of it is to load all itmes card and replace quantity and price from Agouza.xlsx 
is there any other ways to do the same scenario but faster

Thanks in advance 

Itemcard.xlsx       about 65000 rows

SKU    Barcode    reason    start_date    end_date    Campaing_status    discounted_price    max_no_of_orders    original_price    active
84729                    0            0    0
114032                    0            0    0
87306                    0            0    0
 

Agouza.xlsx    about 7000 rows

Code   quantity   price
97918    4    22.5
105628    1    27.5
48991    1    1800
49073    1    400
 

 

 

expandcollapse popup

#include <INet.au3> ; needed for get source (hmtl)
#include <String.au3> ; needed for stringbetween
#include<Array.au3> ;
#include <Date.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>
#include <File.au3>
#include <Array.au3>
;====================================
Global $brname="agouza"
Global $htfilename="agouzaold.htm"
Global $exfilename= "agouzaold.xls"
Global $path = "D:\Desktop\Autoit project\"
;====================================
_movetosheet()
Func _movetosheet()
    ;-----------------------------mydata current stock---------------
    Local $oExcel =_Excel_Open()
    $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\itemcard.xlsx")
    $datawb.worksheets("Sheet1").select
    $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
    $mydata = _Excel_RangeRead($datawb, Default, Default )

    _Excel_BookClose($datawb)
    sleep(1000)
    $oExcel.Quit()
    sleep(1000)
    ;   ----------------------------------------------------------
Local $oExcel =_Excel_Open()
    $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Agouza.xlsx")
    $datawb.worksheets("Sheet1").select
    $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
    $mydata2 = _Excel_RangeRead($datawb, Default, Default )
    ;_ArrayColDelete($mydata2, 3)
    ;_ArrayDelete($mydata2, 0)
    _Excel_BookClose($datawb)
    sleep(1000)
    $oExcel.Quit()

    ;;--------------------Add quantity & price to mydata ------------------
    ;~ Loop through $mydata2
    For $i = 1 To UBound($mydata2)-1
        If StringStripWS($mydata2[$i][0],7) = "" Then ContinueLoop
        ;~ Loop through $mydata
        For $j = 1 To UBound($mydata)-1
            ;~ Check if $mydata[x][column 0] = $mydata2[x][column 0] and set $mydata[x][column 4] to $mydata2[x][column 1]
            If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][8]=$mydata2[$i][2]  ;; Quantity 1>0
                If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][9]=$mydata2[$i][1]  ;price
            Next
        Next
;---------------------mydata Set on------------------DOne
     For $i = UBound($mydata) - 1 To 1 Step -1
      If Number($mydata[$i][9])="" Then $mydata[$i][9] =0
      Next

         For $i = UBound($mydata) - 1 To 1 Step -1
      If Number($mydata[$i][9])>1 Then $mydata[$i][9] =1
      Next
      ;--------------mydata set unwanted items to zero
    Local $filea = @ScriptDir & "\inc\snagouza.txt"
    Local $filesn
    _FileReadToArray($filea, $filesn)
    For $i = 1 To UBound($filesn) - 1
        For $j = UBound($mydata) - 1 To 1 Step -1
            If Number($mydata[$j][0]) = $filesn[$i] Then $mydata[$j][9] =0
            ;_ArrayDelete($mydata, $j)

        Next
    Next
      ;'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ DOne
;===========================================
    ;If IsArray($mydata) Then _ArrayDisplay($mydata)

    ;====================================================
;--------------------------------create open & save---------------------------
    Global $pExcel = _Excel_Open()
    sleep(1000)
    Global $oWorkbook = _Excel_BookNew($pExcel, 1)
    Global $sWorkbook = @ScriptDir & "\agouza-final.csv"
    _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $mydata, "a1", Default, True)
    _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True)
    _Excel_BookClose($oWorkbook)
    $oExcel.Quit()


    EndFunc

 

 

[Danp2]

12 minutes ago, kemo1987 said:

i even run it on hp server with 12g ram  and 12 core cpu still very slow

That doesn't surprise me since AutoIt is single threaded.

Have you isolated which section of code is causing the slowdown?

[kemo1987]

14 minutes ago, Danp2 said:

That doesn't surprise me since AutoIt is single threaded.

Have you isolated which section of code is causing the slowdown?

i have tried with 1000 rows sheet instead of 65000 it takes about 1 min

the slowdown happening when replace items from arrays cell by cell

Here

 If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][8]=$mydata2[$i][2] ;; Quantity 1>0

  If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][9]=$mydata2[$i][1] ;price

Edited December 28, 2023 by kemo1987

[argumentum]

1 hour ago, kemo1987 said:

Itemcard.xlsx       about 65000 rows

can you treat it as a database or use SQLite ?

[rudi]

Hello,

the Excel.au3 UDF is interacting with the Excel application. Maybe one of the authors can jump in to answer, if the speed is limited by this "Excel Remoting"? (See header -> author in excel.au3)

 

Quite a while ago I noticed, that using powershell to convert excel files to CSV files, then work with these CSV files, is to me easier and much faster.

 

Have a look at this free powershell module, that even doesn't need the Excel application to be installed:

 

https://www.powershellgallery.com/packages/ImportExcel/7.8.6

I use that one in conjunction with Autoit to process the data, that I need do plot to a temperature graph (image).

 

import-excel $XLfile -WorksheetName $WSName| convertto-csv -notypeinfo | out-file $CSVFPFN -encoding utf8

 

Maybe doing the replacements using regex line by line (instead of array-cell-by-cell) might speedup the procedure?

 

You also might look at the results, when placing in your loops some consolewrites to follow up the speed:

ConsoleWrite("ScriptLine: " & @ScriptLineNumber & ", $i = " & $i & @CRLF)

 

[Danp2]

You could try optimizing your code to see if that improves the speed. A few areas to investigate --

• Use _ArraySearch / _ArrayBinarySearch instead of manual looping
• Perform actions once instead of every pass through the array
  • StringStripWS could be used to clean the array once
  • UBound could be saved to a variable and then reuses

[kemo1987]

after some help of chatgpt i get this answer using dictionary method
and now it takes >Exit code: 0    Time: 109.1
thanks everyone

The nested loops comparing each element from two arrays can indeed cause performance issues, especially with large datasets. One way to optimize this section is to use a more efficient approach like a hash table or dictionary to store and access the data.

expandcollapse popup

#include <INet.au3> ; needed for get source (hmtl)
#include <String.au3> ; needed for stringbetween
#include<Array.au3> ;
#include <Date.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>
#include <File.au3>
#include <Array.au3>

;====================================
Global $brname="agouza"
Global $htfilename="agouzaold.htm"
Global $exfilename= "agouzaold.xls"
Global $path = "D:\Desktop\Autoit project\"
;====================================
_movetosheet()
Func _movetosheet()
    ;-----------------------------mydata current stock---------------
    Local $oExcel =_Excel_Open()
    $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\itemcard.xlsx")
    $datawb.worksheets("Sheet1").select
    $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
    $mydata = _Excel_RangeRead($datawb, Default, Default )

    _Excel_BookClose($datawb)
    sleep(1000)
    $oExcel.Quit()
    sleep(1000)
    ;   ----------------------------------------------------------
Local $oExcel =_Excel_Open()
    $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Agouza.xlsx")
    $datawb.worksheets("Sheet1").select
    $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
    $mydata2 = _Excel_RangeRead($datawb, Default, Default )
    ;_ArrayColDelete($mydata2, 3)
    ;_ArrayDelete($mydata2, 0)
    _Excel_BookClose($datawb)
    sleep(1000)
    $oExcel.Quit()

    ;;--------------------Add quantity & price to mydata ------------------
    ;~ Loop through $mydata2
    ; Your existing code...

; The optimization block using dictionary


Local $data2Dict = ObjCreate("Scripting.Dictionary")
For $i = 1 To UBound($mydata2) - 1
    $data2Dict(StringStripWS($mydata2[$i][0], 7)) = $mydata2[$i][1] & "|" & $mydata2[$i][2]
Next

For $j = 1 To UBound($mydata) - 1
    Local $key = StringStripWS($mydata[$j][0], 7)
    If $data2Dict.Exists($key) Then
        Local $values = StringSplit($data2Dict($key), "|")
        $mydata[$j][9] = $values[1]
        $mydata[$j][8] = $values[2]
    EndIf
 Next

; End of optimization block

; The rest of your existing code...
    ;~ Loop through $mydata2
;~  For $i = 1 To UBound($mydata2)-1
;~      If StringStripWS($mydata2[$i][0],7) = "" Then ContinueLoop
;~      ;~ Loop through $mydata
;~      For $j = 1 To UBound($mydata)-1
;~          ;~ Check if $mydata[x][column 0] = $mydata2[x][column 0] and set $mydata[x][column 4] to $mydata2[x][column 1]
;~          If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][8]=$mydata2[$i][2]  ;; Quantity 1>0
;~              If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][9]=$mydata2[$i][1]  ;price
;~          Next
;~      Next



    ;;===================================
;---------------------mydata Set on------------------DOne
     For $i = UBound($mydata) - 1 To 1 Step -1
      If Number($mydata[$i][9])="" Then $mydata[$i][9] =0
      Next

         For $i = UBound($mydata) - 1 To 1 Step -1
      If Number($mydata[$i][9])>1 Then $mydata[$i][9] =1
      Next
;~    ;--------------mydata set unwanted items to zero
    Local $filea = @ScriptDir & "\inc\snagouza.txt"
    Local $filesn
    _FileReadToArray($filea, $filesn)
    For $i = 1 To UBound($filesn) - 1
        For $j = UBound($mydata) - 1 To 1 Step -1
            If Number($mydata[$j][0]) = $filesn[$i] Then $mydata[$j][9] =0
            ;_ArrayDelete($mydata, $j)

        Next
    Next
      ;'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ DOne
      ; Load the unwanted items into a dictionary
;~ Local $unwantedItemsDict = ObjCreate("Scripting.Dictionary")
;~ Local $filea = @ScriptDir & "\inc\snagouza.txt"
;~ Local $filesn
;~ _FileReadToArray($filea, $filesn)
;~ For $i = 1 To UBound($filesn) - 1
;~     $unwantedItemsDict($filesn[$i]) = True
;~ Next

;~ ; Loop through the data and set unwanted items to zero
;~ For $j = 1 To UBound($mydata) - 1
;~     If $unwantedItemsDict.Exists($mydata[$j][0]) Then
;~         $mydata[$j][9] = 0
;~     EndIf
;~ Next

;===========================================
    ;If IsArray($mydata) Then _ArrayDisplay($mydata)

    ;====================================================
;--------------------------------create open & save to new xlsx---------------------------
    Global $pExcel = _Excel_Open()
    sleep(1000)
    Global $oWorkbook = _Excel_BookNew($pExcel, 1)
    sleep(1000)
    Global $sWorkbook = @ScriptDir & "\agouza-final.csv"
    sleep(1000)
    _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True)
    sleep(1000)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $mydata, "a1", Default, True)
    sleep(1000)
    _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True)
    Sleep(3000)
    _Excel_BookClose($oWorkbook)
    Sleep(3000)
    $oExcel.Quit()
    sleep(2000)



    EndFunc

 

[Andreik]

@argumentum gave you a pertinent direction to deal with in AutoIt. Load your data in a database and do your processing way more faster than in a loop.

[junkew]

Get your data sorted with usedrange.sort or https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_RangeSort.htm

Excel will quickly sort it for you

Then it's just a  loop on biggest array something like below

i=0
j=0
While I <= ubound mydata

If Mydata[i] = mydata2[j] then

...

  i=i +1

Else

  j=j+1

Endif

 

Edited December 29, 2023 by junkew