VBS to sort lines of a text file

Question

I'm looking for a good way to sort lines of a text file, I don't know if the best way is to use arrays. Here is my file:

VW;blue;20000;link
VW;blue;21000;link
VW;blue;29000;link
VW;blue;28000;link
VW;blue;22000;link
VW;red;20000;link
VW;red;28000;link
VW;red;30000;link
VW;red;21000;link
VW;red;26000;link
MERCEDES;blue;30000;link
MERCEDES;blue;38000;link
MERCEDES;blue;40000;link
MERCEDES;blue;31000;link
MERCEDES;blue;37000;link
MERCEDES;red;40000;link
MERCEDES;red;40000;link
MERCEDES;red;47000;link
MERCEDES;red;41000;link
MERCEDES;red;44000;link

I need to reorganise a file, by sorting each brand of car, with the higher price for the blue one, and the cheaper for the red one. (There are only these 2 colors). The result I'm looking for is the following:

VW;blue;29000;link
VW;blue;28000;link
VW;blue;22000;link
VW;blue;21000;link
VW;blue;20000;link
VW;red;20000;link
VW;red;21000;link
VW;red;26000;link
VW;red;28000;link
VW;red;30000;link
MERCEDES;blue;40000;link
MERCEDES;blue;38000;link
MERCEDES;blue;37000;link
MERCEDES;blue;31000;link
MERCEDES;blue;30000;link
MERCEDES;red;40000;link
MERCEDES;red;40000;link
MERCEDES;red;41000;link
MERCEDES;red;44000;link

Any ideas?

yes it can easily be done, read everything into a jagged array, loop it per your preferences into another jagged array, then output it — MichaelEvanchik, Feb 07 '18 at 17:07
You may try to sort with desconnected ADODB Recordset. Anyway you need to split each string before sorting to extract a key field. — omegastripes, Feb 07 '18 at 17:59
https://stackoverflow.com/questions/29552725/sorting-files-by-numerical-order It's a general purpose sorting routine with options. Unlike the answer below, which uses ADO to read the file, here you read the file and use ADO to create the database in memory. — ACatInLove, Feb 08 '18 at 00:46
Must it be solved with VBScript? This would be a walk in the park with JScript? — Stephen Quan, Feb 09 '18 at 04:59
Haha, I'm open minded, will VBS or JS faster ? (I have around 10 000 lines to sort). — Adrien, Feb 12 '18 at 12:07

score 2 · Accepted Answer · answered Feb 07 '18 at 22:12

Database problems should be solved with database tools (ADO, ODBC, Schema.ini, SQL, IIF). As in this demo (64 Bit, Access driver):

Option Explicit

Const adClipString = 2

Dim oFS : Set oFS = CreateObject("Scripting.FileSystemObject")
WScript.Echo oFS.OpenTextFile("schema.ini").ReadAll()
WScript.Echo "--------------------------"
Dim oDb : Set oDb = CreateObject("ADODB.Connection")
Dim sCS : sCS = Join(Array( _
                    "Driver=Microsoft Access Text Driver (*.txt, *.csv)" _
                  , "Dbq=" & oFS.GetAbsolutePathName(".") _
                  , "Extensions=asc,csv,tab,txt" _
                ), ";")
WScript.Echo sCS
WScript.Echo "--------------------------"
oDb.Open sCS
Dim sSQL 
For Each sSQL In Array( _
                    "SELECT * FROM [48669323.csv]" _
                  , "SELECT * FROM [48669323.csv] ORDER BY Brand, Color, Price" _
                  , "SELECT * FROM [48669323.csv] ORDER BY Brand, Color, IIF(Color='red', Price * -1, Price)" _
                 )
    WScript.Echo sSQL
    WScript.Echo "--------------------------"
    WScript.Echo oDb.Execute(sSQL).GetString(adClipString, , ",", vbCrLf, "<NULL>")
    WScript.Echo "--------------------------"
Next
oDb.Close

output:

cscript 48669323.vbs
[48669323.csv]
Format=Delimited(;)
ColNameHeader=False
Col1=Brand Text
Col2=Color Text
Col3=Price Long
Col4=WTF Text

--------------------------
Driver=Microsoft Access Text Driver (*.txt, *.csv);Dbq=C:\Users\eh\tmp;Extensions=asc,csv,tab,txt
--------------------------
SELECT * FROM [48669323.csv]
--------------------------
VW,blue,20000,link
VW,blue,21000,link
VW,blue,29000,link
VW,blue,28000,link
VW,blue,22000,link
VW,red,20000,link
VW,red,28000,link
VW,red,30000,link
VW,red,21000,link
VW,red,26000,link
MERCEDES,blue,30000,link
MERCEDES,blue,38000,link
MERCEDES,blue,40000,link
MERCEDES,blue,31000,link
MERCEDES,blue,37000,link
MERCEDES,red,40000,link
MERCEDES,red,40000,link
MERCEDES,red,47000,link
MERCEDES,red,41000,link
MERCEDES,red,44000,link

--------------------------
SELECT * FROM [48669323.csv] ORDER BY Brand, Color, Price
--------------------------
MERCEDES,blue,30000,link
MERCEDES,blue,31000,link
MERCEDES,blue,37000,link
MERCEDES,blue,38000,link
MERCEDES,blue,40000,link
MERCEDES,red,40000,link
MERCEDES,red,40000,link
MERCEDES,red,41000,link
MERCEDES,red,44000,link
MERCEDES,red,47000,link
VW,blue,20000,link
VW,blue,21000,link
VW,blue,22000,link
VW,blue,28000,link
VW,blue,29000,link
VW,red,20000,link
VW,red,21000,link
VW,red,26000,link
VW,red,28000,link
VW,red,30000,link

--------------------------
SELECT * FROM [48669323.csv] ORDER BY Brand, Color, IIF(Color='red', Price * -1, Price)
--------------------------
MERCEDES,blue,30000,link
MERCEDES,blue,31000,link
MERCEDES,blue,37000,link
MERCEDES,blue,38000,link
MERCEDES,blue,40000,link
MERCEDES,red,47000,link
MERCEDES,red,44000,link
MERCEDES,red,41000,link
MERCEDES,red,40000,link
MERCEDES,red,40000,link
VW,blue,20000,link
VW,blue,21000,link
VW,blue,22000,link
VW,blue,28000,link
VW,blue,29000,link
VW,red,30000,link
VW,red,28000,link
VW,red,26000,link
VW,red,21000,link
VW,red,20000,link

--------------------------

VBS to sort lines of a text file

1 Answers1