Need to read files created in a folder like 1-100 in a loop but numerically

[Cyborg5000]

expandcollapse popup

Global $sWorkbookPath = "C:\example.xlsx" ; Replace with your workbook path
Global $sWorksheetName = "Sheet1" ; Replace with your worksheet name
Global $xlUp = -4162
Global $sFolder = "D:\test"
Global $oExcel = _Excel_Open() ; Open an instance of Excel
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbookPath) ; Open the workbook


;Call("NOC_SGE")
Call("SGE_Impact_duration")
;Call("SGE_Outage_duration")

;Call("NOC_UGE")
;Call("UGE_Impact_duration")

;Call("UGE_Outage_duration")



;>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

;================================================================Text having SGE_Outage_duration
Func SGE_Impact_duration()

    Local $sStartString = "SGE:"
    Local $sEndString = "Impact Duration:"

    ; Get a list of all text files in the folder
    Local $aFiles = _FileListToArray($sFolder, "*.txt", $FLTA_FILES)

    ; Check if any files were found
    If @error Then
        MsgBox($MB_OK, "Error", "No text files found in folder.")
        Exit
    EndIf


    ; Loop through each text file and search for the string between the start and end strings
    For $i = 1 To $aFiles[0]
        Global $sFilePath = $sFolder & "\" & $aFiles[$i]
        Global $sFileContents = FileRead($sFilePath)
        If @error Then
            MsgBox($MB_OK, "Error", "Error reading file: " & $sFilePath)
        Else
            Global $aMatches = StringRegExp($sFileContents, "(?s)" & $sStartString & "(.*?)" & $sEndString, 4)
            If IsArray($aMatches) Then
                ; String was found between the start and end strings
                Global $sMatch = $aMatches[0]
                ;MsgBox (0, "test", $sMatch)
                Call("SGE_ID_Entry")
            EndIf
        EndIf
    Next
EndFunc   ;==>NOC_SGE_Outage_duration

Func SGE_ID_Entry()
    Local $sColumnName = "C" ; Replace with your column name
    Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet
    Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column
    $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch ; Add the new data to the next row in the column
    $oWorksheet.Cells($iLastRow + 1, "A").Value = "SGE"
    $oWorksheet.Cells($iLastRow + 1, "D").Value = $sFilePath
    ;_Excel_RangeWrite($oWorkbook, Default, $aMatches[$i], "A2" & $iRow)

    _Excel_BookSave($oWorkbook) ; Save the workbook
    _Excel_Close($oExcel) ; Close the Excel instance

EndFunc   ;==>NOC_SGE_OD_Entry
;=============================================================================>NOC_SGE_OD_Entry

Iam using the above code, however iam unable to read the files 1 by 1 in a numerical form starting from 1 - 100 +

My array looks default as below.

I have tried using

_ArraySort($avArray, 0, 0, 0, 1)

 _FileListToArrayRec

Need to sort arry or read files 1, 2, 3,4 ,5 .......and so on

Any help appreciated

 

Edited March 8, 2023 by Cyborg5000

[argumentum]

..I guess you could add zeros to pad the strings like 001.txt, sort them, then remove the pad ? Or don't remove the pad and Int(001) & ".txt" ?

Edited March 9, 2023 by argumentum
Keyboard failure, not me. Never me. =P

[ioa747]

following @argumentum   suggestions i made an example

#include <Array.au3>

Local $aArray[101]
$aArray[0] = 100

For $i = 1 To $aArray[0]
    $aArray[$i] = $i & ".txt"
Next

_ArrayShuffle($aArray, 1)
_ArrayDisplay($aArray, "BEFORE")

_FixIt($aArray)

_ArrayDisplay($aArray, "AFTER")

Func _FixIt(ByRef $Array)
    _ArrayColInsert($Array, 1)
    ; Now a 2D array
    For $i = 1 To $Array[0][0]
        $Array[$i][1] = StringFormat("%010s", $Array[$i][0])
    Next
;~  _ArrayDisplay($Array, "2D array")
    _ArraySort($Array, 0, 1, 0, 1)
    _ArrayColDelete($Array, 1)

    Return $Array

EndFunc   ;==>_FixIt

 

[mistersquirrle]

Yep, as @argumentum said, padding the names is probably the best option.

#include <Array.au3>

Global $aFilenames = FileReadToArray(@ScriptDir & '\filenames.txt')
If @error Then
    ConsoleWrite('FileRead error' & @CRLF)
EndIf
_ArrayDisplay($aFilenames, 'Original')

Global $iMaxLength = 5
For $iIndex = 0 To UBound($aFilenames) - 1
    If StringLen($aFilenames[$iIndex]) > $iMaxLength Then ; Check if the length of this name is more than the current max
        $iMaxLength = StringLen($aFilenames[$iIndex])
        ConsoleWrite('New max filename length (' & $iMaxLength & '), restarting the padding loop' & @CRLF)
        $iIndex = -1 ; Restart the loop to rename/repad with the new length
        ContinueLoop
    EndIf
    $aFilenames[$iIndex] = StringFormat('%0'&$iMaxLength&'s', $aFilenames[$iIndex])
Next
_ArrayDisplay($aFilenames, 'Padded')

_ArraySort($aFilenames)
_ArrayDisplay($aFilenames, 'Sorted')

For $iIndex = 0 To UBound($aFilenames) - 1
    $aFilenames[$iIndex] = StringRegExpReplace($aFilenames[$iIndex], '(?m)^0*', '')
Next
_ArrayDisplay($aFilenames, 'Unpadded')

Using filenames.txt:

expandcollapse popup

174.txt
126.txt
190.txt
105.txt
12.txt
6.txt
120.txt
37.txt
68.txt
103.txt
77.txt
119.txt
142.txt
123.txt
58.txt
151.txt
165.txt
28.txt
88.txt
148.txt
92.txt
91.txt
43.txt
96.txt
177.txt
139.txt
80.txt
8.txt
168.txt
144.txt
196.txt
192.txt
17.txt
195.txt
189.txt
45.txt
49.txt
82.txt
153.txt
47.txt
104.txt
18.txt
40.txt
198.txt
117.txt
42.txt
138.txt
176.txt
152.txt
181.txt
85.txt
186.txt
99.txt
54.txt
48.txt
5.txt
179.txt
183.txt
128.txt
29.txt
25.txt
124.txt
159.txt
98.txt
35.txt
199.txt
113.txt
78.txt
50.txt
27.txt
81.txt
39.txt
140.txt
116.txt
73.txt
121.txt
101.txt
16.txt
146.txt
197.txt
76.txt
11.txt
74.txt
95.txt
67.txt
72.txt
162.txt
134.txt
26.txt
127.txt
63.txt
172.txt
84.txt
200.txt
182.txt
65.txt
180.txt
21.txt
69.txt
154.txt
164.txt
188.txt
62.txt
34.txt
51.txt
178.txt
61.txt
115.txt
107.txt
185.txt
112.txt
111.txt
102.txt
170.txt
89.txt
71.txt
171.txt
10.txt
129.txt
9.txt
169.txt
41.txt
110.txt
109.txt
15.txt
52.txt
55.txt
145.txt
83.txt
143.txt
19.txt
4.txt
191.txt
86.txt
132.txt
23.txt
158.txt
1.txt
60.txt
46.txt
150.txt
157.txt
156.txt
173.txt
141.txt
155.txt
194.txt
30.txt
108.txt
149.txt
137.txt
160.txt
36.txt
75.txt
118.txt
33.txt
32.txt
163.txt
131.txt
193.txt
166.txt
3.txt
125.txt
147.txt
24.txt
135.txt
2.txt
64.txt
22.txt
100.txt
161.txt
167.txt
13.txt
93.txt
14.txt
59.txt
184.txt
57.txt
56.txt
136.txt
175.txt
133.txt
20.txt
53.txt
44.txt
122.txt
66.txt
114.txt
87.txt
31.txt
187.txt
79.txt
70.txt
97.txt
130.txt
7.txt
38.txt
106.txt
94.txt
90.txt

Sorts it just fine.

 

Ah @ioa747 beat me, I left to go have dinner and forgot to hit reply  Your way is safer than mine if there are files that already start with 0's, having the data in a separate/temporary dimension to sort by/with is a good idea, since you can leave the original names unmodified.

Edited March 9, 2023 by mistersquirrle

[AspirinJunkie]

Corresponding UDFs already exist for this purpose.
This is how it would be implemented with the ArrayPlus UDF:

#include <ArrayPlus.au3>

Global $aFilenames = FileReadToArray(@ScriptDir & '\filenames.txt')

_ArrayDisplay($aFilenames, 'original')

; sort natural:
_ArraySortFlexible($aFilenames, __ap_cb_comp_Natural)

_ArrayDisplay($aFilenames, 'sorted')

 

 

[pixelsearch]

@AspirinJunkie and other users who would like to help :

When you run the script below (which requires Aspirinjunkie's ArrayPlus.au3) what does the timer indicate in Scite Console in these 2 cases ?

10000  rows, sorted in ... seconds
100000 rows, sorted in ... seconds

Just run the script twice, switching between these 2 lines in the script to select the corresponding number of rows :

Local $aArray[10000]
; Local $aArray[100000]

Thanks 

#include <ArrayPlus.au3> ; AspirinJunkie

Local $aArray[10000]
; Local $aArray[100000]

For $i = 0 To Ubound($aArray) - 1
    $aArray[$i] = $i & ".txt"
Next

_ArrayShuffle($aArray)
_ArrayDisplay($aArray, 'original')

Local $hTimer = TimerInit()
_ArraySortFlexible($aArray, __ap_cb_comp_Natural) ; natural sort
ConsoleWrite(Ubound($aArray) & " rows, sorted in " & Round((TimerDiff($hTimer) / 1000), 3) & " seconds" & @crlf)

_ArrayDisplay($aArray, 'sorted')

 

[AspirinJunkie]

For the example script it gives:

10000 rows, sorted in 3.606 seconds
100000 rows, sorted in 41.307 seconds

Even if it is unlikely to have to sort 100,000 filenames, you can speed up the process by replacing the general natural comparison function (which also handles numbers in the middle of the string correctly) with a simpler one for this case:

#include <ArrayPlus.au3> 

Local $aArray[10000]
;~ Local $aArray[100000]

For $i = 0 To Ubound($aArray) - 1
    $aArray[$i] = $i & ".txt"
Next

_ArrayShuffle($aArray)
_ArrayDisplay($aArray, 'original')

Local $hTimer = TimerInit()
_ArraySortFlexible($aArray, _mycomp)
ConsoleWrite(Ubound($aArray) & " rows, sorted in " & Round((TimerDiff($hTimer) / 1000), 3) & " seconds" & @crlf)

_ArrayDisplay($aArray, 'sorted')


Func _mycomp($A, $B)
    $A = Int($A)
    $B = Int($B)
    Return $A > $B ? 1 : $A = $B ? 0 : -1
EndFunc

With this I get the following results:

10000 rows, sorted in 1.59 seconds
100000 rows, sorted in 19.582 seconds

If you need even more speed, then it's the same as always:
If you write code specifically for a particular use case, you can get the maximum speed for that case, but in return you lose flexibility and reusability of the code.

Edited March 9, 2023 by AspirinJunkie

[SOLVE-SMART]

I am always truly impressed how "easy" such a huge optimization (performance improvement) can be done by a different (sort) algorithm 🧐 👍 .
Well done!

Best regards
Sven

[pixelsearch]

@AspirinJunkie I asked this because, until January 26 2022, there was a guy on the forum named @markyrocks

On January 2, 2022, he started a topic named "Why is _ArraySort so broken?" where me (and you) did reply to him, among many users. But the way he expressed himself was sometimes a bit... vehement and got him into a few troubles, maybe that's why he left the Forum a few days after.

Nevertheless, the little C (or was it C++) code he indicated and the sort dll (for numerics) he provided were enough to make me start my "little C++ adventure" during a few weeks. It was difficult because I never tried C++ before, but after I checked how incredible the speed sort was (using his dll) then I thought "why not trying this myself and see where it goes ?"

After a lot of sweat with C++ during a few weeks, I shared my results in this link (C++ code to generate "SortAll.dll" , Include file "SortAll.au3", "Test2.au3") ... and that was the end of my C++ journey, I didn't touch C++ since march 2022 and certainly forgot everything about C++ now.

Now I use the dll when needed and I also integrated the call to the dll in my customized version of _ArrayDisplay, making _ArrayDisplay sort 10 times faster than the actual version, when you click on a column header. This is very useful on old machines (like mine !) facing plenty of rows.

So the actual topic made me curious again, that's why I asked you about the timers and thanks for your tests.

3 hours ago, AspirinJunkie said:

or the example script it gives:

10000 rows, sorted in 3.606 seconds
100000 rows, sorted in 41.307 seconds

If I'm not mistaken, your 41 seconds for 100.000 rows could be reduced to 1 second with the dll.
I'm not sure you'll be interested in it, but it was just to let you know (and to other users too) that it is possible to sort at the speed of sound  with an external dll, when the number of elements require it. The sort works on 1D/2D, numerical, string, natural.

So markyrocks, wherever you are now, thanks for the thread where you made me discover it was possible. It was my 1st experience with C++ and probably the last one too.

Here is the script test I used, but it won't work unless the dll is included. In case a user wants to try it, he should first compile the C++ code as indicated in the C++ source thread

#include <ArrayPlus.au3> ; AspirinJunkie
#include <SortAll.au3> ; requires pixelsearch's sortall.dll

Local $aArray[10000]
; Local $aArray[100000]

For $i = 0 To Ubound($aArray) - 1
    $aArray[$i] = $i & ".txt"
Next
_ArrayShuffle($aArray)

Local $aBackup1 = $aArray ; backup of shuffled array for sort #1
Local $aBackup2 = $aArray ; backup of shuffled array for sort #2
Local $aArray = 0 ; free memory

;=======================================
_ArrayDisplay($aBackup1, 'original #1')
Local $hTimer1 = TimerInit()
_ArraySortFlexible($aBackup1, __ap_cb_comp_Natural) ; natural sort
ConsoleWrite(Ubound($aBackup1) & " rows, sort #1 in " & Round((TimerDiff($hTimer1) / 1000), 3) & " seconds" & @crlf)
_ArrayDisplay($aBackup1, 'sort #1')

;=======================================
_ArrayDisplay($aBackup2, 'original #2')
Local $hTimer2 = TimerInit()
SortAll($aBackup2) ; default param. 2 to 5 : col 0, natural sort, ascending, unstable sort
ConsoleWrite(Ubound($aBackup2) & " rows, sort #2 in " & Round((TimerDiff($hTimer2) / 1000), 3) & " seconds" & @crlf)
_ArrayDisplay($aBackup2, 'sort #2')

Thanks for reading 

Edit: and before @jpm asks, yes I also sorted the opposite way, starting with #2, by moving 5 lines concerning sort #2 and placing them before 5 lines concerning sort #1 

Edited March 10, 2023 by pixelsearch

[AspirinJunkie]

6 hours ago, pixelsearch said:

but it was just to let you know (and to other users too) that it is possible to sort at the speed of sound  with an external dll

You could get even more out of it by not only outsourcing the sorting logic, but by doing without AutoIt completely and writing the whole program in C++.

Not surprisingly, a compiled programming language is vastly superior to an interpreter-based language.
And you could possibly get even more out of it, for example, if you let the data be sorted via CUDA or OpenCL on the gpu.
Probably you could also speed up the whole thing massively if you use powershell instead of autoit and would even be able to keep the code size smaller.

In the end, you always end up balancing effort and benefit, which results in the question of which tool you should use for a certain task.

By the way, I can't test your DLL, because I would have to compile it first and I don't want to set up a compiler for it.
But of course I assume that it massively outperforms my solution.
This is already done by the solution of ioa747. His solution is specialized for the case here and is therefore faster.
In contrast, the solution can hardly be used for other cases without major adjustments. It is therefore a matter of weighing up exactly what is needed.

 

[argumentum]

.. how about inline ASM ?. That would save us from calling and external file.
https://www.autoitscript.com/forum/topic/86672-autoit-inline-assembly-udf/
https://www.autoitscript.com/forum/topic/173919-inline-assembler-snippets/

not that I know anything about ... anything, but I thought that it would be good to look at for you actual programmers 

[mikell]

My 2 cents about the script in post #1 :
This

Global $aMatches = StringRegExp($sFileContents, "(?s)" & $sStartString & "(.*?)" & $sEndString, 4)
If IsArray($aMatches) Then
      ; String was found between the start and end strings
       Global $sMatch = $aMatches[0]
      ; MsgBox (0, "test", $sMatch)  <============= can't work !
EndIf

is actually incorrect. Using flag 4, $aMatches[0] is an array, not a string, so the syntax $sMatch = $aMatches[0] is wrong
Voilà ...
 

[jchd]

I beg to differ: $aMatches[0] is an array, so the assignment is not only correct but also necessary.

Displaying the full match in a box should then be:

MsgBox (0, "test", $sMatch[0])

 

[pixelsearch]

46 minutes ago, jchd said:

so the assignment is not only correct but also necessary.

Array in array... can't we just display the content of the 1st element of the inner array at row 0 like this, without assignment ?

MsgBox ( 0, "test", ($aMatches[0])[0] )

Just asking 

[jchd]

This notation is going to bite noobs: you can read from but not write to! IMHO best avoided in the wild West.

[pixelsearch]

8 minutes ago, jchd said:

you can read from but not write to

Sort of "you can check out anytime you like, but you can never leave" 
Oops, that's already copyrighted 

[argumentum]

On 3/8/2023 at 6:18 PM, Cyborg5000 said:

Any help appreciated

so, what did you end up doing ?

[mikell]

8 hours ago, jchd said:

I beg to differ

So do I. My remark was about syntax , not about assignment
Of course assignments must be done but OP uses hungarian notation , so this

MsgBox (0, "test", $sMatch[0])

looks somewhat strange, while this

$aMatch_0 = $aMatches[0]
$sMatch = $aMatch_0[0]
MsgBox (0, "test", $sMatch)

doesn't hurt the eyes
 

[jchd]

Yes I noticed that. It was a bit late at the time I posted and didn't point that point out.