cheesestain Posted September 25, 2013 Share Posted September 25, 2013 It takes a really long time to process a file that has thousands of lines. Is there any way to make this run faster? expandcollapse popup#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 Link to comment Share on other sites More sharing options...
LarsJ Posted September 25, 2013 Share Posted September 25, 2013 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. Controls, File Explorer, ROT objects, UI Automation, Windows Message MonitorCompiled code: Accessing AutoIt variables, DotNet.au3 UDF, Using C# and VB codeShell menus: The Context menu, The Favorites menu. Shell related: Control Panel, System Image ListsGraphics related: Rubik's Cube, OpenGL without external libraries, Navigating in an image, Non-rectangular selectionsListView controls: Colors and fonts, Multi-line header, Multi-line items, Checkboxes and icons, Incremental searchListView controls: Virtual ListViews, Editing cells, Data display functions Link to comment Share on other sites More sharing options...
water Posted September 25, 2013 Share Posted September 25, 2013 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
cheesestain Posted September 26, 2013 Author Share Posted September 26, 2013 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? Link to comment Share on other sites More sharing options...
DatMCEyeBall Posted September 26, 2013 Share Posted September 26, 2013 Shouldn't that be: $aIMEI[$i - 2] = "test" "Just be fred, all we gotta do, just be fred." -Vocaliod "That is a Hadouken. A KAMEHAMEHA would have taken him 13 days and 54 episodes to form." - Roden Hoxha @tabhooked Clock made of cursors ♣ Desktop Widgets ♣ Water Simulation Link to comment Share on other sites More sharing options...
LarsJ Posted September 26, 2013 Share Posted September 26, 2013 I would do something like this:Before the loop:Local $aIMEI[$aRecords[0]+1][4] ; 4 columns, seems to be A - D in ExcelThe 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 neededAfter 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) & "%") Controls, File Explorer, ROT objects, UI Automation, Windows Message MonitorCompiled code: Accessing AutoIt variables, DotNet.au3 UDF, Using C# and VB codeShell menus: The Context menu, The Favorites menu. Shell related: Control Panel, System Image ListsGraphics related: Rubik's Cube, OpenGL without external libraries, Navigating in an image, Non-rectangular selectionsListView controls: Colors and fonts, Multi-line header, Multi-line items, Checkboxes and icons, Incremental searchListView controls: Virtual ListViews, Editing cells, Data display functions Link to comment Share on other sites More sharing options...
cheesestain Posted September 27, 2013 Author Share Posted September 27, 2013 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 Link to comment Share on other sites More sharing options...
cheesestain Posted September 27, 2013 Author Share Posted September 27, 2013 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. Link to comment Share on other sites More sharing options...
water Posted September 27, 2013 Share Posted September 27, 2013 (edited) Should be much faster because the "old" Excel UDF writes cell by cell whereas the "new" UDF writes the array in one go. Edited September 27, 2013 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
DatMCEyeBall Posted September 27, 2013 Share Posted September 27, 2013 Shouldn't the second "in" be "one"? "Just be fred, all we gotta do, just be fred." -Vocaliod "That is a Hadouken. A KAMEHAMEHA would have taken him 13 days and 54 episodes to form." - Roden Hoxha @tabhooked Clock made of cursors ♣ Desktop Widgets ♣ Water Simulation Link to comment Share on other sites More sharing options...
water Posted September 27, 2013 Share Posted September 27, 2013 What do you refer to? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
DatMCEyeBall Posted September 27, 2013 Share Posted September 27, 2013 Post #9 "Just be fred, all we gotta do, just be fred." -Vocaliod "That is a Hadouken. A KAMEHAMEHA would have taken him 13 days and 54 episodes to form." - Roden Hoxha @tabhooked Clock made of cursors ♣ Desktop Widgets ♣ Water Simulation Link to comment Share on other sites More sharing options...
water Posted September 27, 2013 Share Posted September 27, 2013 Oops, looks like I need glasses Fixed. Thanks. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki 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