Search the Community

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 Thanks for the help in advance!

Hi everybody, i want to optimize my search code, because i don't think that my solution is "acceptable" from development perspective, it might be optimized. I have a text file, i read it and i copy all the entries to an Array, then i have to start a search to see if a specific entry is present. I have about ten different strings to search. Func FileSearch($file_content_array, $search) $j=0 For $i = 0 To UBound($file_content_array) - 1 $search_result=StringInStr($file_content_array[$i],$search) If $search_result<>0 Then ReDim $searchResultArray[UBound($searchResultArray) + 1] $searchResultArray [$j] = $file_content_array[$i] $j+=1 EndIf Next Return $searchResultArray EndFunc My solution would be ok for a single search, but in case of multiple searches? would it be still good? I have thought to use a 2D Array, where the first column is the array-item and the second column is "yes" or "no". But what about the searches? Do i have to use 10 times the function? In case of questions: I have to scan a directory and check whether some files are present or not and then write into another file:"yes, it is present", "no, it is not present". Thanks in advance

Hi gang, I'm in a bit of a pickle here, the gist of it is that i have one "main" file with ~200k lines, and literally hundreds of other files each ranging from 1k - 100k lines. I need to go through each of the "other" files and (separately) compare them to the "main" file, and save the differences between the two (no duplicates) The issue is that each comparison (especially if the "other" file has 50+ k lines) takes over a minute each... Is there anyway to cut this time down? As far as i know im using the most optimized array difference script Here's a rough mockup of the script im currently using note: the main file has all unique lines, and the "other" files wont ever have any lines that *DO NOT* appear in the main file, if that helps #include <array.au3> #include <file.au3> global $info_file global $compare_file global $Differece $info_file = FileReadToArray("info-file(200k lines).txt") $compare_file = FileReadToArray("compare-file(100k lines).txt") $Differece = _Diff($info_file, $compare_file, 0) ; get the difference between 2 arrays, NO duplicates _ArrayDisplay($Differece) ;================================================= ; Function Name: _Diff($Set1, $Set2 [, $GetAll=0 [, $Delim=Default]]) ; Description:: Find values in $Set1 that do not occur in $Set2 ; Parameter(s): $Set1 set 1 (1D-array or delimited string) ; $Set2 set 2 (1D-array or delimited string) ; optional: $GetAll 0 - only one occurence of every difference are shown (Default) ; 1 - all differences are shown, allowing duplicates ; optional: $Delim Delimiter for strings (Default use the separator character set by Opt("GUIDataSeparatorChar") ) ; Return Value(s): Succes 1D-array of values from $Set1 that do not occur in $Set2 ; Failure -1 @error set, that was given as array, isn't 1D-array ; Note: Comparison is case-sensitive! - i.e. Number 9 is different to string '9'! ; Author(s): BugFix (bugfix@autoit.de) Modified by ParoXsitiC for Faster _Diff (Formally _GetIntersection) ;================================================= Func _Diff(ByRef $Set1, ByRef $Set2, $GetAll = 0, $Delim = Default) Local $o1 = ObjCreate("System.Collections.ArrayList") Local $o2 = ObjCreate("System.Collections.ArrayList") Local $oDiff1 = ObjCreate("System.Collections.ArrayList") Local $tmp, $i If $GetAll <> 1 Then $GetAll = 0 If $Delim = Default Then $Delim = Opt("GUIDataSeparatorChar") If Not IsArray($Set1) Then If Not StringInStr($Set1, $Delim) Then $o1.Add($Set1) Else $tmp = StringSplit($Set1, $Delim, 1) For $i = 1 To UBound($tmp) - 1 $o1.Add($tmp[$i]) Next EndIf Else If UBound($Set1, 0) > 1 Then Return SetError(1, 0, -1) For $i = 0 To UBound($Set1) - 1 $o1.Add($Set1[$i]) Next EndIf If Not IsArray($Set2) Then If Not StringInStr($Set2, $Delim) Then $o2.Add($Set2) Else $tmp = StringSplit($Set2, $Delim, 1) For $i = 1 To UBound($tmp) - 1 $o2.Add($tmp[$i]) Next EndIf Else If UBound($Set2, 0) > 1 Then Return SetError(1, 0, -1) For $i = 0 To UBound($Set2) - 1 $o2.Add($Set2[$i]) Next EndIf For $tmp In $o1 If Not $o2.Contains($tmp) And ($GetAll Or Not $oDiff1.Contains($tmp)) Then $oDiff1.Add($tmp) Next If $oDiff1.Count <= 0 Then Return 0 Local $aOut[$oDiff1.Count] $i = 0 For $tmp In $oDiff1 $aOut[$i] = $tmp $i += 1 Next Return $aOut EndFunc ;==>_Diff

XCleaner is a simple tool for deleting temporary files and fixing extension problems. OS X Yosemite-like UI is not a part of this app. For now. Download Moderator note: Given the report below be very careful if you decide to download and run this - make sure you take sensible precautions. M23