How extract a substring?
Basics: VB6 sp5 on NT4.0
Question: How can I extract a sub string from between two particular ASCII characters (value marks)?
The input is a long string that has many sub strings in it. Each sub string is separated by a 'value mark'. For example, I might want to extract the 2nd sub string (that sub string between the 1st value mark and the 2nd value mark. There is no leading value mark.) Or I might want to pull the 23rd sub string (that sub string between the 22nd value mark and the 23rd value mark.)
As there will be over 100,000 long strings (records) that I will have to process, I want something that is quick as opposed to counting where each value mark is in the long string and then using the MID function based on the count. I will need to extract about 24 sub strings from each long string. Also, each sub string can vary in length.
Thanks for the help.
Question: How can I extract a sub string from between two particular ASCII characters (value marks)?
The input is a long string that has many sub strings in it. Each sub string is separated by a 'value mark'. For example, I might want to extract the 2nd sub string (that sub string between the 1st value mark and the 2nd value mark. There is no leading value mark.) Or I might want to pull the 23rd sub string (that sub string between the 22nd value mark and the 23rd value mark.)
As there will be over 100,000 long strings (records) that I will have to process, I want something that is quick as opposed to counting where each value mark is in the long string and then using the MID function based on the count. I will need to extract about 24 sub strings from each long string. Also, each sub string can vary in length.
Thanks for the help.
Hey E,
I would recommend XML and it's DOM, for such a process. Let me know if you need more info.
Dave
I would recommend XML and it's DOM, for such a process. Let me know if you need more info.
Dave
ASKER
Thank you for the suggestions. Dave, I don't know anything about XML and it's DOM.
Ok, can you tell me what exactly you are trying to accomplish, then I'll tell you whether an XML solution is applicable.
Thanks
Thanks
ASKER
I am using some jBase object code to read records from a Unix system and port them into an MS Access table. This reading/porting is coming along just fine.
But writing the records into the Access table is proving to be time consuming. Apparently, Access slows down significantly as the number of fields in the Access table increases. I have run some time tests on the Access table and when the number of fields is small, say under 10, the speed is quick even with 100,000 plus records. But when the number of fields is bumped to 50 or 75, the speed significantly degrades. I am not sure what I can do about that but I can keep the extracting of the sub strings from the long strings down to as low a time as possible.
But writing the records into the Access table is proving to be time consuming. Apparently, Access slows down significantly as the number of fields in the Access table increases. I have run some time tests on the Access table and when the number of fields is small, say under 10, the speed is quick even with 100,000 plus records. But when the number of fields is bumped to 50 or 75, the speed significantly degrades. I am not sure what I can do about that but I can keep the extracting of the sub strings from the long strings down to as low a time as possible.
>>
As there will be over 100,000 long strings (records) that I will have to process, I want something that
is quick as opposed to counting where each value mark is in the long string and then using the MID function
<<
Instr is *much* faster than counting characters use instr to find the locators and then use Mid
Dim lngStart as long, lngEnd as long
lngStart = Instr(1, strBigStr, strStart)
lngEnd = instr(lngStart, strBigStr, strEnd)
strMid = Mid$(strBigStr, lngStart + 1, lngend - lngstart -1)
As there will be over 100,000 long strings (records) that I will have to process, I want something that
is quick as opposed to counting where each value mark is in the long string and then using the MID function
<<
Instr is *much* faster than counting characters use instr to find the locators and then use Mid
Dim lngStart as long, lngEnd as long
lngStart = Instr(1, strBigStr, strStart)
lngEnd = instr(lngStart, strBigStr, strEnd)
strMid = Mid$(strBigStr, lngStart + 1, lngend - lngstart -1)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use Instr in a loop to find the n'th value mark.
... Just like rspahitz gave you complete code for! Nice one rspahitz.
Yea...I didn't have a chance to test it, but it should be close.
XML is not going to help you with this problem E, I would suggest moving away from an Access database if at all possible. Access is the point of failure for many projects with a lot of data.
Good luck!
-D
Good luck!
-D
ASKER
Dave - I am not sure what other storage program to save the data in if I don't use Access. Any ideas? The app will be loaded onto laptops for the salesguys to use so SQL Server is out. Need something simple and easy to work with.
Thanks
Thanks
ASKER
My thanks to all for the help.
Dave, my direct email address on Mondays and Tuesdays is: Eric.Y@MackenzieWarehouse.
Dave, my direct email address on Mondays and Tuesdays is: Eric.Y@MackenzieWarehouse.
Thanks, EYoung.
Access is not necessarily bad, but will certainly be slow when dealing with lots of records.
I hope my solution is quick enough for your needs. And please properly test it before deploying it. There may be some problems with things like negative item number values, etc., so you may also want to add error trapping.
Access is not necessarily bad, but will certainly be slow when dealing with lots of records.
I hope my solution is quick enough for your needs. And please properly test it before deploying it. There may be some problems with things like negative item number values, etc., so you may also want to add error trapping.
I just tried this sendkeys on Win2000 and it seems very flaky. It seems to be rejecting every key sent!
P.S.
The "@echo" doesn't cause problems at the DOS command line.
P.S.
The "@echo" doesn't cause problems at the DOS command line.
(oops...sorry...wrong question)
ASKER
I have asked this Access question separately just now. Please see: https://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20145389
Thanks
Thanks
How about uploading everything into SQL Server and have access on the salespeoples laptops. you could then link the access DB to the SQL Server....
Just a thought...
Just a thought...
The easiest is to use the split command
arr = split(LongString, ",") where "," is your separator.
This is however the slowest method. Iterating through each character checking is much faster, particularly if you only want certain items. You may find that using instr is a bit faster but it will probably make your code much messier.
Zaphod.