Sign in to follow this  
Followers 0
is8591

Array Sort/Subsort

25 posts in this topic

Need to sort 2 dimensional array on main index and sort subgroups on secondary index. Very similar to Excel sort. Anybody knows a function?

Thanks

Share this post


Link to post
Share on other sites



what are you reading/sorting can you give us an example

8)


NEWHeader1.png

Share this post


Link to post
Share on other sites

Hi,

You can use excelCom, if you want! - I imagine you are wanting a direct script though..

Best, Randall

_XLArrayRead, _ XLArrayWrite _AddGeneral 1.7.8

#include<ExcelCom.au3>

;$FilePath=@ScriptDir&"\book1.xls"

;$XLArray=_XLArrayRead($FilePath,1,"A1:D6")

;Make any 2D array you like and call it "$XLArray"

$XLArrayAddress=_XLArrayWrite($XLArray,$FilePath,2,"A1",1)

_XLsort($FilePath,"A1",1,"C1",2,$XLArrayAddress,2)

$XLArray=_XLArrayRead($FilePath,2,$XLArrayAddress)

_XLClose($FilePath,1)

MsgBox(0,"","Sorted")

Share this post


Link to post
Share on other sites

Hi!

I used a sort algorithm for a multidim. array in my statistics UDF.

Grab it from my fileman and snap the code.

Good luck and ask here if questions remain, peethebee


vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvGerman Forums: http://www.autoit.deGerman Help File: http://autoit.de/hilfe vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Hi!

Unfortunately, I did not get it to work. Edit: In that time...

Donno why.

peethebee

Edited by peethebee

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvGerman Forums: http://www.autoit.deGerman Help File: http://autoit.de/hilfe vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

Share this post


Link to post
Share on other sites

Hi!

Unfortunately, I did not get it to work. Edit: In that time...

Donno why.

peethebee

Post your issue .. You might get help !! ;)


Visit the SciTE4AutoIt3 Download page for the latest versions        Beta files                                                          Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

Hi,

i can get it to sort on dimension1 or dim2, but don't know how (and help does not explain it to me, if it is possible) to get it to sort ONLY the ones that are the same in one sort by the second dimension? each time; wouldn't it need a script be written?

Best,Randall

Edited by randallc

Share this post


Link to post
Share on other sites

Thanks guys. This is the best support forum.

I am reading a file that basically looks like that: See attached - extension should be csv. I am using csv files in order to utilize Excel whenever possible.

Format:

Date,Acct#,User#,Comments,Amount

Need to sort by Acct# Date

@Randall: I am using your ExcelCom all the way (BTW - loving it) and sort works, but some stations will not carry Excel so got to be in script and of course it will be slower. Actually I am checking if Excel present on system and only if not there will run script function. And our admin disabled run from server.

@peethebee: will check it out over the weekend - will let you know

@JdeB: _ArraySort will only do 1 index. I tried to run the function twice but it messed up the 1st sort. The file is the result of 2nd sort on Acct. 1st sort was on date and as you can see sorted by account but dates messed up.

It looks like I would have to sort on Acct first then separate each account in another array and sort small arrays and then put them together.

Err_NC.txt

Share this post


Link to post
Share on other sites

Just looked at the code of _ArraySort - looks like $Ibase could be any value not just 0 or 1.

If that's the case will just have to figure out start & end index for each subgroup.

Will try and post the result and code.

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Agree that _ArraySort doesn't have the capability to sort for on column 1 and within that column 2, but you could concatenate the into a 3rd column just for sorting....

Here is an example using _ArraySort on an Array with 2 dimensions:

#include <Array.au3>

Dim $avArray[10][2]
$avArray[0][0] = "JPM"
$avArray[1][0] = "Holger"
$avArray[2][0] = "Jon"
$avArray[3][0] = "Larry"
$avArray[4][0] = "Jeremy"
$avArray[5][0] = "Valik"
$avArray[6][0] = "Cyberslug"
$avArray[7][0] = "Nutster"
$avArray[8][0] = "Tylo"
$avArray[9][0] = "JdeB"
For $x = 0 To 9
$avArray[$x][1] = $x
Next

; Sort on first dim
ConsoleWrite("*** SOrt on first dimension" & @LF)
_ArraySort($avArray, 0, 0, 0, 2, 0)
DisplayArray($avArray)
; Sort on second dim to restore original order
ConsoleWrite("*** Sort on second dimension, resore original order" & @LF)
_ArraySort($avArray, 0, 0, 0, 2, 1)
DisplayArray($avArray)

Func DisplayArray($array)
For $x = 0 To 9
ConsoleWrite($avArray[$x][0] & " - " & $avArray[$x][1] & @LF)
Next
EndFunc;==>DisplayArray

Just looked at the code of _ArraySort - looks like $Ibase could be any value not just 0 or 1.

If that's the case will just have to figure out start & end index for each subgroup.

Will try and post the result and code.

$I_base is the "Row" on which ArraySort will start and $i_Ubound is the last row it will include in the sort.

Agree that _ArraySort doesn't have the capability to sort for on column 1 and within that column 2, but you could concatenate the into a 3rd column just for sorting....

Example to sort on 2 "Columns" by means of concatenation:

#include <Array.au3>

Dim $avArray[10][3]
$avArray[0][0] = "b"
$avArray[1][0] = "a"
$avArray[2][0] = "b"
$avArray[3][0] = "a"
$avArray[4][0] = "c"
$avArray[5][0] = "b"
$avArray[6][0] = "a"
$avArray[7][0] = "c"
$avArray[8][0] = "b"
$avArray[9][0] = "e"
For $x = 0 To 9
 $avArray[$x][1] = $x
 $avArray[$x][2] = $avArray[$x][0] & $avArray[$x][1]
Next

; Sort on first dim
ConsoleWrite("*** SOrt on first dimension" & @LF)
_ArraySort($avArray, 0, 0, 0, 2, 2)
DisplayArray($avArray)
; Sort on second dim to restore original order
ConsoleWrite("*** Sort on second dimension, resore original order" & @LF)
_ArraySort($avArray, 0, 0, 0, 2, 1)
DisplayArray($avArray)

Func DisplayArray($array)
 For $x = 0 To 9
  ConsoleWrite($avArray[$x][0] & " - " & $avArray[$x][1] & @LF)
 Next
EndFunc  ;==>DisplayArray
Edited by JdeB

Visit the SciTE4AutoIt3 Download page for the latest versions        Beta files                                                          Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

Agree that _ArraySort doesn't have the capability to sort for on column 1 and within that column 2, but you could concatenate the into a 3rd column just for sorting....

Good idea to concatenate - shall try it next.

I tried to sort on 1st index and then subgroup and sort on 2nd - not a good result. Here is the code (excuse the appearance - had to do it fast and not that good with AU yet). BTW - attaching new file resorted on User#.

#include <file.au3>
#include <Array.au3>

Opt("TrayIconDebug", 1)

$myPath = @WorkingDir & "\"
$file = "Err_NC.txt"        ; Array storage file
Dim $aArr[1][1]             ; Array


;-------------------------------------
; Display 2 dimensional array
Func lf_Array2dDisplay(ByRef $ar_Array, $s_Title="Array contents", $n_Index=1)
    Local $output = ""
    Local $r, $c
    If Not IsArray($ar_Array) Then  Return -1
    For $r = $n_Index to UBound($ar_Array,1) - 1
        $output = $output & @LF
        For $c = 0 to UBound($ar_Array,2) - 1
            $output = $output & $ar_Array[$r][$c] & " "
        Next
    Next
    MsgBox(4096, $s_Title, $output)
    Return
EndFunc;==>mf_Array2dDisplay
;--------------------------------------
Func lf_FileTo2dArray($sFilePath, ByRef $aArray)
    Local $hFile
    Local $str
    Local $r, $c
    Local $aLocal[1]
    Local $aStr
    _FileReadToArray($sFilePath, $aLocal)
    $aStr = StringSplit($aLocal[1], ",")
    
    ReDim $aArray[$aLocal[0]][$aStr[0]]
    For $r = 1 To $aLocal[0]
        $aStr = StringSplit($aLocal[$r], ",")
        For $c = 1 To UBound($aArray,2)
            $aArray[$r-1][$c-1] = $aStr[$c]
        Next
    Next
lf_Array2dDisplay($aArray, "Before Sort",0)
    Return
EndFunc;==> lf_FileTo2dArray
;*********************************
;main
;*********************************
;Initialize array
lf_FileTo2dArray($myPath & $file, $aArr)
;++ add selection for primary and secondary index - for now sort on Acct(index 1) and Date (index 0)
$indexP = 1
$indexS = 0
_ArraySort($aArr, 0, 0, 0, UBound($aArr,2), $indexP)
lf_Array2dDisplay($aArr, "After 1st Sort", 0)
;find all subgroups and sort subgroups only
$i_s = 0    ;start row
While 1
    For $r = $i_s To UBound($aArr) -1
        If $aArr[$i_s][$indexP] <> $aArr[$r][$indexP] Then ExitLoop
    Next
;       MsgBox(0,"start - end", $i_s & " - " & $r-1)
    If $i_s <> $r-1 Then _ArraySort($aArr, 0, $i_s, $r-1, UBound($aArr,2), $indexS)
    If $r >= UBound($aArr) -1 Then ExitLoop
    $i_s = $r
WEnd
lf_Array2dDisplay($aArr, "After 2nd Sort", 0)
Exit
Edited by is8591

Share this post


Link to post
Share on other sites

Thanks everybody - problem solved. My mistake - sorting dates requires to convert to day number otherwise it treats date as string.

Here is the solution if anyone interested using JdeB approach:

Read file into 2d array

Expand array by 1 extra column

Concatenate sort indexes in progression of primary to secondary (if any index is date convert to day number, I guess if time would have to convert to ticks) into added column

Sort array on concatenated column

Strip concatenated column

#include <file.au3>
#include <Array.au3>
#include <Date.au3>

Opt("TrayIconDebug", 1)

$myPath = @WorkingDir & "\"
$file = "Err_NC.txt"            ; Array storage file
Dim $aArr[1][1]                 ; Array


;-------------------------------------
; Display 2 dimensional array
Func lf_Array2dDisplay(ByRef $ar_Array, $s_Title="Array contents", $n_Index=1)
    Local $output = ""
    Local $r, $c
    If Not IsArray($ar_Array) Then  Return -1
    For $r = $n_Index to UBound($ar_Array,1) - 1
        $output = $output & @LF
        For $c = 0 to UBound($ar_Array,2) - 1
            $output = $output & $ar_Array[$r][$c] & " "
        Next
    Next
    MsgBox(4096, $s_Title, $output)
    Return
EndFunc ;==>mf_Array2dDisplay
;--------------------------------------
Func lf_FileTo2dArray($sFilePath, ByRef $aArray)
    Local $hFile
    Local $str
    Local $r, $c
    Local $aLocal[1]
    Local $aStr
    _FileReadToArray($sFilePath, $aLocal)
    $aStr = StringSplit($aLocal[1], ",")
    
    ReDim $aArray[$aLocal[0]][$aStr[0]]
    For $r = 1 To $aLocal[0]
        $aStr = StringSplit($aLocal[$r], ",")
        For $c = 1 To UBound($aArray,2)
            $aArray[$r-1][$c-1] = $aStr[$c]
        Next
    Next
lf_Array2dDisplay($aArray, "Before Sort",0)
    Return
EndFunc ;==> lf_FileTo2dArray
;*********************************
;main
;*********************************
;Initialize array
lf_FileTo2dArray($myPath & $file, $aArr)
;++ add selection for primary and secondary index - for now sort on Acct(index 1) and Date (index 0)
$indexP = 1
$indexS = 0
;concatenate Primary and Secondary index fields into extra column
ReDim $aArr[UBound($aArr)][UBound($aArr,2) +1]  ;room for extra column
For $r = 0 To UBound($aArr) -1
    $s = $aArr[$r][$indexS]                     ; convert date for comparison
    $s = _DateToDayValue(StringRight($s,4), StringLeft($s,StringInStr($s,"/") -1), _ 
                    StringMid($s, StringInStr($s,"/") +1, StringInStr($s,"/",0,2) - StringInStr($s, "/")-1))
    $aArr[$r][UBound($aArr,2)-1] = $aArr[$r][$indexP] & $s
Next
lf_Array2dDisplay($aArr, "Expended", 0)
_ArraySort($aArr, 0, 0, 0, UBound($aArr,2), UBound($aArr,2) -1)
ReDim $aArr[UBound($aArr)][UBound($aArr,2) -1]  ;kill added column
lf_Array2dDisplay($aArr, "Sorted", 0)
Exit

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

Good idea to concatenate - shall try it next.

I tried to sort on 1st index and then subgroup and sort on 2nd - not a good result. Here is the code (excuse the appearance - had to do it fast and not that good with AU yet). BTW - attaching new file resorted on User#.

Well just tried it and works correct... but maybe not what you want. It sorts alphanumeric but i guess you want it sorted on date ..... if thats the case you have to reformat the date column to YYYYMMDD.

EDIT: See you just figured it out ....

Edited by JdeB

Visit the SciTE4AutoIt3 Download page for the latest versions        Beta files                                                          Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

Well just tried it and works correct... but maybe not what you want. It sorts alphanumeric but i guess you want it sorted on date ..... if thats the case you have to reformat the date column to YYYYMMDD.

EDIT: See you just figured it out ....

Thanks - I did it just a minute before your message

Share this post


Link to post
Share on other sites

GLAD you got it.... i have been on on it all day

heres what i got

#include <file.au3>
#include <Array.au3>

Dim $aRecords, $bRecords, $cRecords
Dim $TaRecords[100][100], $TbRecords[100][100], $TcRecords[100][100], $T = 0, $T_Times = 0
Dim $Location = "C:\Temp\"
Dim $file1 = $Location & "sort-text.csv"
Dim $file2 = $Location & "sort-1.csv"
Dim $file3 = $Location & "sort-2.csv"

If Not FileExists($file1) then MsgBox(0,"Error", " the file does not exist")

If Not _FileReadToArray($file1,$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf


    _ArraySort( $aRecords,0,1)
    
    FileOpen($file2, 2)
    For $x = 1 to $aRecords[0] 
    FileWriteLine($file2, $aRecords[$x])
    Next
    FileClose($file2)
;_ArrayDisplay( $aRecords, "First Sort " )
While $T_Times < $aRecords[0] -1
    
    
        $b1file = FileOpen($file2, 0)
        $b2file = FileOpen($file3, 2)
        
        For $b = 1 to $aRecords[0]  
            If @error Then ExitLoop
            If $aRecords[$b] = "" Then ContinueLoop
            
            $bRead = FileReadLine($file2, $b)
            $bSplit = StringSplit($bRead, ",")
            
            $TbRecords [$b][0] = $bSplit[0]
            $TbRecords [$b][1] = $bSplit[2]
            $TbRecords [$b][2] = $bSplit[1]
            $TbRecords [$b][3] = $bSplit[3]
            $TbRecords [$b][4] = $bSplit[4]
            $TbRecords [$b][5] = $bSplit[5]
            
            FileWriteLine($b2file, $TbRecords [$b][1] & "," & $TbRecords [$b][2] & "," & $TbRecords [$b][3] & "," & $TbRecords [$b][4] & "," & $TbRecords [$b][5])
            
        Next
        
        FileClose($b1file)
        FileClose($b2file)
        
        $c2file = FileOpen($file2, 2)
        
        _FileReadToArray($file3,$cRecords)
        _ArrayDelete($cRecords, ($cRecords[0]))
        _ArraySort( $cRecords,0,1)
        
        For $x = 1 to $cRecords[0] -1 
            FileWriteLine($file2, $cRecords[$x])
        Next
        
        FileClose($c2file)
        
        $T_Times = $T_Times +1
WEnd

    Run("notepad.exe " & $file2)

IT WORKS.... 8)

remember..... i'm a hobbyist

8)


NEWHeader1.png

Share this post


Link to post
Share on other sites

GLAD you got it.... i have been on on it all day

heres what i got

IT WORKS.... 8)

remember..... i'm a hobbyist

8)

Thanks a lot for an effort - your code works.

I am not a pro either - just scripting a bit.

Did you try my code? - works too.

I usually prefer to use arrays instead of files - random access is faster. Check out my FileTo2dArray function.

It's pretty quick and can be any 2d array.

Also I use variables in most places so can be more general approach. All I have to do now is to add a bit of error checking, parameter definition, turn off all Array display and it's a function.

Probably will make it into a function in a week or two.

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

Hi,

I got an error in the array script?

>Running: (3.1.1.75):C:\Program Files\AutoIt3\beta\autoit3.exe "C:\Program Files\Au3PROGS\SciTe\ArraySort2dTimed1.au3"

C:\Program Files\Au3PROGS\SciTe\ArraySort2dTimed1.au3 (73) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

$aArray[$r-1][$c-1] = $aStr[$c]

$aArray[$r-1][$c-1] = ^ ERROR

>AutoIT3.exe ended.

>Exit code: 0 Time: 3.826

Best, Randall

Hi,

I got an error in the file script too!...[No, I checked with original file posted too...]

>Running: (3.1.1.75):C:\Program Files\AutoIt3\beta\autoit3.exe "C:\Program Files\Au3PROGS\SciTe\ArraySort2dTimed2List.au3"

C:\Program Files\Au3PROGS\SciTe\ArraySort2dTimed2List.au3 (51) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

$TbRecords [$b][1] = $bSplit[2]

$TbRecords [$b][1] = ^ ERROR

>AutoIT3.exe ended.

>Exit code: 0 Time: 5.710

Best, Randall Edited by randallc

Share this post


Link to post
Share on other sites

Hi,

I got an error in the array script?

Best, Randall

Hi,

I got an error in the file script too!...[No, I checked with original file posted too...]

Best, Randall

The error is caused by CRLF on last line of my file. When doing FileReadTo Array it gets last element empty.

So when sorting 1st time this becomes [1] element and when this being split as a string into $bSplit array you get only 1 element. So $bSplit{2] is out of range.

Fix - open file in WordPad, go to last line and hit "BackSpace" - save file.

It will work.

My fault - I pulled my code from bigger code and forgot to include function to strip blank lines.

Randall - did you try my code?

Share this post


Link to post
Share on other sites

Hi,

I haven't checked it fully for sorting, but looks good for speed till memory runs out. Nearly as fast as excel up to 100Kb, but takes forever for 900Kb (gave up waiting) - I guess you have limited file sizes?

Best, Randall

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  
Followers 0