Solved

How can I pull data from a website into an excel file

Posted on 2009-06-28
7
1,497 Views
Last Modified: 2012-08-14
I am trying to pull public data from a website into an excel file. The website is www.superpages.com - I want to gather all of my local business listings into an excel file so that i can create a directory on my own webpage.  I would also like to know if what I am looking to do is legal. Im not trying to break any laws but I am pretty sure any info that I could, hypothetically get out of a regular phone book and manually eneter wouldnt be illegal to obtain otherwise....right?

I have tried using the web query function inn excel but it seems to only work if there is a table on the site I am pulling from.

p.s. i am not familiar with VB ( i heard it can be done using this program), is there another way?
0
Comment
Question by:Mbielaszka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 3

Expert Comment

by:robofix
ID: 24731169
Is your website operated via PHP and MySQL, as a database?
Then, there should be a tool called phpmyadmin installed on the server, or, if it isn't, you have the possibility to install it.
A short google search should direct you to a website where it is available for download. Or, your website hoster may install it for you.

In phpmyadmin, you have the possibility to EXPORT tables to excel, text, csv or database files. There are really many possibilities.

This is how I do for my own web sites.
0
 
LVL 9

Accepted Solution

by:
Tone' Shelby earned 500 total points
ID: 24731352
Hello,
If you are wanting to do it manually, here is how I have done it ....
1. Start by copying the URL of the webpage with the table you want.

2. Next, open a new, blank worksheet or an existing workbook you want to insert the table into.

3. In Excel 2003 or earlier, point to Import External Data from the Data menu, and then click on New Web Query.
In Excel 2007, click on Data. Find the Get External Data panel on the Data Ribbon, and choose From Web.

4. In the New Web Query dialog, delete any address that is showing in the Address bar. Paste the URL that you copied earlier. You might need to use the Paste keyboard shortcut (Ctrl+V), as your right click menu probably will not show up. Once you have your web address in the box, click Go. The webpage will show up in the query box.

5. Click the little arrow to the left of the table or tables (or any other data) you want to import into Excel and assure the table is highlighted.

6. If you want to keep the formatting and any hyperlinks for the table on import in Excel 2007, click the Options Tab in the upper right corner and choose radio button; Full HTML Formatting and click OK and then click the Import button.

7. In the Import Data box, indicate what cells you want to use to contain the table. Excel will guess for you, but you can change it if you would like to. It will default to the existing worksheet you are working in, but you will also have the option to create a new worksheet.
As far as the legalities, I would consult the particular website policy page for the website and/or contact the webmaster of the particular site...
Hope it helps ....

0
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 24731413
Oh one another note: I do see you mentioned that you tried a web query and had trouble if the website didnt have tables.
The above seemed to work for me on the site you noted if you only click the arrow when importing for the particular list area of the page and also then using the format as table option under the home ribbon tab & the remove duplicates option under the data tab ribbon in Excel 2007.
Also after you have formatted as a table, it will then allow you to filter out blank rows through the dropdown multiple selection option by unchecking (Blanks).
Again hope it helps ....
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 45

Expert Comment

by:patrickab
ID: 24731516
Mbielaszka,

>I'm not trying to break any laws but I am pretty sure any info that I could, hypothetically get out of a regular phone book and manually enter wouldnt be illegal to obtain otherwise....right?

WRONG - I am afraid you are so wrong. The contents of telephone books is Copyrighted material. You may not reproduce it in any form at all. Of course you can take a photocopy of a page(s) for your own use but you cannot re-publish that data anywhere - that is unless you want to be sued for copyright infringement. If you lose such a law suit you would have to pay damages, legal costs, claimed loss of revenue, forfeit your website and maybe even your computer, a fine and so on. If that's all pretty trivial to you, then go ahead.

However if you want to extract data for analysis then that is generally permitted. Some websites expect that - for example stock market data websites. However what you are proposing is not legal unless the laws in your country are very different to ours in the UK. However copyright laws have been standardised in most western countries so my guess is you are subject to the same copyright laws as us in the UK.

Patrick
0
 

Author Comment

by:Mbielaszka
ID: 24732130
patrick Thank you for the legal advice but i posted my question mainly for technical advice. However how can public info be copyrighted? especially when i am not selling it? i have three phonebooks in my home all from different companies which business paid to be in - I wanted to know if it was illegal to gather data from someone elses website.
0
 

Author Closing Comment

by:Mbielaszka
ID: 31597674
thankyou very much! i was pulling my hair out over this I hope that the experts on this website such as yourself get compensation for your efforts! thanks again!
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24732681
It is irrelevant whether it's in a phone book or on a website. Because it is publicly available does not give you the right to copy it. It is copyrighted material and that's all there is to it.

>I wanted to know if it was illegal to gather data from someone else's website.

The answer is yes, it is illegal -  unless they specifically state that it can be copied and re-published.
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question