Sorting By "Custom Rules"

BatMan22 · December 9, 2017

So I have a text file right now and it's formatted like you can see below.. In theory I would like everything in the file Sorted by the second part of the Line (the test code), then in the order of MB, LCS, LCSD, xxxxxxx-xxxx, xxxxxxx-xxxxMS, xxxxxxx-xxxxMSD, and the rest of the xxxxxxx-xxxx's for that test code. I've included an example below of what I would like the final product to look like. I'm not trying to be lazy but I honestly don't know how to sort it out. I would appreciate help/hints/whatever.

Thanks all

Example:

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

And I would like to create a function that sorts the file like..

MB-149969 218.6_DW
LCS-149969 218.6_DW
LCSD-149969 218.6_DW
1712350-001A 218.6_DW
1712351-001A 218.6_DW
1712654-001A 218.6_DW
1712654-001AMS 218.6_DW
1712654-001AMSD 218.6_DW
1712872-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

Also I guess I should post what I have till now..

#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>


$file = @ScriptDir & "\IC2.txt"

Local $aInput
Global $LineID

_FileReadToArray($file, $aInput)
For $i = 1 to UBound($aInput) -1
    
    $LineID = _ArrayToString($aInput, @CRLF, $i, $i)
    MsgBox(0,0,$LineID)
    Local $split = StringSplit($LineID, " ", 2)
    MsgBox(0,0, $split[0])
    MsgBox(0,0, $split[1])

Next

Edited December 9, 2017 by BatMan22
Add what I've Done Till now >.<

careca · December 9, 2017

There's _ArraySort, but because you want to sort it 3 ways.. im not sure i can help.

kylomas · December 10, 2017

Batman22,

How large are the data files?

I'n not sure that you can use sort for this but you can parse the file given a set of rules.

Kylomas

Edit: can you post a representative file?

Edited December 10, 2017 by kylomas

kylomas · December 10, 2017

batman22 (..."hey, I'm Batman"...),

This works given the sample you posted and following these rules:

1 - each group consists of a MB-, LCS-, LCSD-, and various detail entries starting with a numeral.

2 - MB-, LCS- and LCSD- records are joined by the number following the "-"

3 - detail entries are joined by the characters following the space

#include <Array.au3>

Local $sFile = FileRead(@ScriptDir & '\parse log.txt'), $outstr = ''

; get all "MB-" type records
Local $aMBRecs = StringRegExp($sFile, 'MB-.*', 3)
If Not IsArray($aMBRecs) Then Exit MsgBox(17, 'Error', 'No MB- records found')

; iterate thru "MB-" recs getting all matching "LCS" and "LCSD" recs...start building output group
For $1 = 0 To UBound($aMBRecs) - 1

    $aLCRecs = StringRegExp($sFile, '.*' & StringRegExpReplace($aMBRecs[$1], 'MB-(\d+) .*', '$1') & '.*', 3)
    If Not IsArray($aLCRecs) Then Exit MsgBox(17, 'Error', 'No LCx Recs for MB code = ' & StringRegExpReplace($aMBRecs[$1], 'MB-(\d+) .*', '$1'))

    _ArraySort($aLCRecs, 1) ;   just to enforce an order

    $outstr &= $aLCRecs[0] & @CRLF
    $outstr &= $aLCRecs[2] & @CRLF
    $outstr &= $aLCRecs[1] & @CRLF

    ; now get detail entries for each header group
    $aDet = StringRegExp($sFile, '\d.*' & StringRegExpReplace($aMBRecs[$1], '.* (.*)', '$1') & '.*', 3)

    ; ...and complete output group
    For $2 = 0 To UBound($aDet) - 1
        $outstr &= $aDet[$2] & @CRLF
    Next

Next

FileWrite(@ScriptDir & '\parsed log.txt', $outstr)
ShellExecute(@ScriptDir & '\parsed log.txt')

kylomas

BatMan22 · December 10, 2017

6 hours ago, kylomas said:

Batman22,

How large are the data files?

I'n not sure that you can use sort for this but you can parse the file given a set of rules.

Kylomas

Edit: can you post a representative file?

Sure, I'll post one here, see attached. I'm going through your code to understand it, I need to loopup these functions but on my test file.. it didn't work.. and it seems like it duplicated a lot of data. Maybe I loaded the sample file wrong..

samplefile.txt

Edited December 10, 2017 by BatMan22

BatMan22 · December 10, 2017

I think by combining my script and your script.. this should work.. I think I'm going to try and execute something like... I'm kinda writing it out here to help myself plan too.

1) Search for All MB-*'s --> Copy every line starting with MB* to File A and delete those lines from the main file

2) Search for All LCS-*'s --> Copy every line starting with LCS* to File B and delete those lines from the main file

3) Search for All LCSD-*'s --> Copy every line starting with LCSD* to File C and delete those lines from the main file

4) Everything left in main file are non MB/LCS/LCSD lines sooo.. Copy Them all to File D and delete those lines from main, leaving the main file empty

5) Copy file A to Mainfile THEN Copy file B to Mainfile then Copy C to mainfile then Copy D to mainfile...

If all that works correctly.. then I split the two parts of every line with the StringSplit($LineID, " ", 2) command into a 2D Array, Sort by the SECOND column.. And should be done..?

BatMan22 · December 10, 2017

Hah, I think I'm almost done, using your 2D Code from the forums!

Just gotta play a little bit more

#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>


Global $sFile = FileRead(@ScriptDir & '\samplefile.txt'), $outstr = ''

; get all "MB-" type records
Local $aMBRecs = StringRegExp($sFile, 'MB-.*', 3)
If Not IsArray($aMBRecs) Then MsgBox(17, 'Error', 'No MB- records found')
$aMBRecs = _ArrayUnique($aMBRecs, 0, 0, 0, 0, 0)

Local $aLCSRecs = StringRegExp($sFile, 'LCS-.*', 3)
If Not IsArray($aLCSRecs) Then MsgBox(17, 'Error', 'No LCS- records found')
$aLCSRecs = _ArrayUnique($aLCSRecs, 0, 0, 0, 0, 0)

Local $aLCSDRecs = StringRegExp($sFile, 'LCSD-.*', 3)
If Not IsArray($aLCSDRecs) Then MsgBox(17, 'Error', 'No LCSD- records found')
$aLCSDRecs = _ArrayUnique($aLCSDRecs, 0, 0, 0, 0, 0)



local $str = fileread(@scriptdir & '\samplefile.txt')

_arraydisplay(_DBG_StringSplit2D($str, " "),'String Converted to 2D Array')
Local $SortMe = _DBG_StringSplit2D($str, " ")
_ArraySort($SortMe, 0, 0, 0, 1)
_ArrayDisplay($SortMe, "2D'd and Sorted!")


func _DBG_StringSplit2d(byref $str,$delimiter)

    ; #FUNCTION# ======================================================================================
    ; Name ................:    _DBG_StringSplit2D($str,$delimiter)
    ; Description .........:    Create 2d array from delimited string
    ; Syntax ..............:    _DBG_StringSplit2D($str, $delimiter)
    ; Parameters ..........:    $str        - EOL (@CR, @LF or @CRLF) delimited string to split
    ;                           $delimiter  - Delimter for columns
    ; Return values .......:    2D array
    ; Author ..............:    kylomas
    ; =================================================================================================

    local $a1 = stringregexp($str,'.*?(?:\R|$)',3), $a2

    local $rows = ubound($a1) - 1, $cols = 0

    ; determine max number of columns by splitting each row and keeping highest ubound value

    for $i = 0 to ubound($a1) - 1
        $a2 = stringsplit($a1[$i],$delimiter,1)
        if ubound($a2) > $cols then $cols = ubound($a2)
    next

    ; define and populate array

    local $aRET[$rows][$cols-1]

    for $i = 0 to $rows - 1
        $a2 = stringsplit($a1[$i],$delimiter,3)
        for $j = 0 to ubound($a2) - 1
            $aRET[$i][$j] = $a2[$j]
        Next
    next

    return $aRET

endfunc

Edited December 10, 2017 by BatMan22

kylomas · December 10, 2017

Batman22,

The file did not work because it did not follow the same form as the original sample data. However, I'm glad it got you headed toward a solution.

Good Luck,

kylomas

edit: You are getting duplicate data when you run it multiple times. I did not delete the output file before writing to it. When I run it against the new data that you posted I get expected results, per the rules above. One assumption is that 17xxx type records must have a corresponding set of MB- and LCx- records.

Edited December 10, 2017 by kylomas

kylomas · December 10, 2017

Batman22,

This looks like all you need...

#include <Array.au3>

Local $str = FileRead(@ScriptDir & '\samplefile.txt')

;_arraydisplay(_DBG_StringSplit2D($str, " "),'String Converted to 2D Array')
Local $SortMe = _DBG_StringSplit2d($str, " ")
_ArraySort($SortMe, 0, 0, 0, 1)
;_ArrayDisplay($SortMe, "2D'd and Sorted!")

Local $hfl = FileOpen(@ScriptDir & '\parsed log.txt', 2), $outstr = ''

For $1 = 0 To UBound($SortMe) - 1
    For $2 = 0 To UBound($SortMe, 2) - 1
        $outstr &= $SortMe[$1][$2] & ' '
    Next
    $outstr &= @CRLF
Next

FileWrite($hfl, $outstr)
FileClose($hfl)
ShellExecute(@ScriptDir & '\parsed log.txt')


Func _DBG_StringSplit2d(ByRef $str, $delimiter)

    ; #FUNCTION# ======================================================================================
    ; Name ................:    _DBG_StringSplit2D($str,$delimiter)
    ; Description .........:    Create 2d array from delimited string
    ; Syntax ..............:    _DBG_StringSplit2D($str, $delimiter)
    ; Parameters ..........:    $str        - EOL (@CR, @LF or @CRLF) delimited string to split
    ;                           $delimiter  - Delimter for columns
    ; Return values .......:    2D array
    ; Author ..............:    kylomas
    ; =================================================================================================

    Local $a1 = StringSplit($str, @CRLF, 3), $a2
    ;local $a1 = stringregexp($str,'.*?(?:\R|$)',3), $a2

    Local $rows = UBound($a1) - 1, $cols = 0

    ; determine max number of columns by splitting each row and keeping highest ubound value

    For $i = 0 To UBound($a1) - 1
        $a2 = StringSplit($a1[$i], $delimiter, 1)
        If UBound($a2) > $cols Then $cols = UBound($a2)
    Next

    ; define and populate array

    Local $aRET[$rows][$cols - 1]

    For $i = 0 To $rows - 1
        $a2 = StringSplit($a1[$i], $delimiter, 3)
        For $j = 0 To UBound($a2) - 1
            $aRET[$i][$j] = $a2[$j]
        Next
    Next

    Return $aRET

EndFunc   ;==>_DBG_StringSplit2d

kylomas

BatMan22 · December 10, 2017

I'm confused.. That does sort by the second half aka column 1 and writes it to a file, which is great and exactly what I need.. But it doesn't sort by the first part then after. When you say it's all I need I think you're saying excluding that part?

So After your code runs.. I then need to sort out the rest.. which I think I can do, kinda.. with the code below.. I can pull out all the lines that start with MB and write them to a file.. but how do I delete those lines from the main file after I do that?

Local $aMBRecs = StringRegExp($sFile, 'MB-.*', 3)
If Not IsArray($aMBRecs) Then MsgBox(17, 'Error', 'No MB- records found')
$aMBRecs = _ArrayUnique($aMBRecs, 0, 0, 0, 0, 0)

Aka..

1) Sort using your code

2) Find MB's using the above code (also your code, lol).

3) Write those MB's to a new file so they are at the top of the file

4) Delete those MB's from the starting file...

Edited December 10, 2017 by BatMan22

BatMan22 · December 11, 2017

What I came up with doesn't seem to work..

Global $sFile = FileRead(@ScriptDir & '\parsed log.txt'), $outstr = ''

Local $aMBRecs = StringRegExp($sFile, 'MB-.*', 3)
$sFind= "MB-.*"
$sReplace=""
$replaceme= @ScriptDir & '\parsed log.txt'
_ReplaceStringInFile($replaceme, $sFind, $sReplace)
ShellExecute(@ScriptDir & '\parsed log.txt')

Edited December 11, 2017 by BatMan22

kylomas · December 11, 2017

Batman22,

Let's back up a bit.

1 - Are the MB- and LCx- entries related? If so, how to sort them?

2 - Are the 17x recs related to either the MB- or LCx- recs?. If so, how to sort them?

3 - In post #7 you are getting unique entries for MB and LCx recs. I don't see any dups in the sample files.

4 - If you sort and spin each of the arrays created in post #7 to a file in the correct order will that be what you need?

5 - Are you trying to sort column 1 within column 2?

kylomas

Edited December 11, 2017 by kylomas

BatMan22 · December 11, 2017

I think it's easier if you understand what I'm using this for. I'm using this for work, and I'm a chemist, and I work with Machines that analyze my samples and I'm using this to load my samples onto my machines so I don't have to type them in.

Now for every Batch (20 samples max, usually 2-5 like you see below) quality control is required to make sure what we are doing is correct and that the machines are working, this consists of an MB (machine blank), LCS (lab control sample) , LCSD (lab control sample duplicate, to check precision) are required. So the files that I gave you are formatted like..

MB-XXXXX YYYYY where XXXX are the batch ID's ( we can basically ignore this) , and where YYYYY (EPA 218.6, EPA 300.1) indicate the type of testing.. same thing applies to LCS/LCSD.

The rest of the lines that don't start with MB or LCS or LCSD are normal samples (for the most part). So to make my life easier.. I want to sort by Test and then just list the MB/LCS/LCSD before listing the samples after in numercial order.

So to answer your questions..

1) Yes, every batch has an MB/LCS/LCSD, but they might not exist in this file.

2) Yes, each one of the 17X's ARE assigned to an MB/LCS/LCSD. But that doesn't matter here, as long as we sort by test code.. then the 17's should be close to the correct MB/LCS/LCSD for each test code (if they exist).

3) The REST of my program, not seen here, sometimes generates duplicates, just a preventative measure.

4) I think so.. So if we put all the MB's as the lines in the file.. then list the LCS's then the LCSD's then the 17X's.. then when I run that through your 2D array sorting function.. It should be perfect.

In a perfect world I want it sorted like this.. We have done half of the work. We have sorted it by the test code already (218.6_DW, 218.7_W). I just need to figure out how to put the MB, LCS,LCSD and the 17's in numberical order while preserving the test code order.

MB-149969 218.6_DW
LCS-149969 218.6_DW
LCSD-149969 218.6_DW
1712350-001A 218.6_DW
1712351-001A 218.6_DW
1712654-001A 218.6_DW
1712654-001AMS 218.6_DW
1712654-001AMSD 218.6_DW
1712872-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
MB-145333 7199_TTLC_LL_S
LCS-145333 7199_TTLC_LL_S
LCSD-145333 7199_TTLC_LL_S
1712370-001A 7199_TTLC_LL_S

Edited December 11, 2017 by BatMan22

kylomas · December 11, 2017

Batman22,

The easiest way that I know of to sort columns within columns is thru a DB engine like this...

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

_ArrayDisplay($arows)

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

This uses an SQLite dll. If you don't have it you can download it from here. Stick it in any of the WIN standard load libs or specify the path to the dll in the _SQLite_startup function. Make sure you get the right version (32 or 64 bit).

kylomas

Edited December 11, 2017 by kylomas

BatMan22 · December 11, 2017

Hmm.. I'll try that. I've never used SQL, for anything before.

kylomas · December 11, 2017

I don't see any duplicate entries in the samples that you posted...

BatMan22 · December 11, 2017

There are none, I just meant that the rest of the program (not listed here) SOMETIMES generates duplicates.. so I wanted it as a safety measure.

BatMan22 · December 11, 2017

48 minutes ago, kylomas said:

Batman22,

The easiest way that I know of to sort columns within columns is thru a DB engine like this...

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

_ArrayDisplay($arows)

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

This uses an SQLite dll. If you don't have it you can download it from here. Stick it in any of the WIN standard load libs or specify the path to the dll in the _SQLite_startup function. Make sure you get the right version (32 or 64 bit).

kylomas

I don't understand any of this, but the code does exactly what I need it to do.. except is there any way to switch the LCSD/LCS to LCS/LCSD?

kylomas · December 11, 2017

Yes, they can be flopped but it's a hokey way due to sort order....give me a sec...

kylomas · December 11, 2017

Batman22,

Here it is in all it's ridiculous glory...

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

_ArrayDisplay($arows, 'Before flip')

; 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

_ArrayDisplay($arows, 'After flip')

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

IUIAutomation - Topic with framework and examples

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