Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Please Help, Excel extraction and paste to Website - Loop
#1
I'm having a difficult time figuring out how to do the below task. Here is the scenario.

I have an database in Excel 'sheet1' consisting of properties and property contacts. Each row contains a property and related contacts.

1. I need to extract their "lastname" "firstname" "city" "state" & maybe "zipcode" from Excel, find their contact information via Anywho.com,

2. then paste the one or many results (i.e. name, address, and phone number) along with the corresponding Property "PIN", in a xls sheet2 same workbook.
2.5 there may be one or more than one related contact for any one property, all property related contacts are on the same row.

3.Needs to run until it reaches the last property PIN.

Example (xls sheet1)
PIN lastname firstname city state zip lastname2 firstname2 city2
1212123123 Doe John Chicago IL 60601 Smith James Plainfield


I do appreciate any help, thank you.

__________________
Respectfully,

Snwskier2
#2
This macro does what you need, except:
1. gets only the first contact from a row.
2. writes each result in separate row.

Open anywho.com in Internet Explorer and run this macro. Can be home page or results page.

This macro does not depend on active window. You can work with other programs while it is running. Just don't touch that IE window.

This macro runs in separate process, as User. Otherwise would not work on Vista/7. When QM asks where to save exe settings, choose in folder.

Macro Excel and anywho.com
Code:
Copy      Help
;/exe 1
out

;define variable type for search results, and create variable that will be populated by anywho_find
type ANYWHORESULT ~lastName ~firstName ~city ~state ~_zip
ARRAY(ANYWHORESULT) ar

;connect to Excel
ExcelSheet es.Init("Sheet1")
ExcelSheet es2.Init("Sheet2")

;get Sheet1 into array a
ARRAY(str) a
es.GetCells(a)

;for each row
int row col row2(2) i
for row 1 a.len ;;change 1 to 0 if need to include first row
,;out "-------------"
,;for(col 0 a.len(1)) out a[col row]
,
,;searches for the first contact. Would need some more code to repeat for all contacts in row.
,anywho_find ar a[1 row] a[2 row] a[3 row] a[4 row] a[5 row]
,
,;now all results are in ar. Write to Sheet2. Writes each result in separate row.
,for i 0 ar.len
,,es2.SetCell(a[0 row] 1 row2) ;;PIN
,,ANYWHORESULT& r=ar[i]
,,es2.SetCell(r.lastName 2 row2)
,,es2.SetCell(r.firstName 3 row2)
,,es2.SetCell(r.city 4 row2)
,,es2.SetCell(r.state 5 row2)
,,es2.SetCell(r._zip 6 row2)
,,row2+1

out "Finished"

Function anywho_find
Code:
Copy      Help
;/Excel and anywho.com
function ARRAY(ANYWHORESULT)&ar $lastName $firstName $city $state $_zip

;Finds a person in anywho.com.
;Stores all results in ar.
;anywho.com must be displayed in Internet Explorer. Can be home page or results page.


out "Searching for %s %s..." firstName lastName

ar=0
int w1=win("White Pages on AnyWho - Windows Internet Explorer" "IEFrame")

;find all form elements
Htm elLN=htm("INPUT" "qn" "" w1 0 13 0x221)
Htm elFN=htm("INPUT" "qf" "" w1 0 14 0x221)
Htm elC=htm("INPUT" "qc" "" w1 0 16 0x221)
Htm elS=htm("SELECT" "qs" "" w1 0 2 0x221)
Htm elZ=htm("INPUT" "qz" "" w1 0 17 0x221)
Htm elFIND=htm("INPUT" "btnsubmit" "" win("White Pages on AnyWho - Windows Internet Explorer" "IEFrame") 0 18 0x221)

;set text of form elements
elFN.SetText(firstName)
elLN.SetText(lastName)
elC.SetText(city)
if(empty(state)) state="Select"
elS.CbSelect(state)
elZ.SetText(_zip)

;submit
elFIND.Click

;g1
;wait for results
wait 0 I ;;wait while IE is busy
Htm elDIS=htm("A" "DISCLAIMER" "" w1 0 17 0x21 60) ;;and then wait for DISCLAIMER link

;navigate from DISCLAIMER to the first result table
Acc ad=acc(elDIS)
Acc ax at
ad.Navigate("parent next" at)

;enumerate results
rep
,at.Navigate("first3" ax) ;;table -> name
,if(ax.Role!=ROLE_SYSTEM_LINK) break ;;Found ...
,
,ANYWHORESULT& r=ar[] ;;add new element to the results array
,str s=ax.Name
,;split full name into first and last name
,;Note that not always will be correct, because full name can be eg "John J Doe", and then first name will be "John J".
,int i=findcr(s ' '); if(i>=0) r.firstName.left(s i); r.lastName=s+i+1; else r.lastName=s
,;city, state, zip
,ax.Navigate("next2")
,s=ax.Name
,;out s
,ARRAY(str) as
,findrx(s "(.+), (\w\w) ?(\d+)?" 0 0 as)
,r.city=as[1]
,r.state=as[2]
,r._zip=as[3]
,
,at.Navigate("next"); err break ;;next result table

;more pages?
Htm elNext=htm("A" "Next" "<A class=L6 href=''/results.php*" win("White Pages on AnyWho - Windows Internet Explorer" "IEFrame") 0 15 0x5)
if(!elNext) ret
;click Next
elNext.Click
;go to the beginning
goto g1
#3
Thank you very much
#4
I started working on this but had quit. In the meantime Gint gave an example that works.

My first thoughts on this was to not open up the browser and use a string format to send a url request directly to the anywho PHP server side program and then parsing out the html returned all with QM.

Here's what I got:
It crashes under certain instances...like mispellings and things. I'm sure Gint could whip up a better way of extracting information from the anywho html results. Works pretty good otherwise Smile
Function AnyWho_Lookup
Code:
Copy      Help
function ARRAY(str)&ReturnInformation [str'First] [str'Last] [str'State] [str'City] [str'Zip]

str AnyWhoURL.format("http://whitepages.anywho.com/results.php?&qc=%s&qf=%s&qn=%s&qs=%s&qz=%s" City First Last State Zip)

HtmlDoc d.InitFromWeb(AnyWhoURL)
str AnyWhoHTML=d.GetHtml
err
,str& Entry=ReturnInformation[];Entry.from("No Matches")
,ret

ARRAY(str) a ExactMatch Information
d.GetTable(12 a)
str Match=a
ARRAY(str) NumberOfResults

findrx(Match "Found (\d+)" 0 0 NumberOfResults)
int NumberMatch=val(NumberOfResults[1])
err
,d.GetTable(13 ExactMatch)
,tok(ExactMatch[0] Information -1 "[]" 16)
,;out Information[0]
,err
,,&Entry=ReturnInformation[];Entry.from("No Matches")
,,ret
,&Entry=ReturnInformation[];Entry.from(Information[0])
,&Entry=ReturnInformation[];Entry.from(Information[1])
,&Entry=ReturnInformation[];Entry.from(Information[2])
,&Entry=ReturnInformation[];Entry.from(Information[3])
,ret

tok(Match Information -1 "[]" 16)
int i=3
if NumberMatch<10
,int RepNumber=NumberMatch
else
,RepNumber=10
rep RepNumber
,&Entry=ReturnInformation[];Entry.from(Information[i])
,&Entry=ReturnInformation[];Entry.from(Information[i+1])
,&Entry=ReturnInformation[];Entry.from(Information[i+2])
,&Entry=ReturnInformation[];Entry.from(Information[i+3])
,&Entry=ReturnInformation[];Entry.from("-------")
,i+10

Function QM_411
Code:
Copy      Help
\Dialog_Editor
function# hDlg message wParam lParam
if(hDlg) goto messages

;BEGIN DIALOG
;0 "" 0x90C80AC8 0x0 0 0 179 317 "QM 4-1-1"
;3 Edit 0x54030080 0x200 9 9 96 14 ""
;4 Edit 0x54030080 0x200 9 27 96 14 ""
;5 Edit 0x54030080 0x200 9 45 96 14 ""
;6 Edit 0x54030080 0x200 9 63 96 14 ""
;7 Edit 0x54030080 0x200 9 81 96 14 ""
;8 Static 0x54000000 0x0 108 12 48 13 "First"
;9 Static 0x54000000 0x0 108 30 48 12 "Last"
;10 Static 0x54000000 0x0 108 48 48 12 "City"
;11 Static 0x54000000 0x0 108 66 48 12 "State"
;12 Static 0x54000000 0x0 108 84 48 13 "Zip"
;17 Button 0x54032000 0x0 9 99 72 21 "Get Information"
;13 Edit 0x54231044 0x200 9 123 148 183 ""
;END DIALOG
;DIALOG EDITOR: "" 0x2030105 "" "" ""
str controls = "3 4 5 6 7"
str e3 e4 e5 e6 e7
if(!ShowDialog("QM_411" &QM_411 &controls)) ret

ret
;messages
sel message
,case WM_INITDIALOG
,case WM_DESTROY
,case WM_COMMAND goto messages2
ret
;messages2
sel wParam
,case 17
,str First.getwintext(id(3 win("QM 4-1-1" "#32770")))
,str Last.getwintext(id(4 win("QM 4-1-1" "#32770")))
,str State.getwintext(id(6 win("QM 4-1-1" "#32770")))
,str City.getwintext(id(5 win("QM 4-1-1" "#32770")))
,str Zip.getwintext(id(7 win("QM 4-1-1" "#32770")))
,ARRAY(str) Information
,AnyWho_Lookup Information First Last State City Zip

,str sInformation=Information
,sInformation.setwintext(id(13 win("QM 4-1-1" "#32770")))
,
,case IDOK
,case IDCANCEL
ret 1

Some work would still be needed to write to excel file.

Jimmy Vig
#5
Your function works well but gets max 10 results. Add &qi=0&qk=100 and it will get max 100 results.
Function AnyWho_Lookup
Code:
Copy      Help
function ARRAY(str)&ReturnInformation [str'First] [str'Last] [str'State] [str'City] [str'Zip]

str AnyWhoURL.format("http://whitepages.anywho.com/results.php?&qc=%s&qf=%s&qn=%s&qs=%s&qz=%s&qi=0&qk=100" City First Last State Zip)

HtmlDoc d.InitFromWeb(AnyWhoURL)
str AnyWhoHTML=d.GetHtml
err
,str& Entry=ReturnInformation[];Entry.from("No Matches")
,ret

ARRAY(str) a ExactMatch Information
d.GetTable(12 a)
str Match=a
ARRAY(str) NumberOfResults

findrx(Match "Found (\d+)" 0 0 NumberOfResults)
int NumberMatch=val(NumberOfResults[1])
err
,d.GetTable(13 ExactMatch)
,tok(ExactMatch[0] Information -1 "[]" 16)
,;out Information[0]
,err
,,&Entry=ReturnInformation[];Entry.from("No Matches")
,,ret
,&Entry=ReturnInformation[];Entry.from(Information[0])
,&Entry=ReturnInformation[];Entry.from(Information[1])
,&Entry=ReturnInformation[];Entry.from(Information[2])
,&Entry=ReturnInformation[];Entry.from(Information[3])
,ret

tok(Match Information -1 "[]" 16)
int i=3
if NumberMatch<100
,int RepNumber=NumberMatch
else
,RepNumber=100
rep RepNumber
,&Entry=ReturnInformation[];Entry.from(Information[i])
,&Entry=ReturnInformation[];Entry.from(Information[i+1])
,&Entry=ReturnInformation[];Entry.from(Information[i+2])
,&Entry=ReturnInformation[];Entry.from(Information[i+3])
,&Entry=ReturnInformation[];Entry.from("-------")
,i+10
#6
Is there a better way you can see to get the data out of the html.

I'm not very good with using the HTML functions in QM...I mean I figure out ways, but it always feels like there is a simple way.

Thanks,
jimmy Vig
#7
HtmlDoc is the best way in this case. With simpler pages can be used findrx.
#8
Like using

<td class="nameAndAddress">
<table class="resultTable">

stuff like that?
#9
Your function uses HtmlDoc, and it is the best way. When it does not work?
#10
I'm talking about using specific classes and divs in the HTML using html doc.

The container tags...and indexes. I never seem to have any luck.

I guess I would like it to work like XML where I can just navigate in the HTML using an path.
#11
Here you cannot use path.
Parse whole document into smaller parts and look which part contains element with certain attributes.
For example

Macro Macro633
Code:
Copy      Help
out
str First("Bill") Last("Gates") City State Zip

str AnyWhoURL.format("http://whitepages.anywho.com/results.php?&qc=%s&qf=%s&qn=%s&qs=%s&qz=%s" City First Last State Zip)
HtmlDoc d.InitFromWeb(AnyWhoURL)

ARRAY(str) a
d.GetTable(12 a 0 1)
;
int j
for j 0 a.len
,out a[j]
,out "-------"
ret

Now in every a[j] search for "<TD class=nameAndAddress>"...
#12
This is definitely a lot more stable.

Function AnyWho_Lookup2
Code:
Copy      Help
function ARRAY(str)&ReturnInformation [str'First] [str'Last] [str'State] [str'City] [str'Zip]

str AnyWhoURL.format("http://whitepages.anywho.com/results.php?&qc=%s&qf=%s&qn=%s&qs=%s&qz=%s&qi=0&qk=100" City First Last State Zip)
HtmlDoc d.InitFromWeb(AnyWhoURL)

ARRAY(str) a Entry Information
d.GetTable(12 a 0 1)
int j
for j 0 a.len
,if(matchw(a[j] "<TABLE class=resultTable*"))
,,goto Populate
,if(matchw(a[j] "*<SPAN class=singleName*"))
,,goto Populate
,,;Populate
,,int flag=1
,,d.InitFromText(a[j])
,,d.GetTable(0 Entry)
,,tok(Entry[0] Information 4 "[]" 16)
,,str& entry=ReturnInformation[];entry.from(Information[0])
,,&entry=ReturnInformation[];entry.from(Information[1])
,,&entry=ReturnInformation[];entry.from(Information[2])
,,&entry=ReturnInformation[];entry.from(Information[3])
,,&entry=ReturnInformation[];entry.from("----------")

if flag=1;ret
_s=Information;_i=val(_s);if _i=0;out "No Matches";ret

ret

Is there anything else you could recommend to do to make this code better? It's looking pretty short now Smile
Thanks,
jimmy Vig
#13
Code:
Copy      Help
,,str& entry=ReturnInformation[];entry.from(Information[0])
,,&entry=ReturnInformation[];entry.from(Information[1])
,,&entry=ReturnInformation[];entry.from(Information[2])
,,&entry=ReturnInformation[];entry.from(Information[3])


could be
Code:
Copy      Help
for _i 0 4
,,str& entry=ReturnInformation[];entry.from(Information[_i])


Is there anyway to clean this up?

Code:
Copy      Help
if(matchw(a[j] "<TABLE class=resultTable*"))
,,goto Populate
,if(matchw(a[j] "*<SPAN class=singleName*"))
,,goto Populate
#14
does this help

Function Function8
Code:
Copy      Help
if(matchw(a[j] "<TABLE class=resultTable*") or matchw(a[j] "*<SPAN class=singleName*"))
,int flag = 1
,
if(flag = 1)
,d.InitFromText(a[j])
,d.GetTable(0 Entry)
,tok(Entry[0] Information 4 "[]" 16)
,str& entry=ReturnInformation[];entry.from(Information[0])
,&entry=ReturnInformation[];entry.from(Information[1])
,&entry=ReturnInformation[];entry.from(Information[2])
,&entry=ReturnInformation[];entry.from(Information[3])
,&entry=ReturnInformation[];entry.from("----------")
else
,_s=Information;_i=val(_s);if _i=0;out "No Matches"

ret
#15
Guess I should have asked this question for the get go, but how do I go about getting the code to compile. The 'anywho_find' keeps coming up as unknown.

Also, just now when typing the above reply my lcase 'p' stopped working. I was running QuickMacro, closed it down and the lcase 'p' started to work again.
Any idea why this happened?
#16
anywho_find must be function, not macro. If you have macro anywho_find, delete it. Then create new function anywho_find, and paste the code there.

---

look for p trigger assigned to a macro.
#17
Hi Gintaras,

I'm still having issues with the program unable to recognize macro 'Excel and anywho.com', is it because I'm evaluating the product have on yet registered?
I like what I've seen so far, but it seems fairly complicated for me, as most of my knowledge is within vba code and all self taught.

Thanks again and hope all is well.
#18
Create macro Excel and anywho.com and function anywho_find. For more help, click the small ? above code here in forum (Macro Excel and anywho.com ?).
#19
Oh boy do feel dumb now, thanks again.


Forum Jump:


Users browsing this thread: 1 Guest(s)