Parsing Data From A Website Into Access

[Jim Mac Millan]

Hi,

I'm not sure if this is a good place for this question!? Since it will
ultimately end up in Access I figured I would start here.

Every week I pull about 20 records from an online prospecting services
website. The service does not offer the ability to download data in any kind
of file format. So I highlight the page and save it as a text file. From
there I paste the pieces of information into the appropriate fields in my
Access database.

I think my question is this; Is there anyway I can have Access parse the
data into the appropriate fields? Below I have copied a page from the site
with the data changed to protect the innocent.

Thanks For Any Input
Jim Mac Millan

/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
Widget Inc Llc
Physical Address
123 Anywhere St
My City, AZ 12345-7890 (Map)Phone: 123-4567-123


Key Information
D-U-N-S Number 987654321
Doing Business As "Widget Makers" "Assi Security Of Arizona"

Line of Business BIG AND SMALL WIDGET CONTRACTOR
Branch Status Has No Branch Locations

Key Numbers
Annual Sales (mil.) $4.5
Total Employees 13
Employees At This Location 5

Key Person
President John E Doe

SIC Code
5065 Electronic parts and equipment

NAICS Code
423690 Other Electronic Parts and Equipment Merchant Wholesalers

 

[Guest]

I would use Excel as intermediate processing.
Paste each record in a different tab. Then have a processing tab. Next use
a tab to pull the processed data together. Copy and Paste Special - Values
on final tab.
Link this tab in Access.

To re-use just delete data from the records tabs. The processin tab needs
an IF function for when there is no data to process.

 

[Jim Mac Millan]

Karl,

I'm not grasping what you are saying. Could you send me a small sample
(Excel file) that might give me an idea how to move forward?

Thanks
Jim
(e-mail address removed) turn the extension around to bypass the spam

 

[Albert D.Kallal]

I wrote one that parse out the internet movie database.

The code was not that hard to write. The trick is to build a routines that
you pass some text, and it returns the data *after* that.

You do need to very fluent in VB to write such code...but it is not hard....

eg:

dim rst as dao.ReocrdSet
dim str as string
.....code here to load up array from text file

then...

set rst = currentdb.OpenReocrdSet("tblSICdata")

rstAdd.new

rst!DunsNum = findtag("D-U-N-S Number ") ' note the extra space
after number
str = findtag("Doing Business As ")
rst!BusinessName = split(str,chr(34))(1) ' remove the " around
business name

rst!LineOfBus = findtag("Line of Business ")

etc.

I also had a routine to move to a particular line..such as

iPtr = moveTag("Key Person")
iPtr = iPtr + 1
rst!KeyPerson = vbuf(iptr)

iPtr = moveTag("SIC Code")
iPtr = iPtr = 1
rst!SIC = split(vbuf(iPtr)," ")(0) ' get number up to first space
rst!SICDescripton = mid(vbuf(iPtr), instr(vbuf(iPtr," ") ) ' get
description after space

So, you can see in the above how I parsed out the sic code into the number
part..and the string part....

The two code routines above I used are repeated below (they may not be
suitable for your use..but you can see how cobbling together just a FEW
routines to help you parse will go a long way....


Public Function findTag(strFind As String) As Variant

Dim i As Integer

findTag = Null

For i = 0 To UBound(vBuf, 1)

If InStr(vBuf(i), strFind) > 0 Then
findTag = Split(vBuf(i), strFind)(1)
Exit Function
End If
Next i


End Function

Public Function moveTag(strFind As String) As Integer

Dim i As Integer

For i = 0 To UBound(vBuf, 1)

If InStr(vBuf(i), strFind) > 0 Then
moveTag = i
Exit Function
End If
Next i


End Function


In fact, I find my self writing all kind of parsing stuff...