Couple questions

[korrowan]

On-line help then?

I will scour the web for a help file or a manual but I am sure the city would be annoyed that I am trying to screen scrape their mainframe lol and would likely not be much help.

On March 1st I may be able to get that help from them as that is when our bid will either be rejected or a contract given and we will have the necessary contacts to then proceed in getting their help (or even better getting data feeds). Currently we are contracted by a lawfirm to do the same work but have limited access to the Cities IT staff since we work for a 3rd party.

[adroege]

http://www.jollygiant.com/support/faqs. ... he_program

check out this link


Look especially at the topic titled "Automation"


You also said you know VBScript..... remember Macro Scheduler does VBScript as well

Just put it between tags like so:

VBStart

' vb code goes here

VBEnd

[korrowan]

Thanks for that link! It looks like that little piece of software is much more robust than I expected. In the past I was a user and had no time to deal with it but with our downtime I am sure I can get it to do what I need!

I guess the only thing that I will have to decide is the best way to read/write since I cannot read/write from my database.

[korrowan]

Another question. I have another way to access the mainframe using IE for some of the data and from this website http://www.phila.gov/revenue/RealEstateTax/.

Would using web recorder be a better option to screen scrape that data?? If so how does one pull the data from the correct spot?

For instance what I would need this to do is load the page. Put in the BRT Number (second box for instance 021317100) from a table and pull the total and how many liens there are (any years that are not the current year that have a balance) and put that into my table. Is this possible? I would assume I would open the table, create a variable (BRT) and loop through the table and execute the screen scrape? Then pass the data back? Also are there any good examples of this.. I have looked for the last hour and have yet to find one that corresponds well to my situation..then again there are tons of posts.

Thanks.

[adroege]

Yes, I think you should download the eval version of WebRecorder and try it. There are examples (I think) inside the help after you install it.

Getting the data back can be done several ways. Many times you will know the particular "tag" by name or it's ID (tag is another way to say data field in this case) ...and this of course id done by inspecting the HTML which is returned after your query.

If this is not appropriate, you might have to use Regx (regular expressions) to parse apart the resulting HTML, for example to return multiple rows.

Some might suggest image recognition with a combination of cut/paste -- however I don't believe this is a reliable method and encourage it's use only as a last resort.

[korrowan]

Yes, I think you should download the eval version of WebRecorder and try it. There are examples (I think) inside the help after you install it.

Getting the data back can be done several ways. Many times you will know the particular "tag" by name or it's ID (tag is another way to say data field in this case) ...and this of course id done by inspecting the HTML which is returned after your query.

If this is not appropriate, you might have to use Regx (regular expressions) to parse apart the resulting HTML, for example to return multiple rows.

Some might suggest image recognition with a combination of cut/paste -- however I don't believe this is a reliable method and encourage it's use only as a last resort.

Okay cool I ll give it a shot. The thing about using tags though is that if a particular BRT has multiple years due it will change the look of the page. For example a tax account can be delinquent till 1978 (which happens far more than it should in Philadelphia). So is it possible to use tags if the table that is displayed can include a random number of rows?? This should be REALLY easy for the amount due but to check for number of delinquent tax years seems much more complex.

Thanks again for the the help!

[adroege]

Yes, you are correct.

That is why the ability to parse apart the HTML using Regx (or some other method) is going to be the most helpful, I think.

[adroege]

This example should help you out. It will parse the HTML apart into individual string variables

Code: Select all

//Parse HTML

Let>MSG_WIDTH=300
Let>MSG_HEIGHT=300

//Using WebRecorder or some other method
//Enter the BRT number and submit the form
//Then capture the resulting HTML response to
//a file or a string variable

//If the response is in a file -- read it in
ReadFile>C:\Documents and Settings\Computer User\Desktop\myinput.html,www_page

//Grab the table
Let>pattern=<table class="grdRecords"
Position>pattern,www_page,1,pos_start_table
Let>pattern=</table>
Position>pattern,www_page,pos_start_table,pos_end_table
Add>pos_end_table,7
MidStr>www_page,pos_start_table,pos_end_table,h_table

//Get row matches per year
Let>pattern=<td>\d\d\d\d</td><td>.*?</td><td>.*?</td><td>.*?</td><td>.*?</td><td>.*?</td><td>.*?</td>
RegEx>pattern,h_table,0,year,num_match,0,,
Let>count=num_match

If>count=0,None_found

Let>x=1
Let>VAREXPLICIT=0

Repeat>x
  Let>myYEAR=YEAR_%x%
  //replace HTML tags with pipe
  StringReplace>myYEAR,<td>,|,myYEAR
  Let>YEAR_%x%=myYEAR
  //replace HTML tags with pipe
  StringReplace>myYEAR,</td>,|,myYEAR
  Let>YEAR_%x%=myYEAR
  //replace double pipes with single pipe
  StringReplace>myYEAR,||,|,myYEAR
  Let>YEAR_%x%=myYEAR
  Length>YEAR_%x%,len
  //Get rid of pipe in position 1
  MidStr>myYEAR,2,len,myYEAR
  Let>YEAR_%x%=myYEAR
  Length>YEAR_%x%,len
  Sub>len,1
  //Get rid of pipe as last char of string
  MidStr>myYEAR,1,len,myYEAR
  Let>YEAR_%x%=myYEAR
  Add>x,1
Until>x>count

//Show row 1 if it exists
MessageModal>Here is Year 1 data %YEAR_1%

//Break the pipe delimited string into separate variables
Let>x=1
Repeat>x
  Let>myYEAR=YEAR_%x%
  Separate>myYEAR,|,Data
   MessageModal>Year=%DATA_1%%CRLF%Principle=%DATA_2%%CRLF%Interest=%DATA_3%%CRLF%Penalty=%DATA_4%%CRLF%Other=%DATA_5%%CRLF%Total=%DATA_6%%CRLF%Lien#=%DATA_7%
  Add>x,1
Until>x>count

Label>None_found