Re: Sorting Array's via VBS / JScript

From: Mr. Lee (Nospam_at_email.net)
Date: 09/22/04 

Date: Wed, 22 Sep 2004 10:36:19 -0400

I have over 74,000 records.
Thanks for the code.
I'll play with that and see how that works.

"tlviewer" <tlviewerSHRUB@yahoo.CHENEY.com> wrote in message
news:ORE1ar3nEHA.3988@tk2msftngp13.phx.gbl...

"Mr. Lee" <Nospam@email.net> wrote in message
news:utnk9wxnEHA.3896@TK2MSFTNGP15.phx.gbl...
> I have a large csv file that I'm loading into an array (vbscript) for some
> analysis.
>
> I need to sort the file/Array first so I can do proper reading.
> the file has become too large for me to sort in excel, and when I use the
> examples for vbscript sorting, it takes a huge amount of time.
>
> I've seen that jscript has a Sort Function but I'm not sure how I can get
> it
> to work in my vbscript.
>
> I'm doing something like this.
>
>
> set f = fso.opentextfile("mycsvfile.csv")
> MyArray = split(f.readall,vbcrlf)
>
> ' Now that I have the File in the array. I want to sort at this point. Can
> someone tell me what I need to do to be able to incorporate the jscript
> function into this?
>
> Thanks in advance.
> Mr. Lee
>

Its rare that a simple bubble sort in native VBS is not fast enough.
It takes 2000+ records before you will start to notice a difference.

Michael Harris has published a WSC quicksort scriptlet that
improves on a bubble sort.

http://www.aspobjects.com/process/code_repository/wsc/?file=Sort.wsc&subject=Array+Sorting+Routines

If you need even better speed, lately I tested a VB
wrapper around the CPP qsort, which will sort a variant
array of Bstr's in place.

I can sort a 10000 element array of random 23 char strings
in .0312 seconds ( P4 2.0 Ghz). This is up to 50 times
faster than a quicksort coded in native VBS.

It works great from VBS
    http://4.11.211.97/cpp_qsort.zip (52 kB)

contains the VB DLL source, the CPP DLL source, and
some VBS samples.

' begin SortTest.vbs
''''''''''''''''''''''''''
option explicit
dim oArg
dim myIn, myhelpr

' extreme importance
' must first declare without parens
dim arTest

dim myAr, i,sOut, oMain, sTime
redim arTest(10000)
dim arOut

dim seed,t, oSort, sAscending , sDescending

seed = 4096
randomize seed
Rnd -1

' build test array of random 23 char strings
for i = 0 to Ubound(arTest)
   arTest(i)= Cstr(RanPwd(23))
   'sOut = sOut & arTest(i) & vbCrLf
next

wscript.echo vartype(arTest)
'wscript.echo Join( arTest, vbCrLf)
'set oSort = createobject("Sort.WSC")
set oSort = WScript.CreateObject("prjCSort.csort")

' in case of WSC
sAscending = "x1<x2"
sDescending = "x1>x2"

t = Csng(Timer)

arTest = oSort.qsort( arTest) ' .019 sec, 10k records
'arTest = oSort.HuthSort(arTest)
'oSort.QuickSortArray arTest, sAscending

sTime = FormatNumber(Csng(Timer)-t,9,-1)
wscript.echo "elapsed time=" & sTime & vbCrLf & Join( arTest, vbCrLf)

set oSort = nothing

' helper function
''''''''''''''''''''
' generate a random password from legal characters
Function RanPwd(nLen)
   dim arrChar, nJ , spwd, schar, ran
   arrChar = array("a","b","c","d","e","f","g","h", _
    "i","j","k","l","m","n","o","p","q","r", _
    "s","t","u","v","w","x","y","z", _
"A","B","C","D","E","F","G","H","I",_
"J","K","L","M","N","O","P","Q","R", _
"S","T","U","V","W","X","Y","Z", _
     "-","_","%","#","|", _
     "0","1","2","3","4","5","6","7","8","9")

   if nLen = 0 then nLen = 10
   spwd = ""
   for nJ = 0 to nLen -1
       ran = int(66 * Rnd) + 1
       'wscript.echo ran
       schar = arrChar(ran)
       spwd = spwd & schar
   next
   RanPwd = spwd
end function
' end script

good luck,
tlviewer

Relevant Pages

  • Re: Counting IP address in firewall log
    ... > array, but I cann't figure out what to do next to count each unique IP. ... > Dim objFSO, objFWFile, arrLine, i ... I also did a REAL cheesy sort using JS for no particular reason then to see ... var rtn; ...
    (microsoft.public.scripting.vbscript)
  • Re: VBA Sort 2-dimensional array based on 2 column
    ... I pull all of the data into an array then I ... Some of the fields are blank, can that mess up the sort? ... Dim i As Long, j As Long ... 'MergeSort recursively calls itself until we have lists short enough ...
    (microsoft.public.office.developer.vba)
  • Re: VBA Sort 2-dimensional array based on 2 column
    ... a simple bubble sort should work fine. ... If your array is named myArray, and you want to sort by column 5 (say age ... Dim i As Long, j As Long ... Dim ColNr As Long ...
    (microsoft.public.office.developer.vba)
  • Morefunc VSort with array of long output
    ... I picked up a really neat trick from Jim Mack to alter the lBound of an array. ... Dim VType As Integer ... 'Will be about 4 to 5 times faster than a quicksort and can sort ...
    (microsoft.public.excel.programming)
  • Re: Detecting a running process.
    ... Private Declare Function EnumProcessModules Lib "psapi.dll" _ ... (ByVal dwProcessID As Long, _ ... Dim nProcesses As Long ... 'fill an array of longs with the ...
    (microsoft.public.vb.winapi)