is8591 Posted September 22, 2005 Share Posted September 22, 2005 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 Link to comment Share on other sites More sharing options...
Valuater Posted September 23, 2005 Share Posted September 23, 2005 what are you reading/sorting can you give us an example 8) Link to comment Share on other sites More sharing options...
randallc Posted September 23, 2005 Share Posted September 23, 2005 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") ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
peethebee Posted September 23, 2005 Share Posted September 23, 2005 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 Link to comment Share on other sites More sharing options...
Developers Jos Posted September 23, 2005 Developers Share Posted September 23, 2005 Can anybody tell me whats wrong with the provided _ArraySort() UDF ? ( see helpfile) SciTE4AutoIt3 Full installer Download page  - Beta files    Read before posting   How to post scriptsource   Forum etiquette Forum Rules  Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
peethebee Posted September 23, 2005 Share Posted September 23, 2005 (edited) Hi! Unfortunately, I did not get it to work. Edit: In that time... Donno why. peethebee Edited September 23, 2005 by peethebee vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvGerman Forums: http://www.autoit.deGerman Help File: http://autoit.de/hilfe vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv Link to comment Share on other sites More sharing options...
Developers Jos Posted September 23, 2005 Developers Share Posted September 23, 2005 Hi!Unfortunately, I did not get it to work. Edit: In that time...Donno why.peethebee Post your issue .. You might get help !! SciTE4AutoIt3 Full installer Download page  - Beta files    Read before posting   How to post scriptsource   Forum etiquette Forum Rules  Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
randallc Posted September 23, 2005 Share Posted September 23, 2005 (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 September 23, 2005 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
is8591 Posted September 23, 2005 Author Share Posted September 23, 2005 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 Link to comment Share on other sites More sharing options...
is8591 Posted September 23, 2005 Author Share Posted September 23, 2005 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. Link to comment Share on other sites More sharing options...
Developers Jos Posted September 23, 2005 Developers Share Posted September 23, 2005 (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;==>DisplayArrayJust 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 SciTE4AutoIt3 Full installer Download page  - Beta files    Read before posting   How to post scriptsource   Forum etiquette Forum Rules  Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
is8591 Posted September 23, 2005 Author Share Posted September 23, 2005 (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#.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 Link to comment Share on other sites More sharing options...
is8591 Posted September 23, 2005 Author Share Posted September 23, 2005 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 Link to comment Share on other sites More sharing options...
Developers Jos Posted September 23, 2005 Developers Share Posted September 23, 2005 (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 September 23, 2005 by JdeB SciTE4AutoIt3 Full installer Download page  - Beta files    Read before posting   How to post scriptsource   Forum etiquette Forum Rules  Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
is8591 Posted September 23, 2005 Author Share Posted September 23, 2005 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 Link to comment Share on other sites More sharing options...
Valuater Posted September 23, 2005 Share Posted September 23, 2005 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) Link to comment Share on other sites More sharing options...
is8591 Posted September 24, 2005 Author Share Posted September 24, 2005 GLAD you got it.... i have been on on it all dayheres what i gotIT WORKS.... 8)remember..... i'm a hobbyist8)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. Link to comment Share on other sites More sharing options...
randallc Posted September 25, 2005 Share Posted September 25, 2005 (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.826Best, RandallHi,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.710Best, Randall Edited September 25, 2005 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
is8591 Posted September 25, 2005 Author Share Posted September 25, 2005 Hi,I got an error in the array script?Best, RandallHi,I got an error in the file script too!...[No, I checked with original file posted too...]Best, RandallThe 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? Link to comment Share on other sites More sharing options...
randallc Posted September 25, 2005 Share Posted September 25, 2005 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW 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