Re: Sorting Array's via VBS / JScript

From: tlviewer (tlviewerSHRUB_at_yahooCHENEY.com)
Date: 09/23/04 

Date: Wed, 22 Sep 2004 17:34:12 -0700

"Mr. Lee" <Nospam@email.net> wrote in message news:uUfOYGLoEHA.3424@TK2MSFTNGP12.phx.gbl...
> I have over 74,000 records.
> Thanks for the code.
> I'll play with that and see how that works.

When handling that many records, I found that I had
declare some of the array stack pointers as Long instead
of integer (VB-HuthSort).

The CPP qsort had no trouble with 80k records. So after
fixing HuthSort I get these times for an
array of 80k strings

    Wrapped CPP-qsort = .28 sec
    Wrapped VB HuthSort = 1.5 sec
    native VBS quicksort = 5.85 sec

hth,
tlviewer

>
> "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: Sorting Arrays via VBS / JScript
    ... I give a rat a** on your rat observations. ... >> the file has become too large for me to sort in excel, ... > Its rare that a simple bubble sort in native VBS is not fast enough. ... > dim myIn, myhelpr ...
    (microsoft.public.scripting.wsh)
  • Re: Make Table Query - Sorting Errors
    ... Dim OutputTable As DAO.Recordset ... The query to concatenate the CAMPNO values will need to be LOOKING at the ... If you post the query you are using to do this, I beleive we can get it to ... Is there a command that I can use in the module code to first sort the ...
    (microsoft.public.access.queries)
  • 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)
  • Re: VBA Sort 2-dimensional array based on 2 column
    ... Maybe you could transpose the List, sort, transpose again, and stick it back ... Dim conlist as Variant ... Function Transpose(vOld As Variant) As Variant ... 'MergeSort recursively calls itself until we have lists short ...
    (microsoft.public.office.developer.vba)