How To: Sort Strings Or Numbers

[floyd]

This macro generates and calls a VBS script to sort a single dimension array of words or numbers. The resulting sorted array is written back to Macro Express in the form of a string.

 

It is meant to show the different features of the generated QuickSort algorithm, which converts a string of elements to be sorted into an array. Each element is separated by a delimiter character, or string of characters, that you specify. There are two sample strings built in to this example macro. A string of words and a string of numbers. But you may create your own test strings.

 

Because this example generates a VBS script, which in turn writes a string variable back to the macro, you only need to import the attached playable (.mxe) file into any Macro Express library (.mex) file. Nothing else is required to use the sorting algorithm. There are no external files.

• Sort word strings or numbers
  
• You choose the delimiter to use
  
• Sort all string elements or just some of them
  
• Choose to sort by case or to ignore case
  
• Sort in ascending or descending order
  

The attached macro is well documented. And there is an extensive explanation in the header section. Any temporary files created by the macro in your system temp folder are erased before the macro terminates.

 

Jan-28-2005 Made some changes to the macro concerning the Repeat Loops that follow the Program Launch command. See internal comments for details.

SingleDimensionSort.zip

Edited January 28, 2005 by floyd

[randallc]

Hi Floyd!

 

I like the Quicksort!

I had to add 2 lines after the vbs program launch, else the program wscript would only appear to run once on some computers (had already finished before the check if running!);

      Program Launch: "%T6%%T7%.vbs"

      Repeat Until %N1% <> %N1%

      If Program Name "wscript.exe" is running

        OR

      If File Exists "%T6%%T7%.mxe"

        Repeat Exit

      End If

      Repeat End

 

Best, Randall

[floyd]

Thanks Randall

 

Glad you like it, but as noted in the script itself, I cannot take credit for the original algorithm. It is a fast little bugger 'though, eh?

 

I will change the macro to add your lines at some point when time allows. Thanks for looking at it and I hope it is useful to everybody!

[randallc]

Hi, Floyd,

 

I had time on holidays to get the Quicksort working in MacroExpress; fiddly!

 

I thought you might be interested; it starts from generating an example array, and gives the option of seeing the result from your vbs script to compare. not so many options as you gave!

 

best, Randall.

(A zipped file needs you to open the macro file (or import I suppose) A number of interlinked macros; "Alt-Cntl-"O"" "O"pens it!) Here's the main quicksort sub;

Macro Express Script for "QuickSort" (No Activation) 20/01/2005 11:12 PM

 

    // Sub QuickSort( arrToSort, intAlphaRow, intOmegaRow, booUseCase )

    Macro Run: TwoRows

    Macro Run: ThreeOrMore

    // Do (....loop while)

    Repeat Until %N1% >= %N2%

                    Variable Set String %T1% from Environment Variable

                    If Variable %T1% = "0"

                                                         Macro Run: FindIntLowRow

                                                         Macro Run: FindIntHighRow

                    Else

                                                         Macro Run: FindUCaseIntLowRow

                                                         Macro Run: FindUCaseIntHighRow

                    End If

                    Macro Run: SwapIntLowRow

    Repeat End

    // arrToSort(intAlphaRow) = arrToSort(intHighRow) T1

    Variable Set String %T1% from Environment Variable

    Variable Modify String: Save %T1% to Environment Variable

    // arrToSort(intHighRow) = varPivot T2

    Variable Set String %T2% from Environment Variable

    Variable Modify String: Save %T2% to Environment Variable

    // ' Two or more rows in first section

    Read Registry Integer: "intHighRow"

    Variable Modify Integer: %N4% = %N2% - 1

    // If intAlphaRow N5 < N4 (intHighRow - 1) Then Call QuickSort( arrToSor

    Read Registry Integer: "intAlphaRow"

    If Variable %N5% < variable %N4%

                    Macro Run: AlphaOmegaNum

                    Write Registry Integer: "intOmegaRow"

                    Variable Modify Integer: Convert %N4% to text string %T1%

                    Variable Modify String: Trim %T1%

                    Variable Modify String: Save %T1% to Environment Variable

                    Macro Run: QuickSort

                    Macro Run: AlphaOmegaRestore

    End If

    // ' Two or more rows in second section

    Read Registry Integer: "intHighRow"

    Variable Modify Integer: %N4% = %N2% + 1

    Read Registry Integer: "intOmegaRow"

    // If intHighRow + 1 < intOmegaRow Then Call QuickSort( arrToSort, intHi

    If Variable %N4% < variable %N5%

                    Macro Run: AlphaOmegaNum

                    Write Registry Integer: "intAlphaRow"

                    Variable Modify Integer: Convert %N4% to text string %T1%

                    Variable Modify String: Trim %T1%

                    Variable Modify String: Save %T1% to Environment Variable

                    Macro Run: QuickSort

                    Macro Run: AlphaOmegaRestore

    End If

    Macro Return

 

The ME version stays under 3seconds up to 100 elements (no loading/ saving?, so faster than vbs version from ME, which is always 3-4 secs even for 2 elements!), then becomes slower; 10 seconds for 200 (vbs 4secs), 50secs for 1000; (vbs5secs!) I suppose it still may useful if you wish to stay in ME, and are only doing small sorts (<100!)

 

The zip file has screen pics of the menus in Word doc.

btw, How do you you add pics to these emails? "http" needed at website location?

macexsorterOnly4.zip

[floyd]

Interesting ... a sort routine using all native Macro Express code. Bet you learned a lot. These kinds of exercises are what I cut my teeth on when Joe first introduced me to Macro Express. Great way to learn. I spent hours on this and that until I got a feel for it.

 

Most of the slow-down in your macro occurs because you are doing a lot of reading and writing to and from the environment space. It takes up most, if not all, the time. The same can be said for the vbs script in my original QuickSort macro, except that it generates, writes, and reads a single file.

 

As to attaching pictures, they must be located within a URL somewhere. I do not know why.

[Les Hazlett]

Sorting is a wonderful addition to Macro Express capability. Thanks Floyd for providing it.

 

Now I must try to adapt it to what I am doing. I confess I am lost in your macro. I understand what you are doing in general but not some of the specific statements you use. I would like to discover the answers in the ME documentation and not ask "dumb" questions. But, I have tried and am stuck. If there is documention to answer my questions please point me to it.

 

//Loop until user chooses to cancel

Text Type: <REP3:08:000002:000002:0001:1:01:N1

Text Type:

 

I don't understand what this does. When I run the macro, there is no user involved "delay" prior to the Variable Type dialog box. I don't understand how to execute commands such as a Repeat within a Text Type statement. I can't find anything about it in the documentation.

 

// Reset variables that are to be reused.

Text Type: <CLEARVAR1:T:1:2>

 

Same problem. I don't know how to execute commands with a Text Type statement. I can use the CLEARVAR command from the Script Editor and it does what I expect. When I try to use this CLEARVAR1 command in a Text Type statement it doesn't clear T1 & T2 or do anything else that I can observe.

 

Wonderful mysteries. Give me a clue and I will study further and learn to understand.

 

Thanks again for the sorting method. I am anxious to make it work.

 

Les Hazlett

Fargo, ND

 

"It's snowing snowing snowing snowing"

[floyd]

Les -

 

It looks like your macro has screwed up somehow. None of those Text Type lines should be there. Instead, they should be Repeat Until %N1% <> %N1% and Clear Text Variables: From 1 To 2 commands.

[Les Hazlett]

Thanks Floyd,

 

After importing the MXE again all is well. The error was deceptive. The macro ran and performed as you described except that it didn't automatically repeat when finished. I can't even guess how it got messed up.

 

Back to my study of your great macro.

 

Les

[floyd]

It should repeat. Maybe another of those "Text Type" things happened to you. Which release of Macro Express are you using?

 

The demo makes it look like there is a lot involved in using the QuickSort script. But 95% of the demo was created to show different features of the algorithm. There are only 7 variables that need to be set before calling the script

1. A temporary file name
   
2. The string of elements to sort
   
3. A delimiter string
   
4. The starting element to sort
   
5. The ending element
   
6. Case or no case
   
7. Ascending or descending
   

Eventually the script will be changed to use default values so that a person will only need the top two variables in the list.

[Les Hazlett]

Floyd,

 

Sorry that my response was confusing. Your macro runs correctly with ME version 3.5.0.1. The way it ran with the corrupted "repeat" statement was deceptive because the macro ran "except for repeating".

 

You did a fine job of documenting both your ME macro and the VB script. The documentaion and your last message will be quite valuable to those of us trying to use Quicksort. I am deep into doing what you discribed - using default parameters.

 

All was going very well until I developed a strange problem. I had removed much of the ME code and even put some of the defaults into the script. Now, for some reason it intermittently stalls in the Repeat Until %N1% <> %N1 loop after the Program Launch %T6%%T7%.vbs. Sometimes it works and sometimes it stalls. When it works the sort is correct. When it stalls, both temporary files are in the temp folder and they are correct. Once it stalls, it continues to stall until I clear out the temporary files that are left when the macro is aborted. Then it works again - once.

 

Perhaps you have some idea how this could happen. If you can help, but this type of discussion is inappropriate for the Forum, please use my email - <lesjanehazlett@email.com>. I am temporarily running again "dangerously" by deleting the repeat loop and merely waiting indefinely for file ready.

 

Quicksort is fast and it is great to have the ability to sort.

 

Thanks,

Les

[Les Hazlett]

I was so excited when I got my linesort macro running. Then I tested it on larger and larger data sets. It runs great (fast) until it gets too large a data file. Then, ME reports - "Macro Express Player has encountered an error and will be closed." I would have expected that it would slow dramatically if it needed to use virtual memory. I wouldn't have expected that it would blow its mind and wipe out ME.

 

Does anyone have any idea what happened?

 

I'm not sure what to report to help you determine the problem. I am running ME 3.5.0.1 under XP Pro - Media Center. My computer has 512 MB RAM and over 100 GB of open hard drive.

 

This is rough but perhaps useful:

Largest file to run was ~60KB.

I was sorting ~290 lines that were ~330 characters long.

It only took 5 seconds to sort.

It failed with a file of ~120KB

It failed again with a file of ~80KB

 

Being able to sort is great, but how big a real job will I be able to sort?

 

Les

[randallc]

Hi,

 

Yes, I await Floyd's thoughts with interest. I have made a new .vbs script which handles arrays written to the registry (crude version not ready to attach yet!; string type vbs is not working in it despite the menu option!) to avoid the strings. I think I can speed things up by only ever once having to make the vbs script, check each run that it exists too. (and no need for mxe file)

 

How have you been handling your lists? - in txt files?

 

I have had to avoid large strings and write individually to notepad if the strings would be larger than 16K.

 

Best, Randall

[Les Hazlett]

Hi Randall,

 

Yes - we use Tab delimited text strings/files mostly.

 

After getting stuck with limited string sizes in the VB Quicksort method, we took another approach. This approach was easier because we didn't need a true sort. I just posted this macro as a separate topic - "ALMOST SORTING".

 

We are delighted with this macro. It does what we need and it is very fast. It easily handled the "live" file that choked the VB Quicksort macro.

 

Les

[floyd]

Les -

 

The sorting bogging down and Macro Express crashing are two separate issues.

 

A replacement macro has been uploaded, which adds a Delay in the loop that follows the Progam Launch command. This will take care of the bogging down problem because the VBS script will have more time on the CPU to do its task.

 

The QuickSort script is sorting 10,000 words in a 140k file in under 2 seconds. It seems pretty fast to me, however, the example macro itself takes quite a few seconds longer to prepare the string built from the file. The crash is occurring when the Load Macro Text File command fires to retrieve the sorted string. There may be a limit to the size of a playable MXE file. So, the problem is a limitation within the example macro and not the QuickSort itself.

 

Randall - There is a 16k length limitation in the Windows Registry. If it were me, I would stay as far away as possible from using the Registry for any kind of sorting task.

[randallc]

Hi, Floyd et al!

 

I am posting another macro like the Sor Single of Floyd's; taking his advice to avoid the registry for sorting, but saving the macro extra time. I still use the registry just for timing and to handle passing the indices to Quicksort.

All his options other than "Descending" are not working on loaded files.

 

The macro prompts for a"cvs" file to load from a directory. it will use a "txt" file if you force it, but won't work at present unless comma-delimited anyway.

 

Please ask if questions. It may compete with the "almost sort" in speed (2000 by 300 takes 10 secs, mostly load file time!, 1-2 secs to sort, I think).

 

The slowing down after about 140K is still related to having sucha big string in memory, and would need arrays or somewhere else such as registry to use to sort, i think.

 

Why would the huge files not be just loaded into Excel and sorted there? Most ME3 sorts would be on small lists, I would expect?

 

Best , Randall

(PS reply shows zipped files of example "cvs"

Please let me know if not working on other machines?)

SortVBS.mxe

[randallc]

Hi, here are the examples;

Best, Randall

ExampleCSVfiles.zip

[kevin]

Hello all,

 

After a bit of research (thus the delay in my reply) we have a some information about why the sort macro doesn't work when the number of items to sort gets moderatly large.

 

Each macro command has a limit of 64K (65,536 bytes). The routine, as written, was trying to create a 'Variable Set String' command that exceeded this limit. Then Macro Express tried to interpret the extra information beyond the 64K boundary as additional macro commands. Since the additional information is not in the proper format, Macro Express displayed a message that an unknown command format was found and the macro was stopped.

 

The solution is to write the sorted string to a file and then read the file into a variable using the Variable Set String from File command.