Jump to content
Sign in to follow this  
cheesestain

Is there any way to make this run faster?

Recommended Posts

cheesestain

It takes a really long time to process a file that has thousands of lines.

Is there any way to make this run faster?

#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#Include <File.au3>
#Include <Array.au3>
#include <Excel.au3>
#include <ExcelBookSaveAs2.au3>

$Replacer = GUICreate("ASN 2 Excel", 515, 130,-1, -1, -1, 0x00000010)
$PathLabel = GUICtrlCreateLabel("ASN File:", 58, 20, 88, 17)
$PathInput = GUICtrlCreateInput("", 110, 15, 310, 21)
$ASNOpen = GUICtrlCreateButton("Open",430,15,55,20)


$OutputFile = GUICtrlCreateInput("", 110, 45, 310, 21)
$OutputFileOpen = GUICtrlCreateButton("Open",430,45,55,20)
$Process = GUICtrlCreateButton("&Process",160,85,80,30)
$Clear = GUICtrlCreateButton("Clea&r",275,85,80,30)
$Fill = GUICtrlCreateCheckbox("&Output File:",32,50,70,15)

GUICtrlSetState($OutputFile,$GUI_FOCUS)
GUISetState(@SW_SHOW)
GUICtrlSetState($PathInput,$GUI_DROPACCEPTED)
GUICtrlSetState($OutputFile,$GUI_DROPACCEPTED)

GUISetState()

;Global $VendorSKU, $SKU, $Counter, $Vendor, $VendorCode

Func Go()

Dim $aRecords

$Path = GUICtrlRead($PathInput)
_FileReadToArray($Path,$aRecords)

If Not IsArray($aRecords) Then
    MsgBox(16,"Error","No file to process")
Else
    $Counter = 2
    $FileCounter = 0

    ProgressOn("Progress", "Generating Excel File", "0 percent")

        Local $oExcel = _ExcelBookNew(0)
        _ExcelWriteCell($oExcel,"SKU",1,1)
        _ExcelWriteCell($oExcel,"IMEI",1,2)
        _ExcelWriteCell($oExcel,"UNIQUE SKID IDENTIFIER",1,3)
        _ExcelWriteCell($oExcel,"MASTER CARTON #",1,4)

        $oExcel.Range("A:D").Select
        $oExcel.Selection.NumberFormat = "@" ; To set a Text format


        ;_ArrayDisplay($aRecords)
        For $i = 2 to $aRecords[0]
            ProgressSet( 100/$aRecords[0]*$i,Round(100/$aRecords[0]*$i) & "%")
            $VendorSKU = StringStripWS(StringMid($aRecords[$i],31,20),2)
            $IMEI = StringStripWS(StringMid($aRecords[$i],81,20),2)
            $UniqueSkidIdentifier = StringStripWS(StringMid($aRecords[$i],251,10),2)
            $MasterCarton = StringStripWS(StringMid($aRecords[$i],266,20),2)

            Local $var = IniReadSection("settings.ini",$VendorSKU)

            If @error Then
                MsgBox(16,"Error","SKU not found")
            Else
                ;_ArrayDisplay($var,"Mapping")
                $SKU = $var[1][1]
                _ExcelWriteCell($oExcel,$SKU,$Counter,1)
                _ExcelWriteCell($oExcel,$IMEI,$Counter,2)
                _ExcelWriteCell($oExcel,$UniqueSkidIdentifier,$Counter,3)
                _ExcelWriteCell($oExcel,$MasterCarton,$Counter,4)
                $Counter += 1
            EndIf
    Next
;       ProgressSet(100, "Done", "Complete")
;       ProgressOff()


        $oExcel.ActiveSheet.Columns("A:D").AutoFit
        ;$oExcel.ActiveSheet.Columns(1).AutoFit
        ;$oExcel.ActiveSheet.Columns(2).AutoFit
        ;$oExcel.ActiveSheet.Columns(3).AutoFit
        ;$oExcel.ActiveSheet.Columns(4).AutoFit
        _ExcelFontSetProperties($oExcel,1,1,1,4,True,False,False)

        $oExcel.Range("A1").Select
        $oExcel.Selection.AutoFilter

        _ExcelBookSaveAs2($oExcel,GUICtrlRead($OutputFile),"xlsx",0,1)
        _ExcelBookClose($oExcel)
        ProgressSet(100, "Done", "Complete")
        ProgressOff()

        MsgBox(64,"Complete","Excel file created")

    EndIf
EndFunc

While 1
    $msg = GUIGetMsg()
    Select

        Case $msg = $ASNOpen
            If StringLen(GUICtrlRead($PathInput)) = 0 Then
                MsgBox(0,"Error","Missing ASN File")
            Else
                ShellExecute(GUICtrlRead($PathInput))
            EndIf
        Case $msg = $OutputFileOpen
            If StringLen(GUICtrlRead($OutputFile)) = 0 Then
                MsgBox(0,"Error","Missing Output File")
            Else
                ShellExecute(GUICtrlRead($OutputFile))
            EndIf

        Case $msg = $Clear
            GUICtrlSetData($PathInput,"")
            GUICtrlSetData($OutputFile,"")
            GUICtrlSetState($Fill, $GUI_UNCHECKED)

        Case $msg = $Process
            Local $szDrive, $szDir, $szFName, $szExt
            Local $PathSplit = _PathSplit(GUICtrlRead($OutputFile), $szDrive, $szDir, $szFName, $szExt)
            ;_ArrayDisplay($PathSplit, "Demo _PathSplit()")
            If StringLen(GUICtrlRead($PathInput)) = 0 Then
                MsgBox(16,"Error","Missing ASN File")
            ElseIf Not FileExists(GUICtrlRead($PathInput)) Then
                MsgBox(16,"Error","ASN File not found")
            ElseIf Not FileExists("settings.ini") Then
                MsgBox(16,"Error","settings.ini not found")
            ;ElseIf GUICtrlRead($PathInput) = StringTrimRight($szDrive & $szDir,1) Then
            ;;ElseIf StringInStr(GUICtrlRead($OutputFile),GUICtrlRead($PathInput)) Then
            ;   MsgBox(16,"Error"," Output File path cannot be the same as the ASN Files Path.")
            ElseIf StringLen(GUICtrlRead($OutputFile)) = 0 Then
                MsgBox(16,"Error","Missing Output File")
            ElseIf Not FileExists($szDrive & $szDir) Then
                $CreateFolder = MsgBox(292,"Path Not Found",$szDrive & $szDir & " not found." & @CRLF & @CRLF & "Create folder?")
                If $CreateFolder= 7 Then
                ElseIf $CreateFolder= 6 Then
                    DirCreate($szDrive & $szDir)
                    Go()
                EndIf
            ElseIf FileExists(GUICtrlRead($OutputFile)) Then
                $Overwrite = MsgBox(292,"File Exists","Overwrite " & GUICtrlRead($OutputFile) & "?")
                If $Overwrite = 7 Then
                ElseIf $Overwrite = 6 Then
                    Go()
                EndIf
            Else
                Go()
            EndIf

        Case $msg = $Fill
            If GUICtrlRead($Fill) = $GUI_CHECKED Then
                If StringLen(GUICtrlRead($PathInput)) = 0 Then
                    MsgBox(0,"Error","Missing ASN File")
                    GUICtrlSetState($Fill, $GUI_UNCHECKED)
                Else
                    $Error = 0
                    $ASNFile = FileOpen(GUICtrlRead($PathInput),0)
                    $ReadHeader = FileReadLine($ASNFile,1)
                    $VendorID = StringStripWS(StringMid($ReadHeader,53,10),2)
                    $PO = StringStripWS(StringLeft($ReadHeader,20),2)
                    $QTY = _FileCountLines(GUICtrlRead($PathInput)) - 1
                    $ReadDetail = FileReadLine($ASNFile,2)
                    $VendorSKU = StringStripWS(StringMid($ReadDetail,31,20),2)


                    Local $var = IniReadSection("settings.ini",$VendorID)
                    If @error Then
                        MsgBox(16, "Warning", "Vendor ID: " & $VendorID & " not found")
                        $VendorName = "VENDOR"
                        $Error = 1
                    Else
                        $VendorName = $var[1][1]
                    EndIf


                    Local $var2 = IniReadSection("settings.ini",$VendorSKU)
                    If @error Then
                        MsgBox(16, "Warning", "SKU: " & $VendorSKU & " not found")
                        $SKU = "SKU"
                        $Error = 1
                    Else
                        $SKU = $var2[1][1]
                    EndIf


                    FileClose($ASNFile)
                    ;MsgBox(0,"Test","Vendor: " & $VendorName & @CRLF & "PO: " & $PO & @CRLF & "QTY: " & $QTY & @CRLF & "SKU: " & $SKU)
                    $Folder = IniRead(@WorkingDir & "\settings.ini", "copyto", "excelpath", "default")

                    If $Error = 1 Then
                        GUICtrlSetData($OutputFile,"")
                        GUICtrlSetState($Fill, $GUI_UNCHECKED)
                    Else
                        GUICtrlSetData($OutputFile,$Folder & "\" & $VendorName & " - " & $SKU & " - PO " & $PO & " - QTY " & $QTY & " - " & @MON & " " & @MDAY & " " & @YEAR & " " & @HOUR & @MIN & ".xlsx")
                    EndIf

                EndIf

            ElseIf GUICtrlRead($Fill) = $GUI_UNCHECKED Then
                GUICtrlSetData($OutputFile,"")
            EndIf

        Case $msg = $GUI_EVENT_CLOSE

            ExitLoop
    EndSelect
WEnd

Share this post


Link to post
Share on other sites
LarsJ

There are two easy things you can do:

1. Instead of reading the inifile for every single row in the loop:

Local $var = IniReadSection("settings.ini",$VendorSKU)
you should read the inifile into an array in top of your script. Then you can extract the information from the array.

2. Store the results in an array instead of calling _ExcelWriteCell that many times. When the loop is finished write the entire array to Excel with one single command: _ExcelWriteArray.

Share this post


Link to post
Share on other sites
water

If _ExcelWriteArray is still too slow then you could give my rewrite of the Excel UDF a try (still in beta and needs the latest beta of AutoIt). It's about 20 - 100 times faster when writing arrays to a Excel worksheet.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
cheesestain

I'm trying option 2

2. Store the results in an array instead of calling _ExcelWriteCell that many times. When the loop is finished write the entire array to Excel with one single command: _ExcelWriteArray.

But I can't figure out how to declare the array and the number of elements

I'm trying to do so by using $aRecords[0] which should be the number of records in the file I want to process.

Local $aIMEI[$aRecords[0]]

Then in my for loop i put

$aIMEI[$aRecords[$i]] = "test"

But when i check using array display it keeps storing it in 0

Any suggestions on how i can get started?

Share this post


Link to post
Share on other sites
LarsJ

I would do something like this:

Before the loop:

Local $aIMEI[$aRecords[0]+1][4] ; 4 columns, seems to be A - D in Excel

The Else-part of the If-statement:

;_ArrayDisplay($var,"Mapping")
;$SKU = $var[1][1] ; $SKU not necessary
$aIMEI[$i][0] = $var[1][1]
$aIMEI[$i][1] = $IMEI
$aIMEI[$i][2] = $UniqueSkidIdentifier
$aIMEI[$i][3] = $MasterCarton
;$Counter += 1 ; Not needed

After the loop:

_ExcelWriteArray($oExcel, 2, 0, $aIMEI)

You should definitely take a look at that IniReadSection-statement. You are opening a file, reading the file, extracting the proper section and closing the file. For every row. Even if the file is cached after first row this is still very very very slow.

If you only have a few different values of $VendorSKU you can use a simple Switch...Case...EndSwitch (very very very fast). If you have many values use an array.

Now that your script is flying you should not update the progress bar for every row. Do it for every 1000 rows:

If Not Mod( $i, 1000 ) Then ProgressSet( 100/$aRecords[0]*$i,Round(100/$aRecords[0]*$i) & "%")

Share this post


Link to post
Share on other sites
cheesestain

LarsJ:

Thanks for your help.

I couldn't figure out why the array wouldn't write to the excel sheet.  I had to use _ExcelWriteSheetFromArray instead.  _ExcelWriteArray doesn't support 2D arrays

_ExcelWriteSheetFromArray($oExcel, $aIMEI,2,1,2,0)

The script seems to run a bit faster.  But most of the processing time is writing to the excel sheet.  The IniReadSection is pretty fast

Share this post


Link to post
Share on other sites
cheesestain

If _ExcelWriteArray is still too slow then you could give my rewrite of the Excel UDF a try (still in beta and needs the latest beta of AutoIt). It's about 20 - 100 times faster when writing arrays to a Excel worksheet.

 

I will test the script using _Excel_RangeWrite and see if it's any faster.

Share this post


Link to post
Share on other sites
water

Should be much faster because the "old" Excel UDF writes cell by cell whereas the "new" UDF writes the array in one go.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

What do you refer to?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Oops, looks like I need glasses ;) Fixed. Thanks.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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
Sign in to follow this  

×