How to Extract Data from Website to Excel?

Robin
Sep 18, 2017

Have you ever needed to scrape data for a project? Let’s say your target is to collect 1000 email addresses or contact details from 5-star hotels to build a master database in Excel.

What would you do?

If you lack specialized data extraction software, you might think your only option is the tedious, soul-crushing manual "Copy & Paste" method. Consider the time commitment: a proficient user might take 7 to 8 hours, but for most, this Herculean task could easily drag on for days.

Why waste time when you can work smarter?

Modern Microsoft Excel, via its powerful Power Query engine, offers a streamlined way to accomplish web data extraction projects without writing a single line of code.

Step-by-Step: Extracting Web Data into Excel

The old "New Web Query" wizard has been replaced by the much more robust Power Query connector. Here is the modern workflow:

Step 1. Open the spreadsheet.

Step 2. Go to the Data menu.This is your primary hub for all external data.

Step 3. Click on the "From Web" icon. Find this in the "Get & Transform Data" group on the far left.

Excel Get External Data ribbon showing options to import data from Access, Web, Text, and other sources

Step 4. A new window will appear. Enter your target URL. A dialogue box will appear. Paste the URL of the webpage containing your desired data and click OK.

Step 5. Use the Navigator

Excel will analyze the webpage and present a "Navigator" window.

Table View: Excel automatically detects structured tables on the page. For a preview, click through the "Suggested Tables" on the left to see it on the right.
Add a Table Using Examples: This option is for the cases where the website doesn't use a standard HTML table. Simply, click this button. You can simply type a few examples of the data you want to extract from the page, and Excel’s AI will automatically detect the way you want to extract data and build a custom table for you.

Step 6. Transform or Load

This option is for cleansing your data.

If the data needs cleaning like removing extra spaces or formatting text, click Transform Data. It will open the Power Query Editor.
If the data does not need any changes, click Load to drop it directly into your spreadsheet as a structured and formatted table.

Keeping Your Data Fresh During Extraction: The Auto-Update Feature

Mostly scrapers use Power Query to create a live connection with the source to smartly extract data in real time. Once done, you don’t need to repeat scraping of the same page. Your data will be updated automatically even if the source website changes its data.

How to trigger the update:

Manual Refresh: Simply click any cell in your imported table, go to the Data tab in the ribbon, and click Refresh.
Automatic Refresh: To do it once for real-time extraction always, go to the Data tab, click Queries & Connections, right-click your query, and select Properties. You can then check "Refresh every (x) minute" or "Refresh data when opening the file".

Data Extraction for Fueling Data Mining

For small-size data extraction, modern analysts and outsourcing companies rely on the aforesaid method, which further helps in fueling data mining. By leveraging Power Query, you get rid of manual fatigue and it’s like a walkover to gain access to sophisticated and productive data pipelines. This practice certainly provides you predictive analysis for desired results.

Once your data is in Excel, take a long breath and just relax. The setup connection in the spreadsheet with the target URL will handle the heavy lifting. Later, you just check out patterns and get insights into demographics and perform predictive analysis. This is how it all becomes a "piece of cake" once your data is structured and live.

Conclusion

Data extraction is now the most efficient data collection strategy for data mining and predictive analysis. Global companies today rely on it for diverse purposes like price monitoring, competitor analysis, consumer behaviour detection, etc. So, knowledge discovery is easy with this setup. Many small companies use the aforesaid method to detect what their customers do because their competition horizon is initially small. Certainly, the Excel data extraction method makes a day for many businesses via scraping.