Sorting By "Custom Rules"

BatMan22 · December 11, 2017

YYYYAAAAAAAYYYY! Thanks a million dude! I'm going to figure out how to make this into a header and put it into all my programs for when I need to sort files! I already got it working as a function (not that it requires any work to do that) You rock!

; Start -- Required to use SortWithSQL Function!
#include <sqlite.au3>
#include <array.au3>
; start sqlite with a temp DB
_SQLite_Startup()
_SQLite_Open()
OnAutoItExitRegister('_fini')
Global $Filename = @ScriptDir & '\samplefile.txt'
$sorted = SortwithSQL($Filename)
_ArrayDisplay($sorted)
; End -- of what is Required to use SortWithSQL Function!

Func SortwithSQL($Filename)

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE if not exists [t1] (c1, c2);')
If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Temp Table Failed'))

; load temp table t1

Local $aFile = StringSplit(FileRead($Filename), @CRLF, 3), $sql = 'insert into t1 values '

For $1 = 0 To UBound($aFile) - 1
    If $aFile[$1] = '' Then ContinueLoop
    $sql &= '(' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '(.*?) .*', '$1')) & ', ' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '.*? (.*)', '$1')) & '),' & @CRLF
Next

$sql = StringTrimRight($sql, 3) & ';'

_SQLite_Exec(-1, $sql)
If @error Then Exit MsgBox(0, '', _SQLite_ErrMsg())

; get rid of dup entries...stor result in table t2

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE t2 AS SELECT distinct * FROM t1; drop table t1')
If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Final Table Failed'))

; retrieve entries ordered by column 1 desc within column 2 asc

Local $ret, $arows, $irows, $icols
_SQLite_GetTable2d(-1, 'select * from t2 order by c2, c1 desc;', $arows, $irows, $icols)


; flip LCSD with LCS rows

Local $tleft, $tright
For $1 = 0 To UBound($arows) - 1
    If StringLeft($arows[$1][0], 4) = 'LCSD' Then
        $tleft = $arows[$1][0]
        $tright = $arows[$1][1]
        $arows[$1][0] = $arows[$1 + 1][0]
        $arows[$1][1] = $arows[$1 + 1][1]
        $arows[$1 + 1][0] = $tleft
        $arows[$1 + 1][1] = $tright
        $1 += 1
    EndIf
Next

Return $arows

EndFunc   ;==>SortwithSQL
Func _fini()
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_fini

BatMan22 · December 11, 2017

Ummm, you're going to hate me, but last step I promise. How do I get the sample ID's aka the 17X's to be in numberical order? Like normal Ascending, ie..

MB

LCS

LCSD

1

2

3

4

I'm sorry dude >.< not trying to be a pain..

Malkey · December 11, 2017

I had trouble trying to sort your unsorted example of post #1 that has seven lines with "218.6". Your sorted example of post #1 has nine lines with "218.6" - an impossible sorting task.

So to get even, :shifty: here is a working example the relies heavily in regular expressions to re-arranges and modify the original data which is put into a new first column that is suitable for sorting the array in the required order.
The second column of the array has the original data and simply gets carried along with the sorting being carried out in the first column.
When sorted, the first sorting column of the 2D array can be deleted, leaving a 1D array of the sorted data.

#include <Array.au3>
; https://www.autoitscript.com/forum/topic/191487-sorting-by-custom-rules/

Local $sRESortingOrder = "^MB\-, ^LCS\-, ^LCSD\-, ^([^\-]{7})\-([^\h]{4}), ^(.{7})\-(.{4})MS, ^(.{7})\-(.{4})MSD" ;, and the rest of the xxxxxxx-xxxx's"
Local $sString = StringStripWS(FileRead('samplefile.txt'), 3) ;  $STR_STRIPLEADING (1) = strip leading white space + $STR_STRIPTRAILING (2) = strip trailing white space
Local $sToSort = $sString
Local $aString = StringSplit($sString, @CRLF, 3) ; String to 1D array
Local $sSortOrder = StringSplit($sRESortingOrder, ", ", 3) ; String to 1D array

; ----- Rearrange string for sorting (to be added to first column of 2D array later) --------
For $i = 0 To UBound($sSortOrder) - 1
    If $i <= 2 Then _
            $sToSort = StringRegExpReplace($sToSort, "(?im)(" & $sSortOrder[$i] & "\S+\h+)([\.0-9]+)([^0-9\.].+)*$", "${2}" & StringRight("0000000000000" & $i, 13))
    ;       e.g. "MB-149969 218.6_DW" becomes "218.60000000000000" ; Trailing "00" is from matching first element, or, index "[0]" of $sSortOrder array.

    If $i > 2 Then _
            $sToSort = StringRegExpReplace($sToSort, "(?im)" & $sSortOrder[$i] & "\h(.+?)([^0-9\.].+)*$", "${3}${1}${2}" & StringRight("00" & $i, 2))
    ;       e.g. "1712330-002A 218.6_W" becomes "218.61712330002A03" Note: Trailing "03" is from matching 4th element (zero-based), or, index "[3]" of $sSortOrder array.

Next
Local $aSToSort = StringSplit($sToSort, @CRLF, 3) ; String to 1D array

; --- Create 2D array with column 0 as sorting column, and, column 1 (second column) as original data to be sorted. ---
Local $aSort2D[UBound($aSToSort)][2]
For $i = 0 To UBound($aSToSort) - 1
    $aSort2D[$i][1] = $aString[$i]
    $aSort2D[$i][0] = $aSToSort[$i]
Next
_ArraySort($aSort2D, 0, 0, 0, 0) ; Sort on column 0
_ArrayDisplay($aSort2D, "Sorted")

;--- Delete Column 0 -----
Local $aRes[UBound($aSort2D)]
For $i = 0 To UBound($aRes) - 1
    $aRes[$i] = $aSort2D[$i][1]
Next

_ArrayDisplay($aRes, "Sorted Results")

BatMan22 · December 11, 2017

8 hours ago, Malkey said:

I had trouble trying to sort your unsorted example of post #1 that has seven lines with "218.6". Your sorted example of post #1 has nine lines with "218.6" - an impossible sorting task.

So to get even, :shifty: here is a working example the relies heavily in regular expressions to re-arranges and modify the original data which is put into a new first column that is suitable for sorting the array in the required order.
The second column of the array has the original data and simply gets carried along with the sorting being carried out in the first column.
When sorted, the first sorting column of the 2D array can be deleted, leaving a 1D array of the sorted data.

#include <Array.au3>
; https://www.autoitscript.com/forum/topic/191487-sorting-by-custom-rules/

Local $sRESortingOrder = "^MB\-, ^LCS\-, ^LCSD\-, ^([^\-]{7})\-([^\h]{4}), ^(.{7})\-(.{4})MS, ^(.{7})\-(.{4})MSD" ;, and the rest of the xxxxxxx-xxxx's"
Local $sString = StringStripWS(FileRead('samplefile.txt'), 3) ;  $STR_STRIPLEADING (1) = strip leading white space + $STR_STRIPTRAILING (2) = strip trailing white space
Local $sToSort = $sString
Local $aString = StringSplit($sString, @CRLF, 3) ; String to 1D array
Local $sSortOrder = StringSplit($sRESortingOrder, ", ", 3) ; String to 1D array

; ----- Rearrange string for sorting (to be added to first column of 2D array later) --------
For $i = 0 To UBound($sSortOrder) - 1
    If $i <= 2 Then _
            $sToSort = StringRegExpReplace($sToSort, "(?im)(" & $sSortOrder[$i] & "\S+\h+)([\.0-9]+)([^0-9\.].+)*$", "${2}" & StringRight("0000000000000" & $i, 13))
    ;       e.g. "MB-149969 218.6_DW" becomes "218.60000000000000" ; Trailing "00" is from matching first element, or, index "[0]" of $sSortOrder array.

    If $i > 2 Then _
            $sToSort = StringRegExpReplace($sToSort, "(?im)" & $sSortOrder[$i] & "\h(.+?)([^0-9\.].+)*$", "${3}${1}${2}" & StringRight("00" & $i, 2))
    ;       e.g. "1712330-002A 218.6_W" becomes "218.61712330002A03" Note: Trailing "03" is from matching 4th element (zero-based), or, index "[3]" of $sSortOrder array.

Next
Local $aSToSort = StringSplit($sToSort, @CRLF, 3) ; String to 1D array

; --- Create 2D array with column 0 as sorting column, and, column 1 (second column) as original data to be sorted. ---
Local $aSort2D[UBound($aSToSort)][2]
For $i = 0 To UBound($aSToSort) - 1
    $aSort2D[$i][1] = $aString[$i]
    $aSort2D[$i][0] = $aSToSort[$i]
Next
_ArraySort($aSort2D, 0, 0, 0, 0) ; Sort on column 0
_ArrayDisplay($aSort2D, "Sorted")

;--- Delete Column 0 -----
Local $aRes[UBound($aSort2D)]
For $i = 0 To UBound($aRes) - 1
    $aRes[$i] = $aSort2D[$i][1]
Next

_ArrayDisplay($aRes, "Sorted Results")

I really appreciate what you did but.. The final result have a couple of problems I think..

1) Data seems to be duplicated by the macro, or maybe I had duplicate data in my file?

2) The Test Code Sorting seems flawed slightly in the end.. Assuming nothing shifted.. In the middle of the 218.6_DW test codes.. suddenly the 218.6_W's appear in the middle (see picture). I'm guessing this is because you're sorting for the test code then sorting by ascending or something?

jdelaney · December 11, 2017

Here's my go at it...just adding the data into a string, in the proper order, that you then sort. It works with the given data you provided, but string formatting might be needed on the sub-parts as new number schemes are entered into the mix.

Local $a[1]
_FileReadToArray(@DesktopDir & "/testdata.txt",$a,0)
_ArrayColInsert($a,1)
For $i = 0 To UBound($a)-1
    Local $atemp = StringRegExp($a[$i][0],"(\D*)(.*)\s([\d|\.]+)",3)
    If StringLeft($atemp[0],3)="MB-" Then
        $j = 1
    ElseIf StringLeft($atemp[0],4)="LCS-" Then
        $j = 2
    ElseIf StringLeft($atemp[0],5)="LCSD-" Then
        $j = 3
    Else
        $j = 4
    EndIf
    $a[$i][1] = $atemp[2] & $j & $atemp[1]
Next
_ArraySort($a,0,0,0,1)
_ArrayColDelete($a,1)
_ArrayDisplay($a)

MB-149969 218.6_DW
LCS-149969 218.6_DW
LCSD-149969 218.6_DW
1712354-001A 218.6_DW
1712354-001AMS 218.6_DW
1712354-001AMSD 218.6_DW
1712372-001C 218.6_DW
MB-145333 218.7
LCS-145333 218.7
LCSD-145333 218.7
1712350-006A 218.7_W
1712350-007A 218.7_W
1712350-008A 218.7_W
1712350-009A 218.7_W
1712370-001A 7199_TTLC_LL_S

Edited December 11, 2017 by jdelaney

jdelaney · December 11, 2017

I went ahead and added in the string formatting to try to keep the numbering consistent.

#include <Array.au3>
#include <File.au3>
Local $a[1]
_FileReadToArray(@DesktopDir & "/testdata.txt",$a,0)
_ArrayColInsert($a,1)
For $i = 0 To UBound($a)-1
    Local $atemp = StringRegExp($a[$i][0],"(\D*)(.*)\s([\d|\.]+)",3)
    If StringLeft($atemp[0],3)="MB-" Then
        $j = 1
    ElseIf StringLeft($atemp[0],4)="LCS-" Then
        $j = 2
    ElseIf StringLeft($atemp[0],5)="LCSD-" Then
        $j = 3
    Else
        $j = 4
    EndIf
    $atemp[2]=StringFormat("%.2f",$atemp[2])
    $atemp[2]=StringFormat("[%010s]",$atemp[2])
    $a[$i][1] = $atemp[2] & $j & $atemp[1]
Next
_ArraySort($a,0,0,0,1)
_ArrayColDelete($a,1)
_ArrayDisplay($a)

Edited December 11, 2017 by jdelaney

BatMan22 · December 12, 2017

53 minutes ago, jdelaney said:

I went ahead and added in the string formatting to try to keep the numbering consistent.

#include <Array.au3>
#include <File.au3>
Local $a[1]
_FileReadToArray(@DesktopDir & "/testdata.txt",$a,0)
_ArrayColInsert($a,1)
For $i = 0 To UBound($a)-1
    Local $atemp = StringRegExp($a[$i][0],"(\D*)(.*)\s([\d|\.]+)",3)
    If StringLeft($atemp[0],3)="MB-" Then
        $j = 1
    ElseIf StringLeft($atemp[0],4)="LCS-" Then
        $j = 2
    ElseIf StringLeft($atemp[0],5)="LCSD-" Then
        $j = 3
    Else
        $j = 4
    EndIf
    $atemp[2]=StringFormat("%.2f",$atemp[2])
    $atemp[2]=StringFormat("[%010s]",$atemp[2])
    $a[$i][1] = $atemp[2] & $j & $atemp[1]
Next
_ArraySort($a,0,0,0,1)
_ArrayColDelete($a,1)
_ArrayDisplay($a)

Your code seems to work.. 95% of the way.. but the sample ID's still end up mixed up with the test codes.

jdelaney · December 12, 2017

Oh, I missed that one condition. I'll add that in once I have a few minutes. It would be more 'esleif' statements.

Edited December 12, 2017 by jdelaney

BatMan22 · December 12, 2017

40 minutes ago, jdelaney said:

Oh, I missed that one condition. I'll add that in once I have a few minutes. It would be more 'esleif' statements.

I hate not understanding your program.. from what I can tell.. you insert a blank column into the array holding the data. Assigning 1, 2, 3, or 4, based on what kinda sample it is (MB/LCS/LCSD/Other). And I don't know what the code below does. After that code, you sort by the J Value column. Then you delete that column. I assume that the part of the code that I don't understand is what is in charge of sorting by the test code?

$atemp[2]=StringFormat("%.2f",$atemp[2])
    $atemp[2]=StringFormat("[%010s]",$atemp[2])
    $a[$i][1] = $atemp[2] & $j & $atemp[1]

jdelaney · December 12, 2017

The 'empty' value is only populated if the first characters are NON numerical...so the MD-, etc...the else catches all else.

This ensures that the numbers are all generic, so a string comparison will work...to do that, they need to be the same length, and same decimal digit:

StringFormat("%.2f",$atemp[2]) adds 2 decimals places (and a decimal) if not present

$atemp[2]=StringFormat("[%010s]",$atemp[2]) makes the entire string 10 digits in length, and pads the start of the string with 0s until that is present. (after adding the decimal, and 2 decimal digits)

Edited December 12, 2017 by jdelaney

BatMan22 · December 12, 2017

3 minutes ago, jdelaney said:

The 'empty' value is only populated if the first characters are NON numerical...so the MD-, etc...the else catches all else.

This ensures that the numbers are all generic, so a string comparison will work...to do that, they need to be the same length, and same decimal digit:

StringFormat("%.2f",$atemp[2]) adds 2 decimals places (and a decimal) if not present

$atemp[2]=StringFormat("[%010s]",$atemp[2]) makes the entire string 10 digits in length, and pads the start of the string with 0s until that is present. (after adding the decimal, and 2 decimal digits)

Thanks buddy.. I'm going to try and learn the Exp search formate things. For my uses.. that's very important.

kylomas · December 12, 2017

Batman22,

Looks like we have some new players...Here's a new version enforcing your sort order for the subordinate search column...

#include <sqlite.au3>
#include <array.au3>

; start sqlite with a temp DB

_SQLite_Startup()
_SQLite_Open()
OnAutoItExitRegister('_fini')

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE if not exists [t1] (c1, c2);')
If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Temp Table Failed'))

; load temp table t1

Local $aFile = StringSplit(FileRead(@ScriptDir & '\samplefile.txt'), @CRLF, 3), $sql = 'insert into t1 values '

For $1 = 0 To UBound($aFile) - 1
    If $aFile[$1] = '' Then ContinueLoop

    ; prefix sort order control byte
    Switch True
        Case StringRegExp($aFile[$1], '^M.*')
            $aFile[$1] = '0' & $aFile[$1]
        Case StringRegExp($aFile[$1], '^LCSD.*')
            $aFile[$1] = '1' & $aFile[$1]
        Case StringRegExp($aFile[$1], '^LCS-.*')
            $aFile[$1] = '2' & $aFile[$1]
        Case Else
            $aFile[$1] = '3' & $aFile[$1]
    EndSwitch

    $sql &= '(' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '(.*?) .*', '$1')) & ', ' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '.*? (.*)', '$1')) & '),' & @CRLF
Next

$sql = StringTrimRight($sql, 3) & ';'

_SQLite_Exec(-1, $sql)
If @error Then Exit MsgBox(0, '', _SQLite_ErrMsg())

; get rid of dup entries...stor result in table t2

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE t2 AS SELECT distinct * FROM t1; drop table t1')
If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Final Table Failed'))

; retrieve entries ordered by column 1 desc within column 2 asc

Local $ret, $arows, $irows, $icols
_SQLite_GetTable2d(-1, 'select * from t2 order by c2, c1;', $arows, $irows, $icols)

;strip sort order control byte
For $1 = 1 To UBound($arows) - 1
    $arows[$1][0] = StringTrimLeft($arows[$1][0], 1)
Next

_ArrayDisplay($arows, 'Final')

Func _fini()
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_fini

kylomas

edit: changed if's to case construct...

Edited December 12, 2017 by kylomas
programming

BatMan22 · December 12, 2017

18 hours ago, kylomas said:

Batman22,

Looks like we have some new players...Here's a new version enforcing your sort order for the subordinate search column...

#include <sqlite.au3>
#include <array.au3>

; start sqlite with a temp DB

_SQLite_Startup()
_SQLite_Open()
OnAutoItExitRegister('_fini')

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE if not exists [t1] (c1, c2);')
If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Temp Table Failed'))

; load temp table t1

Local $aFile = StringSplit(FileRead(@ScriptDir & '\samplefile.txt'), @CRLF, 3), $sql = 'insert into t1 values '

For $1 = 0 To UBound($aFile) - 1
    If $aFile[$1] = '' Then ContinueLoop

    ; prefix sort order control byte
    Switch True
        Case StringRegExp($aFile[$1], '^M.*')
            $aFile[$1] = '0' & $aFile[$1]
        Case StringRegExp($aFile[$1], '^LCSD.*')
            $aFile[$1] = '1' & $aFile[$1]
        Case StringRegExp($aFile[$1], '^LCS-.*')
            $aFile[$1] = '2' & $aFile[$1]
        Case Else
            $aFile[$1] = '3' & $aFile[$1]
    EndSwitch

    $sql &= '(' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '(.*?) .*', '$1')) & ', ' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '.*? (.*)', '$1')) & '),' & @CRLF
Next

$sql = StringTrimRight($sql, 3) & ';'

_SQLite_Exec(-1, $sql)
If @error Then Exit MsgBox(0, '', _SQLite_ErrMsg())

; get rid of dup entries...stor result in table t2

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE t2 AS SELECT distinct * FROM t1; drop table t1')
If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Final Table Failed'))

; retrieve entries ordered by column 1 desc within column 2 asc

Local $ret, $arows, $irows, $icols
_SQLite_GetTable2d(-1, 'select * from t2 order by c2, c1;', $arows, $irows, $icols)

;strip sort order control byte
For $1 = 1 To UBound($arows) - 1
    $arows[$1][0] = StringTrimLeft($arows[$1][0], 1)
Next

_ArrayDisplay($arows, 'Final')

Func _fini()
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_fini

kylomas

edit: changed if's to case construct...

Your code works perfectly..!

Edited December 12, 2017 by BatMan22

BatMan22 · December 12, 2017

18 hours ago, kylomas said:

Batman22,

Looks like we have some new players...Here's a new version enforcing your sort order for the subordinate search column...

#include <sqlite.au3>
#include <array.au3>

; start sqlite with a temp DB

_SQLite_Startup()
_SQLite_Open()
OnAutoItExitRegister('_fini')

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE if not exists [t1] (c1, c2);')
If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Temp Table Failed'))

; load temp table t1

Local $aFile = StringSplit(FileRead(@ScriptDir & '\samplefile.txt'), @CRLF, 3), $sql = 'insert into t1 values '

For $1 = 0 To UBound($aFile) - 1
    If $aFile[$1] = '' Then ContinueLoop

    ; prefix sort order control byte
    Switch True
        Case StringRegExp($aFile[$1], '^M.*')
            $aFile[$1] = '0' & $aFile[$1]
        Case StringRegExp($aFile[$1], '^LCSD.*')
            $aFile[$1] = '1' & $aFile[$1]
        Case StringRegExp($aFile[$1], '^LCS-.*')
            $aFile[$1] = '2' & $aFile[$1]
        Case Else
            $aFile[$1] = '3' & $aFile[$1]
    EndSwitch

    $sql &= '(' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '(.*?) .*', '$1')) & ', ' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '.*? (.*)', '$1')) & '),' & @CRLF
Next

$sql = StringTrimRight($sql, 3) & ';'

_SQLite_Exec(-1, $sql)
If @error Then Exit MsgBox(0, '', _SQLite_ErrMsg())

; get rid of dup entries...stor result in table t2

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE t2 AS SELECT distinct * FROM t1; drop table t1')
If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Final Table Failed'))

; retrieve entries ordered by column 1 desc within column 2 asc

Local $ret, $arows, $irows, $icols
_SQLite_GetTable2d(-1, 'select * from t2 order by c2, c1;', $arows, $irows, $icols)

;strip sort order control byte
For $1 = 1 To UBound($arows) - 1
    $arows[$1][0] = StringTrimLeft($arows[$1][0], 1)
Next

_ArrayDisplay($arows, 'Final')

Func _fini()
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_fini

kylomas

edit: changed if's to case construct...

Check out what your file has turned into.. Sexiness!

kylomas · December 13, 2017

Batman22,

Glad it's workiing! (...pulling on a cold Miller Lite...)

BatMan22 · December 13, 2017

Lol, I owe you one buddy.

BatMan22 · December 14, 2017

Ignore that. I'm an idiot.

Edited December 14, 2017 by BatMan22

kylomas · December 14, 2017

Huh??,

BatMan22 · December 14, 2017

5 hours ago, kylomas said:

Huh??,

Lol, I made a mistake, thought the program wasn't sorting some things.. but it is. But.. I can ask you a super simple question... (I think). So you saw that I made your program a function that you call, along with a header at the top using:

#include "C:\Users\Ash\Documents\SampleDueSheetExports\Sorter.au3"

I've included the way I've implimented the function in the attached file.. My question is that when I call your function using the code below, it seems to use | as a delimiter, even when I try and just use a " " as a delimiter, how do I just write the array to a file in the same format as before?

Func SortEverything()

    Local $arrayforic1 = SortMe("\\IC3\Loaderfiles\IC1.txt") ; This is an example of the Sort function and it will RETURN the sorted file as an array!
    _ArrayDisplay($arrayforic1)
    _FileWriteFromArray("\\IC3\Loaderfiles\IC1.txt", $arrayforic1, Default, Default, " ")

    Local $arrayforic2 = SortMe("\\IC3\Loaderfiles\IC2.txt") ; This is an example of the Sort function and it will RETURN the sorted file as an array!
    _FileWriteFromArray("\\IC3\Loaderfiles\IC2.txt", $arrayforic2)

    Local $arrayforic3 = SortMe("\\IC3\Loaderfiles\IC3.txt") ; This is an example of the Sort function and it will RETURN the sorted file as an array!
    _FileWriteFromArray("\\IC3\Loaderfiles\IC3.txt", $arrayforic3)

    Local $arrayforic5 = SortMe("\\IC3\Loaderfiles\IC5.txt") ; This is an example of the Sort function and it will RETURN the sorted file as an array!
    _FileWriteFromArray("\\IC3\Loaderfiles\IC5.txt", $arrayforic5)


EndFunc   ;==>SortEverything

Sorter.au3

kylomas · December 14, 2017

Batman22,

This worked for me...I don't have any idea what is wrong on your end. Also, you should interrogate @error after call _FileWriteFromArray().

#include <array.au3>
#include <file.au3>
#include <sqlite.au3>

local $array = sortme(@ScriptDir & '\samplefile.txt')
_FileWriteFromArray(@ScriptDir & '\samplefile.txt', $array, Default, Default, " ")

shellexecute(@ScriptDir & '\samplefile.txt')

Func SortMe($filename)

    _SQLite_Startup()
    _SQLite_Open()
    OnAutoItExitRegister('_fini')


    Local $ret = _SQLite_Exec(-1, 'CREATE TABLE if not exists [t1] (c1, c2);')
    If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Temp Table Failed'))

    ; load temp table t1

    Local $aFile = StringSplit(FileRead($filename), @CRLF, 3), $sql = 'insert into t1 values '

    For $1 = 0 To UBound($aFile) - 1
        If $aFile[$1] = '' Then ContinueLoop

        ; prefix sort order control byte
        Switch True
            Case StringRegExp($aFile[$1], '^M.*')
                $aFile[$1] = '0' & $aFile[$1]
            Case StringRegExp($aFile[$1], '^LCSD.*')
                $aFile[$1] = '1' & $aFile[$1]
            Case StringRegExp($aFile[$1], '^LCS-.*')
                $aFile[$1] = '2' & $aFile[$1]
            Case Else
                $aFile[$1] = '3' & $aFile[$1]
        EndSwitch

        $sql &= '(' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '(.*?) .*', '$1')) & ', ' & _SQLite_FastEscape(StringRegExpReplace($aFile[$1], '.*? (.*)', '$1')) & '),' & @CRLF
    Next

    $sql = StringTrimRight($sql, 3) & ';'

    _SQLite_Exec(-1, $sql)
    If @error Then Exit MsgBox(0, '', _SQLite_ErrMsg())

    ; get rid of dup entries...stor result in table t2

    Local $ret = _SQLite_Exec(-1, 'CREATE TABLE t2 AS SELECT distinct * FROM t1; drop table t1')
    If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Final Table Failed'))

    ; retrieve entries ordered by column 1 desc within column 2 asc

    Local $ret, $arows, $irows, $icols
    _SQLite_GetTable2d(-1, 'select * from t2 order by c2, c1;', $arows, $irows, $icols)

    ;strip sort order control byte
    For $1 = 1 To UBound($arows) - 1
        $arows[$1][0] = StringTrimLeft($arows[$1][0], 1)
    Next


    Return $arows

EndFunc   ;==>SortMe

Func _fini()
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_fini

kylomas

Sorting By "Custom Rules"

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

kylomas

kylomas

kylomas

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members