Sorting a table

[Anonym 53]

Anyone who has come up with an idea on how to sort a table?

[JohnB 255]

By what? Like sort it so all rows remain intact? Depending on how many columns and the type of data, that could be a logistical nightmare!

 

John

[Anonym 53]

Yes, I know, but after a bad night's sleep I just got an idea that I will test.

 

Maybe table sorting should be supported in the platform...?

 

 

The idea of passing lists/tables as parameters to commands/functions would be really nice to have now.

[Anonym 53]

Well, hmm, my idea is to approach the table as a set of lists. There is a sort list function. If I am able to identify the one column I want to sort by and let that prefix that specific row/list, and then do a sort list, then remove the prefix and then save it back as a set of lists to the file, well at least in my head, this should be possible to work.

 

Are you with me?

[Anonym 53]

It's me again...

 

It will be a bit tricky when any of the columns in the table is a quoutation marked string, but I will try to solve that with my very recently added skills in regular expressions.

 

So basically the idea is as follows:

 

Read the table from the file as a table
Get the number of cols of the table
Clear the table
Test so that the col num u want to sort by is less than the num of cols
Read the table as a set of lists
For each element in the list { 
        Find the column (it is something that is delimited with commas)
        copy it
        prefix the list element with it
}
Perform a sort list
For each elem in the list {
         Remove the prefix
}
Save the list as a file

 

Done!

 

 

Hmmmm... It should work. ...or not.

[Anonym 53]

Ta da!

 

 

comment("Read the table from the file as a table
Get the number of cols of the table
Clear the table
Test so that the col num u want to sort by is less than the num of cols
Read the table as a set of lists
For each element in the list \{ 
        Find the column (it is something that is delimited with commas)
        copy it
        prefix the list element with it
\}
Perform a sort list
For each elem in the list \{
         Remove the prefix
\}
Save the list as a file")
ui open file("Table file", #tableFile)
ui save file("Sorted Table file", #sortedTableFile)
ui text box("Sort col no>", #sortColNo)
ui drop down("Sort Order", "Ascending,Descending", #sortOrder)
ui stat monitor("numCols> ", #numCols)
ui stat monitor("item> ", #item)
set(#prefixDelimiter, "||==>>", "Global")
clear table(&myTable)
create table from file(#tableFile, &myTable)
set(#numCols, $table total columns(&myTable), "Global")
clear table(&myTable)
if($comparison(#sortColNo, "<", #numCols)) {
   then {
       set(#i, 0, "Global")
       clear list(%tmpList)
       add list to list(%tmpList, $list from file(#tableFile), "Delete", "Global")
       clear list(%listToSort)
       loop($list total(%tmpList)) {
           set(#item, $list item(%tmpList, #i), "Global")
           clear list(%parseRow)
           add list to list(%parseRow, $find regular expression(#item, "(\"(?:[^\"]|\"\")*\"|[^,]*)"), "Delete", "Global")
           comment("colIndex is a dirty fix since there is an error in 
my regexp that is causing an extra blank line after 
each list item . Not beautyful, but it works. 
")
           set(#colIndex, $eval($add($eval($multiply(#sortColNo, 2)), 1)), "Global")
           set(#prefix, $list item(%parseRow, $eval($subtract(#colIndex, 1))), "Global")
           set(#newPrefixedItem, "{#prefix}{#prefixDelimiter}{#item}", "Global")
           add item to list(%listToSort, #newPrefixedItem, "Delete", "Global")
           increment(#i)
       }
       clear list(%sortedList)
       clear list(%tmpSortedList)
       add list to list(%tmpSortedList, $sort list(%listToSort, #sortOrder), "Delete", "Global")
       set(#i, 0, "Global")
       loop($list total(%tmpSortedList)) {
           set(#item, $list item(%tmpSortedList, #i), "Global")
           set(#posAfterPrefix, $eval($add($find index(#item, #prefixDelimiter), $text length(#prefixDelimiter))), "Global")
           set(#remainCharacters, $eval($subtract($subtract($text length(#item), $find index(#item, #prefixDelimiter)), $text length(#prefixDelimiter))), "Global")
           set(#newItem, $substring(#item, #posAfterPrefix, #remainCharacters), "Global")
           add item to list(%sortedList, #newItem, "Delete", "Global")
           increment(#i)
       }
       save to file(#sortedTableFile, %sortedList)
       clear list(%tmpList)
       clear list(%listToSort)
       clear list(%parseRow)
       clear list(%tmpSortedList)
       clear list(%sortedList)
   }
   else {
       comment("The column to sort by does not exist.")
   }
}

 

 

As mentioned in one of the comments, there is an error in my regular expression that I use to parse out the column that I want to sort by. If someone knows how to solve it (and I am sure since there are many smart ppl here), pls go ahead and post the updated regexp in this thread.

 

 

IF UB4 was able to pass list/tables as parameters >ehhh, notch, notch <, this could have been a pretty nice generic sort functionality.

 

 

Well, anyway, this works. It might not be the most beautyful code you have ever seen, but it does work.

 

 

 

Hmmm... It should be possible to sort by two or more columns. I'll be back.

[Guest klauzser]

Ta da!

 

 

comment("Read the table from the file as a table
Get the number of cols of the table
Clear the table
Test so that the col num u want to sort by is less than the num of cols
Read the table as a set of lists
For each element in the list \{ 
        Find the column (it is something that is delimited with commas)
        copy it
        prefix the list element with it
\}
Perform a sort list
For each elem in the list \{
         Remove the prefix
\}
Save the list as a file")
ui open file("Table file", #tableFile)
ui save file("Sorted Table file", #sortedTableFile)
ui text box("Sort col no>", #sortColNo)
ui drop down("Sort Order", "Ascending,Descending", #sortOrder)
ui stat monitor("numCols> ", #numCols)
ui stat monitor("item> ", #item)
set(#prefixDelimiter, "||==>>", "Global")
clear table(&myTable)
create table from file(#tableFile, &myTable)
set(#numCols, $table total columns(&myTable), "Global")
clear table(&myTable)
if($comparison(#sortColNo, "<", #numCols)) {
   then {
       set(#i, 0, "Global")
       clear list(%tmpList)
       add list to list(%tmpList, $list from file(#tableFile), "Delete", "Global")
       clear list(%listToSort)
       loop($list total(%tmpList)) {
           set(#item, $list item(%tmpList, #i), "Global")
           clear list(%parseRow)
           add list to list(%parseRow, $find regular expression(#item, "(\"(?:[^\"]|\"\")*\"|[^,]*)"), "Delete", "Global")
           comment("colIndex is a dirty fix since there is an error in 
my regexp that is causing an extra blank line after 
each list item . Not beautyful, but it works. 
")
           set(#colIndex, $eval($add($eval($multiply(#sortColNo, 2)), 1)), "Global")
           set(#prefix, $list item(%parseRow, $eval($subtract(#colIndex, 1))), "Global")
           set(#newPrefixedItem, "{#prefix}{#prefixDelimiter}{#item}", "Global")
           add item to list(%listToSort, #newPrefixedItem, "Delete", "Global")
           increment(#i)
       }
       clear list(%sortedList)
       clear list(%tmpSortedList)
       add list to list(%tmpSortedList, $sort list(%listToSort, #sortOrder), "Delete", "Global")
       set(#i, 0, "Global")
       loop($list total(%tmpSortedList)) {
           set(#item, $list item(%tmpSortedList, #i), "Global")
           set(#posAfterPrefix, $eval($add($find index(#item, #prefixDelimiter), $text length(#prefixDelimiter))), "Global")
           set(#remainCharacters, $eval($subtract($subtract($text length(#item), $find index(#item, #prefixDelimiter)), $text length(#prefixDelimiter))), "Global")
           set(#newItem, $substring(#item, #posAfterPrefix, #remainCharacters), "Global")
           add item to list(%sortedList, #newItem, "Delete", "Global")
           increment(#i)
       }
       save to file(#sortedTableFile, %sortedList)
       clear list(%tmpList)
       clear list(%listToSort)
       clear list(%parseRow)
       clear list(%tmpSortedList)
       clear list(%sortedList)
   }
   else {
       comment("The column to sort by does not exist.")
   }
}

 

 

As mentioned in one of the comments, there is an error in my regular expression that I use to parse out the column that I want to sort by. If someone knows how to solve it (and I am sure since there are many smart ppl here), pls go ahead and post the updated regexp in this thread.

 

 

IF UB4 was able to pass list/tables as parameters >ehhh, notch, notch <, this could have been a pretty nice generic sort functionality.

 

 

Well, anyway, this works. It might not be the most beautyful code you have ever seen, but it does work.

 

 

 

Hmmm... It should be possible to sort by two or more columns. I'll be back.

 

You don't have to make it beautiful. As long as it works perfectly, that would be fine.

[Anonym 53]

Uhm, I found some bugs. Working with temporary lists should NEVER cause a deletion of a duplicate in such a list.

 

Here is the fix.

 

comment("Read the table from the file as a table
Get the number of cols of the table
Clear the table
Test so that the col num u want to sort by is less than the num of cols
Read the table as a set of lists
For each element in the list \{ 
        Find the column (it is something that is delimited with commas)
        copy it
        prefix the list element with it
\}
Perform a sort list
For each elem in the list \{
         Remove the prefix
\}
Save the list as a file")
ui open file("Table file", #tableFile)
ui save file("Sorted Table file", #sortedTableFile)
ui text box("Sort col no>", #sortColNo)
ui drop down("Sort Order", "Ascending,Descending", #sortOrder)
ui stat monitor("numCols> ", #numCols)
ui stat monitor("item> ", #item)
set(#prefixDelimiter, "||==>>", "Global")
clear table(&myTable)
create table from file(#tableFile, &myTable)
set(#numCols, $table total columns(&myTable), "Global")
clear table(&myTable)
if($comparison(#sortColNo, "<", #numCols)) {
   then {
       set(#i, 0, "Global")
       clear list(%tmpList)
       add list to list(%tmpList, $list from file(#tableFile), "Don\'t Delete", "Global")
       clear list(%listToSort)
       loop($list total(%tmpList)) {
           set(#item, $list item(%tmpList, #i), "Global")
           clear list(%parseRow)
           add list to list(%parseRow, $find regular expression(#item, "(\"(?:[^\"]|\"\")*\"|[^,]*)"), "Don\'t Delete", "Global")
           comment("colIndex is a dirty fix since there is an error in 
my regexp that is causing an extra blank line after 
each list item . Not beautyful, but it works. 
")
           set(#colIndex, $eval($add($eval($multiply(#sortColNo, 2)), 1)), "Global")
           set(#prefix, $list item(%parseRow, $eval($subtract(#colIndex, 1))), "Global")
           set(#newPrefixedItem, "{#prefix}{#prefixDelimiter}{#item}", "Global")
           add item to list(%listToSort, #newPrefixedItem, "Don\'t Delete", "Global")
           increment(#i)
       }
       clear list(%sortedList)
       clear list(%tmpSortedList)
       add list to list(%tmpSortedList, $sort list(%listToSort, #sortOrder), "Don\'t Delete", "Global")
       set(#i, 0, "Global")
       loop($list total(%tmpSortedList)) {
           set(#item, $list item(%tmpSortedList, #i), "Global")
           set(#posAfterPrefix, $eval($add($find index(#item, #prefixDelimiter), $text length(#prefixDelimiter))), "Global")
           set(#remainCharacters, $eval($subtract($subtract($text length(#item), $find index(#item, #prefixDelimiter)), $text length(#prefixDelimiter))), "Global")
           set(#newItem, $substring(#item, #posAfterPrefix, #remainCharacters), "Global")
           add item to list(%sortedList, #newItem, "Don\'t Delete", "Global")
           increment(#i)
       }
       save to file(#sortedTableFile, %sortedList)
       clear list(%tmpList)
       clear list(%listToSort)
       clear list(%parseRow)
       clear list(%tmpSortedList)
       clear list(%sortedList)
   }
   else {
       comment("The sort by column no does not exist.")
   }
}

[Anonym 53]

Just a question: Do you guys/gals see a need for a SortTable function such as this? If so I can continue to post updates, if not then I will just let this thread sink in.

[rocket976 62]

Yea post updates this was very helpful for me.  Thanks.

[utsavat 1]

yes the Short table function would be really handy

 

but there are other ways also to have the tables sorted

Edited February 18, 2013 by utsavat

[Anonym 53]

Ok, I'll remember that. No one expressed any interest in this thread when I published my code so I thought; "Hey, what the heck, I'll keep it to myself then"   There are a few updates, I just need to find the code (I had a disk crash at the beginning of this year, and I haven't used it since so... Well, there IS a backup somewhere. )

[UBotBuddy 331]

Until a process is cooked up you can do this.

 

Load your table

build a new List with the first entry have the Table's entire row of info in that 1st List entry

Then do the same for the entire Table's contents

Then Sort the List

Save as a CSV

Then Load that CSV into a new Table or Clear the original

 

Buddy

[Anonym 53]

This is actually what my code is doing, but on top of that it adds the possibility to sort a column of your choice.

 

It's not very fast, but it does work.