Re: Advanced? Parsing Methods
From: Albert D. Kallal (PleaseNOOOsPAMMkallal_at_msn.com)
Date: 10/07/04
- Next message: agodfried: "Difficulty accessing some VBA help topics from within MS Access co"
- Previous message: MikeC: "Advanced? Parsing Methods"
- In reply to: MikeC: "Advanced? Parsing Methods"
- Next in thread: MikeC: "Re: Advanced? Parsing Methods"
- Reply: MikeC: "Re: Advanced? Parsing Methods"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 6 Oct 2004 18:17:37 -0600
"MikeC" <anonymous@discussions.microsoft.com> wrote in message
news:Gk_8d.24966$QJ3.877@newssvr21.news.prodigy.com...
>I have developed a fully functioning sub that parses data from a CSV file
>and writes new records to a table in a SQL Server 2000 database.
>
> I am currently in the process of optimizing my code and was wondering if
> there is a more efficient/advanced way to parse the data before I pass it
> to my SQL string variable, called strSQL.
The above is a good question.Fact is we often deal with comma data, space
data, or all kinds of delimiters. Every developer over time will likely
build up a nice library of code. Prior to a2000, I wrote a function called
strDfield("text", "delimiter", which delimiter)
of course, you can do the same thing with split() command
So, to pass two values to a form via open args, the forms on-load event can
parse out the two values like:
parms1 = split(me.OpenArgs,"~")(0)
parms2 = split(me.OpenArgs,"~")(1)
Of couse, the above me.Open args would be some text like
"#11/11/2004#~StartDate"
So, parms1 would be a date, and the parms2 would be the field name for
example.
>
> Would it be faster to use the Split() function to parse each line (using a
> comma delimiter) and pass the parsed data to an array? Some other
> approach? If so, what would be the most efficient way to do this?
I don't know if you have a performance problem here do you? Certainly
building up your own spilt routines in place of whole bunch of mids, and
instrs will make your life a zillion times easer. So, I am certainly with
you in terms of the need for developers to have a nice grab bag of routines
that parse out strings..since this is common requirement.
So, are you looking to make the coding job easer, or are you looking to
increase the performance of what you got now working?
If you are looking at a performance issued, then yes..split is going to be
quite fast....
So, while you can use:
strSomevVale = split("one,two,three,four")(1)
The above would thus results in strSomeValue being now = two
So, for handy dandy reductions of code, the split is great, and you don't
have to put the results into an array.
However, since you need to repeat "hit" the string and pull out values, then
no need to run the split over and over again..is there?
Also, looking at what you got...you likely could have used transfer text to
a temp table..and then transfer that to the server....
As for performance increase?, I would use a recordset in place of executing
inserts each time...
would certainly go:
dim vBuf as varient
dim rstTable as new ADODB.Recordset
rstTable.Open "w$fxrateload", cnn1
vBuf = split(strSingeLine,",")
You now have a variant array full of the delimited data
rstTable.AddNew
rstTable!BatchNumber = v(0)
rstTable!from_currentcy = v(1)
rstTable!to_currency = v(2)
rstTable!exectuionstamp = now()
..etc. etc. etc.
rstTable.Update
You can see the above recordset is MUCH cleaner then the sql stamntet.
Futher, it will run about 100 times faster also....
-- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com http://www.attcanada.net/~kallal.msn
- Next message: agodfried: "Difficulty accessing some VBA help topics from within MS Access co"
- Previous message: MikeC: "Advanced? Parsing Methods"
- In reply to: MikeC: "Advanced? Parsing Methods"
- Next in thread: MikeC: "Re: Advanced? Parsing Methods"
- Reply: MikeC: "Re: Advanced? Parsing Methods"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
