Tables To CSV, ASP

Ok,

Here is my problem which is plaguing me, to a point where I don't know
where to start. My boss wants data pulled from a page that contains a
HTML table with values in that table, he wants the table to opened, and
parsed then stored into an access database for our own use. Now the
data is coming from a client site, understandably they don't want to
give us access to their DB (duh?)

What would the best method be to have the information from the page
parsed into variables and written, i condisdered dumping the source
into a variable and then remove the formatting but that seems to be a
very LONG task. Any suggestions.

Re: Tables To CSV, ASP

wrote in message
news:1138662848.511137.54370@z14g2000cwz.googlegroups.com...
> Ok,
>
> Here is my problem which is plaguing me, to a point where I don't know
> where to start. My boss wants data pulled from a page that contains a
> HTML table with values in that table, he wants the table to opened, and
> parsed then stored into an access database for our own use. Now the
> data is coming from a client site, understandably they don't want to
> give us access to their DB (duh?)
>
> What would the best method be to have the information from the page
> parsed into variables and written, i condisdered dumping the source
> into a variable and then remove the formatting but that seems to be a
> very LONG task. Any suggestions.
>

It may be a LONG task but if it's your only choice then ....

Use xmlhttp to fetch the URL source, parse it to extract the data,
then update an MS-Access database. This can be done via a
standalone VBS (or HTA); that is, ASP is not required.

Here's and example of the first two steps that extracts the copyright
phrase from the Home page of Google. Watch for word-wrap.

Option Explicit
'*
'* Declare Constants
'*
Const cVBS = "Google.vbs"
Const cURL = "http://www.google.com"
Const cTXT = "©"
'*
'* Declare Variables
'*
Dim intTXT
Dim strTXT
strTXT = 0
Dim strXML
'*
'* Fetch Page
'*
Dim objXML
Set objXML = CreateObject("Microsoft.XMLHTTP")
objXML.Open "GET", cURL, False
objXML.Send
strXML = objXML.ResponseText
Set objXML = Nothing
'*
'* Extract Text: "©2006 Google"
'*
intTXT = InStr(strXML,cTXT)
If intTXT > 0 Then
strTXT = Mid(strXML,intTXT)
intTXT = InStr(strTXT,"<")
strTXT = Left(strTXT,intTXT-1)
End If
'*
'* Finish
'*
WScript.Echo strTXT

Re: Tables To CSV, ASP

Yes it will be a long task, I have been scoping out this "phase" of
development for awhile and well, a 1 person team building a Intranet
for 10,000+ users has me at my ends. The code seems to be a good start
I definitaly owe you some!

Re: Tables To CSV, ASP

wrote in message
news:1138685968.972590.113340@z14g2000cwz.googlegroups.com.. .
> Yes it will be a long task, I have been scoping out this "phase" of
> development for awhile and well, a 1 person team building a Intranet
> for 10,000+ users has me at my ends. The code seems to be a good start
> I definitaly owe you some!
>

Here's an example of what's possible.

The City of Bellevue, Washington has this page:
http://www.cityofbellevue.org/page.asp?view=37722
which has a link to "Daily summary of reports and arrests" at:
http://www.cityofbellevue.org/page.asp?view=7863
which has links to monthly pages which contain links to
multiple daily pages which are actually MS-Word documents.

After downloading and parsing hundreds of files via an automated
process, I came up this page:
http://216.247.177.213/CityOfBellevue/Recaps.asp
which presents most of the same data in a very clear and concise way.
(Only the first nine months of 2005 are shown as I haven't rerun it.)

Thus, to paraphrase on old saying: "Anything is parse-able!".

Let us know if you run into any roadblocks. Good luck.

Re: Tables To CSV, ASP

> Thus, to paraphrase on old saying: "Anything is parse-able!".


That is SO wrong!!!!!

:)

Re: Tables To CSV, ASP

You help is very appreciated and i have taken a few steps to pull the
data. I see the source of web pages like google, and even pages of my
own.

Now the pages I am trying are https:// PHP pages, i am getting a
certificate validation error when sending the username and password we
have been given. Is this something i can get around. I don't work alot
with VBS other than simple functions. Now I used basically the script
you have above just to start playing with it so there aren't many
changes from there

Re: Tables To CSV, ASP

wrote in message
news:1138762194.740984.261030@z14g2000cwz.googlegroups.com.. .
> You help is very appreciated and i have taken a few steps to pull the
> data. I see the source of web pages like google, and even pages of my
> own.
>
> Now the pages I am trying are https:// PHP pages, i am getting a
> certificate validation error when sending the username and password we
> have been given. Is this something i can get around. I don't work alot
> with VBS other than simple functions. Now I used basically the script
> you have above just to start playing with it so there aren't many
> changes from there
>

For https:// pages try changing
objXML.Open "GET", cURL, False
to
objXML.Open "GET", cURL, False, cUSR, cPWD
where
cUSR and cPWD are constants as in:
Const cUSR = "username"
Const cPWD = "password"

Re: Tables To CSV, ASP

That was my first though, now I am recieving a permission denied error
message from the objXML.Open line. I verified my username and password
works and i can browse the site with no problems. The username and
password const are assigned correctly and case sensitive surrounded by
" ".

Set objXML = CreateObject("Microsoft.XMLHTTP")
objXML.Open "GET", cURL, false, cUser, CPWD
objXML.Send
strXML = objXML.ResponseText

Re: Tables To CSV, ASP

wrote in message
news:1138825171.726524.198810@z14g2000cwz.googlegroups.com.. .
> That was my first though, now I am recieving a permission denied error
> message from the objXML.Open line. I verified my username and password
> works and i can browse the site with no problems. The username and
> password const are assigned correctly and case sensitive surrounded by
> " ".
>
> Set objXML = CreateObject("Microsoft.XMLHTTP")
> objXML.Open "GET", cURL, false, cUser, CPWD
> objXML.Send
> strXML = objXML.ResponseText
>

Don't know what to tell you; it worked for me.

Re: Tables To CSV, ASP

I have gotten a few steps forward i believe, now i am starting to think
this maybe an issue in my IIS security. I typically develope from a
remote computer and not go near my webserver. I tried running that
script from the server and it worked perfectly. However i get a
permission denied error.

This could cause a problem in the long run as this script will be used
by users from many different locations. I know have the great task of
parsing it line by line.

Re: Tables To CSV, ASP

I also sort of "cheated" in displaying back the source code. Is this
the best way or should i be writing a loop that converts the ",&,>,<
characters.

document.write "

Re: Tables To CSV, ASP

wrote in message
news:1138833136.906164.50870@o13g2000cwo.googlegroups.com...
> I also sort of "cheated" in displaying back the source code. Is this
> the best way or should i be writing a loop that converts the ",&,>,<
> characters.
>
> document.write "

Re: Tables To CSV, ASP

I still have alot of error's and ALOT of mind knumbin issues, including
saving the html to a text file to get over but without your help i
couldn't do this.

For some reason i can't get it to output to text. Here is my entire
code, and yes it is similar to yours. I don't really need it to replace
the variables as I need to parse it in very specific row's and col's
however.. It still seems impossible, there are 15 different reports
that i need this to attack. here is my code, can you tell me why i get
an error "ActiveX component cannot create object .

I am using the FileStream object that i have used alot in ASP.NET, but
(and i don't use much VBS) will not work.

Const cURL = "url" '*Url to parse
Const cTXT = "<"
Const cTXT2 = ">"
Const cUser = "USER" '*Valid User
Const cPWD = "PASS" '*Valid Pass
Dim strXML
Dim objXML
Set objXML = CreateObject("Microsoft.XMLHTTP")
objXML.Open "GET", cURL, False, cUser, cPWD
objXML.Send
strXML = objXML.ResponseText
Dim strings
strings = strxml
'*document.write "

Re: Tables To CSV, ASP

i know i, the Dim tf and the tfs don't match but they have both ran as
equal variables andstill the problem, i relize this isn't ure issue.
But i really appreciate the help. To be honest i have resorted to Excel
Web Queries of the data, and to be honest that is not an efficient way

Re: Tables To CSV, ASP

wrote in message
news:1138869011.383051.246980@f14g2000cwb.googlegroups.com.. .
> I still have alot of error's and ALOT of mind knumbin issues, including
> saving the html to a text file to get over but without your help i
> couldn't do this.
>
> For some reason i can't get it to output to text. Here is my entire
> code, and yes it is similar to yours. I don't really need it to replace
> the variables as I need to parse it in very specific row's and col's
> however.. It still seems impossible, there are 15 different reports
> that i need this to attack. here is my code, can you tell me why i get
> an error "ActiveX component cannot create object .
>
> I am using the FileStream object that i have used alot in ASP.NET, but
> (and i don't use much VBS) will not work.
>
> Const cURL = "url" '*Url to parse
> Const cTXT = "<"
> Const cTXT2 = ">"
> Const cUser = "USER" '*Valid User
> Const cPWD = "PASS" '*Valid Pass
> Dim strXML
> Dim objXML
> Set objXML = CreateObject("Microsoft.XMLHTTP")
> objXML.Open "GET", cURL, False, cUser, cPWD
> objXML.Send
> strXML = objXML.ResponseText
> Dim strings
> strings = strxml
> '*document.write "

Re: Tables To CSV, ASP

Well with little luck (and little hair left) I am still getting many
errors, when running the above code I get the validations for the SSL
Certificates, we pass the login fine howerver i get the following
error.

Error : ActiveX Component Can't Create Object :
"Scripting.FileSystemObject"

Any clues anyone.. Or if anyone has a great way to parse a non-local,
HTTPS, PHP page and then parse that to remove all the tables and extra
formatting to remove the raw data, then turn around and write it to an
Access Database i would be forever indebted to you

Re: Tables To CSV, ASP

wrote in message
news:1139281960.062069.96850@g14g2000cwa.googlegroups.com...
> Well with little luck (and little hair left) I am still getting many
> errors, when running the above code I get the validations for the SSL
> Certificates, we pass the login fine howerver i get the following
> error.
>
> Error : ActiveX Component Can't Create Object :
> "Scripting.FileSystemObject"
>
> Any clues anyone.. Or if anyone has a great way to parse a non-local,
> HTTPS, PHP page and then parse that to remove all the tables and extra
> formatting to remove the raw data, then turn around and write it to an
> Access Database i would be forever indebted to you
>

Are you using an (IE-only) Web page or a standalone VBS file?

You code parse the URL source directly into a database table;
thus, you wouldn't need to use the "FileSystemObject".

Post you code (after changing the values of cURL, cUSR, and cPWD).

Re: Tables To CSV, ASP

McKirahan,

Thank you for all your help i finally finished the application. Took
alot of work and alot of tears but without your coding, and everyone
else in this forum i have finised it.