Best Way to Search a String?

hcl-bot · July 12, 2012, 3:22pm

I am scraping some data off of a web page and storing it into a string. That part is working great. Now I want to “navigate” the string (using Lotus Script) and pull out specific pieces of data. I planned to use functions such as strRight and Left$ to do this however it’s a lot more cumbersome than I expected. Before I continue down this road further, I thought I’d ask more seasoned developers if they could suggest a better way.

For example, for each company listed, I am trying to grab the NAIC #, the Underwriting Limitation amount and licensed states from the sample data below. I’m able to jump to the NAIC number using StrRight and grab the data to the right of it but that is where everything gets messy. I’m not sure how to continue on from that point and get the next bit of information (underwriting limitation).

I may be way off in my approach, so any suggestions would be greatly appreciated. Thanks!

ACCREDITED SURETY AND CASUALTY COMPANY, INC. (NAIC #26379)

BUSINESS ADDRESS: PO Box 140855, Orlando, FL 32814 - 0855. PHONE: (407) 629-2131. UNDERWRITING LIMITATION b/: $1,641,000. SURETY LICENSES c,f/: AL, AK, AZ, AR, CA, CO, CT, DE, DC, FL, GA, HI, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY. INCORPORATED IN: Florida.

ACSTAR INSURANCE COMPANY (NAIC #22950)

BUSINESS ADDRESS: P.O. BOX 2350, NEW BRITAIN, CT 06050 - 2350. PHONE: (860) 224-2000. UNDERWRITING LIMITATION b/: $2,848,000. SURETY LICENSES c,f/: AL, AK, AZ, AR, CA, CO, CT, DE, DC, FL, GA, HI, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, PR, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY. INCORPORATED IN: Illinois.

Aegis Security Insurance Company (NAIC #33898)

BUSINESS ADDRESS: P.O. Box 3153, Harrisburg, PA 17105. PHONE: (717) 657-9671. UNDERWRITING LIMITATION b/: $4,353,000. SURETY LICENSES c,f/: AL, AK, AZ, AR, CA, CO, CT, DE, DC, FL, GA, HI, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY. INCORPORATED IN: Pennsylvania.

ALL AMERICA INSURANCE COMPANY (NAIC #20222)

BUSINESS ADDRESS: P.O. BOX 351, VAN WERT, OH 45891 - 0351. PHONE: (419) 238-1010. UNDERWRITING LIMITATION b/: $10,726,000. SURETY LICENSES c,f/: AZ, CA, CT, GA, IL, IN, IA, KY, MA, MI, NV, NJ, NY, NC, OH, OK, TN, TX, VA. INCORPORATED IN: Ohio.

Allegheny Casualty Company (NAIC #13285)

BUSINESS ADDRESS: One Newark Center, 20th Floor, Newark, NJ 07102. PHONE: (800) 333-4167 x-269. UNDERWRITING LIMITATION b/: $1,851,000. SURETY LICENSES c,f/: AL, AK, AS, AZ, AR, CA, CO, CT, DE, DC, FL, GA, HI, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, PR, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY. INCORPORATED IN: Pennsylvania.

hcl-bot · July 12, 2012, 9:51pm

Subject: Here’s one way

Someone might suggest using regular expressions, but I would just use Instr to find the location of "UNDERWRITING LIMITATION b/: ", then add the length of that string to the value returned by Instr. That gives you the position of the dollar sign. Use Instr again starting from there and looking for “.”. Use the Mid function to extract the substring between those two points, that gives you the dollar value. Continue in the same vein to look for "SURETY LICENSES c,f/: " and "INCORPORATED IN: "

hcl-bot · July 13, 2012, 10:38am

Subject: Sounds good

Thanks Wayne, I’ll give it a shot and report back with results and code.

hcl-bot · July 19, 2012, 4:00pm

Subject: This is how I would do it…

I would solve this by creating a class, say WebData.

The class will contain the following methods/properties:

Public WebItem List As String

Function GetNAIC(webdatastring as string) as String

Will use the method Wayne describe to get the NAIC code from the string:

startpos = instr(webdatastring,“(NAIC #”)+7

endpos = instr(startpis,webdatastring,“)”

NAIC = Mid$(webdatastring, startpos, endpos-startpos)

Function AddItem(webdatastring as string)

Will take the webdatastring, pass it to GetNAIC() to get the NAIC code, and and add it to the list webitem with the NAIC code as the list tag:

WebItem(NAIC) = webdatastring

You then add additional functions to get the company name, license state and amounts. All that is pretty easy, the way the data is stored.

In your main code, you split the string with data into an array:

Dim arrayWebString as Variant ’ One of the few times it is OK with a variant

Dim webData as New WebData()

arrayWebString = Split(webString,“

”)

ForAll arr in arrayWebString

Call webData.AddItem(arr)

End Forall

Now you can do whatever you like with the data in the object/class.