Sorting A Text File Using Me

[ispaydeu]

Anyone know how to go about sorting a text file based off of a field in it? Lets say I was using the ASCII processing, and wanted to sort off the 3rd field in the file using ME, is this possible?

 

Matt

[Cory]

There is no sort function in ME. I' supprised that I don't see any in the PGMacros functions library either. Someone may have one though you could borrow. Beyond that you need to do it programmatically. http://en.wikipedia.org/wiki/Sorting_algorithm has some good algorithms you could emulate.

 

You might also consider using the strength of another application. It’s a simple matter to have Excel sort your data so you might consider employing it. This is the cool think about ME. You can use other applications as subroutines. For instance calculation of the square root of something is difficult programmatically. But it’s a breeze to pop open the Windows Calculator and use it.

[kevin]

To add to Cory's answer, you may want to use the 'sort' command that comes with Windows. The sort command sorts from the beginning of the line but it should be pretty simple to rearrange your file, sort it, and rearrange it back. Something like this might work if the text file is in ASCII, csv or tab delimited format.

// Delete c:\workfile.txt if it exists
If File Exists "workfile.txt"
 Delete File or Files: "workfile.txt"
End If

// Delete c:\outputfile.csv if it exists
If File Exists "outputfile.csv"
 Delete File or Files: "outputfile.csv"
End If

// Copy from the file c:\inputfile.csv to c:\workfile.txt rearranging the fields in each record
ASCII File Begin Process: "inputfile.csv" (Comma Delimited Text )
 // rearrange the record so field 3 is first
 Variable Set String %T90% ""%T3%","%T1%","%T2%","%T4%","%T5%""
 // write the record to the work file
 Variable Modify String: Append %T90% to Text File
ASCII File End Process

// Sort from c:\workfile.txt into c:\sorted.txt
Program Launch: "cmd.exe"
Wait for File Ready: "sorted.txt"

// Copy from the file c:\sorted.txt to c:\outputfile.csv rearranging the fields in each record
ASCII File Begin Process: "sorted.txt" (Comma Delimited Text )
 // rearrange the record so field 3 is first
 Variable Set String %T90% ""%T2%","%T3%","%T1%","%T4%","%T5%""
 // write the record to the work file
 Variable Modify String: Append %T90% to Text File
ASCII File End Process

Use this macro as a starting point. You may need to adjust how the records are written. You will need to change the filenames throughout. If sort.exe is not on your path then you may need to adjust the program launch command to include the path to sort.exe.

 

Here is the macro in direct editor format that you can copy and paste into a blank macro.

<REM2:Delete c:\workfile.txt if it exists><IFOTH:01:2:c:\workfile.txt><DOFILE:08:NN:c:\workfile.txt>><ENDIF><REM2:><REM2:Delete c:\outputfile.csv if it exists><IFOTH:01:2:c:\outputfile.csv><DOFILE:08:NN:c:\outputfile.csv>><ENDIF><REM2:><REM2:Copy from the file c:\inputfile.csv to c:\workfile.txt rearranging the fields in each record><ADFBEG:F10:001:000001:000000:c:\inputfile.csv><REM2:rearrange the record so field 3 is first><TVAR2:90:01:"%T3%","%T1%","%T2%","%T4%","%T5%"><REM2:write the record to the work file><TMVAR2:20:90:00:000:000:c:\workfile.txtT><ADFEND><REM2:><REM2:Sort from c:\workfile.txt into c:\sorted.txt><LAUNCHDEL2:0:01cmd.exe<PARAM>/C sort.exe < c:\workfile.txt >c:\sorted.txt><WFREADY:000000:000030:000000c:\sorted.txt><REM2:><REM2:Copy from the file c:\sorted.txt to c:\outputfile.csv rearranging the fields in each record><ADFBEG:F10:001:000001:000000:c:\sorted.txt><REM2:rearrange the record so field 3 is first><TVAR2:90:01:"%T2%","%T3%","%T1%","%T4%","%T5%"><REM2:write the record to the work file><TMVAR2:20:90:00:000:000:c:\outputfile.csvT><ADFEND>

You might be able to use the built-in options for sort.exe to streamline this process. Open a Command Prompt window and type 'sort /?' for a list of parameters.

[Cory]

Genius! I'm so happy I learned something that didn't cause me more work today.

 

Check this out for more description.

 

Also there's a cool Find equivelant. That cold be handy.