Array Sort/Subsort

[is8591]

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

[Valuater]

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

8)

[randallc]

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")

[peethebee]

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

[Jos]

Can anybody tell me whats wrong with the provided _ArraySort() UDF ? ( see helpfile)

[peethebee]

Hi!

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

Donno why.

peethebee

Edited September 23, 2005 by peethebee

[Jos]

Hi!

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

Donno why.

peethebee

Post your issue .. You might get help !!

[randallc]

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 September 23, 2005 by randallc

[is8591]

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

[is8591]

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.

[Jos]

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 September 23, 2005 by JdeB

[is8591]

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#.

expandcollapse popup

#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 September 23, 2005 by is8591

[is8591]

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

expandcollapse popup

#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

[Jos]

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 September 23, 2005 by JdeB

[is8591]

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

[Valuater]

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

heres what i got

expandcollapse popup

#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)

[is8591]

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.

[randallc]

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 September 25, 2005 by randallc

[is8591]

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?

[randallc]

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