• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Website Data From Google Maps and Tables

Hi !

3 points :

• check and correct your first link (click on it !) …

• « Various tables » ? There is no table as you can see
if you ever use the webbrowser inspection tool ! So explain …

• As we can't guess, explain too what to extract and
how should be the worksheet layout !
Where is in your post your desired result workbook ?!
​
 
Marc L said:
Hi !

3 points :

• check and correct your first link (click on it !) …

• « Various tables » ? There is no table as you can see
if you ever use the webbrowser inspection tool ! So explain …

• As we can't guess, explain too what to extract and
how should be the worksheet layout !
Where is in your post your desired result workbook ?!
​
Click to expand...
Hi Marc,

Thank you for the reply! Sorry for the lack of attention. I'll double check next time. I can't edit the thread, could you modify it if please?

These are the correct links:
1. http://www.reidomate.com.br/lojas.php
2. http://www.franscafe2.com.br/lojas/

This is what I need specifically:
I need to List "all" the stores found on both websites, containing:
- store name
- store address
- store zip code
- store city
- store state

On website 1. stores locations are shown as a type of table.
On website 2. stores locations are shown on a map.

I've attached an example workbook with the results for both websites.
But, I doesn't have to be like the example workbook, I just need the stores information, I can do the cleaning afterwards :)

Thanks all :)
 

Attachments

  • Store Locations.xlsb
    8.8 KB · Views: 11
• For first link, just using any webbrowser inspector tool,
each store is in a DIV element with borda class.
When you move to next page, see the URL address updating.
So you just have to change URL until there is no DIV borda …

• For map link, all is within the Web page initial code !
Maybe the reason why it is so slow …
You just have to parse text data after each tooltip_content …

I think data in both links can be grabbed using requests
like in this must see Web Scraping tutorial !
​
 
Marc L said:
• For first link, just using any webbrowser inspector tool,
each store is in a DIV element with borda class.
When you move to next page, see the URL address updating.
So you just have to change URL until there is no DIV borda …

• For map link, all is within the Web page initial code !
Maybe the reason why it is so slow …
You just have to parse text data after each tooltip_content …

I think data in both links can be grabbed using requests
like in this must see Web Scraping tutorial !
​
Click to expand...

Update: Website 2. I solved by copying the entire html body, then pasting it on excel.

Now, Website 1. is a bit difficult. Could you clarify the part where you have to change URL until there is no DIV. I know there isn't any stores after pagina=41

http://www.reidomate.com.br/lojas.php?pagina=41&estado=&cidade=&cep_bsc=#ancora

Rgds
 

No possible by QueryTable : there is no table !
By piloting IE or better by a request like in the post #4 tutorial link …

I'm now out of Net for a day …
​
 
Marc L said:
No possible by QueryTable : there is no table !
By piloting IE or better by a request like in the post #4 tutorial link …

I'm now out of Net for a day …
​
Click to expand...

Hey, thanks for the help.
I managed through the loop with querytables.

Sub URLs()
Dim Erw, Frw, Lrw
Drw = 1
Frw = 1
Lrw = Range("A" & Rows.Count).End(xlUp).Row
For Erw = Frw To Lrw

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Range("A" & Erw).Value, Destination:=Range("G" & Drw))
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Drw = Drw + 80
Next Erw

End Sub
Click to expand...

Best rgds!! :)
 

There is no table but yes you can download all the webpage
using a QueryTable, well done !

Just notice some web pages cannot be loaded by QueryTable,
in this case you must use a request or pilot IE …
​
 
Marc L said:
There is no table but yes you can download all the webpage
using a QueryTable, well done !

Just notice some web pages cannot be loaded by QueryTable,
in this case you must use a request or pilot IE …
​
Click to expand...

Yep :) thanks for the attention and tips!!! :) rgds
 
You must log in or register to reply here.
Back
Top