Whether a digital native or immigrant, you probably know the basic functions of Excel inside out. With Excel, it is easy to accomplish simple tasks like sorting, filtering and outlining data and making charts based on them. When the data are highly structured, we can even perform advanced data analysis using pivot and regression models in Excel.
But the problem is, how can we extract scalable data and put them into Excel efficiently? This would be an extremely tedious task if done manually by repetitive typing, searching, copying, and pasting. So, how can we achieve automated extraction and scraping data from websites to Excel? In the following parts, you can learn 3 different solutions with easy steps.
Method 1: Using Web Scraping Tool (No-coding)
Web scraping is the most flexible way to get all kinds of data from webpage to excel. Many users may feel hard because they have no idea about coding, however, an easy web scraping tool like Octoparse can help you scrape data from websites to Excel without any coding.
As an easy web scraper, Octoparse provides auto-detecting functions based on AI to extract data automatically. What you need to do is just checking and make some modification. You can also use the preset scraping templates for popular sites like Amazon, eBay, LinkedIn, Google Maps, etc. to get the webpage data with several clicks. What’s more, Octoparse has advanced functions like API access, IP rotation, cloud service, and scheduled scraping, etc. to help you get more data.
Let’s see the simple steps below to extract data from any websites into Excel with Octoparse.
3 steps to scrape data from website to Excel
Step 1: Paste target website URL to begin auto-detecting.
After download Octoparse and install on your device quickly, you can paste the site link you want to scrape and Octoparse will start auto-detecting.
Step 2: Customize the data field you want to extract.
A workflow will be created after auto-detection. You can easily change the data field according to your needs. There will be a Tips panel and you can follow the hints it gives.
Step 3: Download scraped website data into Excel
Run the task after you have checked all data fields. You can download the scraped data quickly in Excel/CSV formats to your local device, or save them to a database.
Web scraping project customer service
If time is your most valuable asset, and you want to focus on your core businesses, outsourcing such complicated work to a proficient web scraping team that has experience and expertise might be the best option. Data scraping is difficult to scrape data from websites due to the fact that the presence of anti-scraping bots will restrain the practice of web scraping. A proficient web scraping team would help you get data from websites properly and deliver structured data to you in an Excel sheet, or in any format you need.
Here are some customer stories that how Octoparse web scraping service helps businesses of all sizes.
Method 2: Using Excel Web Queries
Except for transforming data from a web page manually by copying and pasting, Excel Web Queries are used to quickly retrieve data from a standard web page into an Excel worksheet. It can automatically detect tables embedded in the web page’s HTML. Excel Web queries can also be used in situations where a standard ODBC (Open Database Connectivity) connection gets hard to create or maintain. You can directly scrape a table from any website using Excel Web Queries.
6 steps to extract website data with Excel web queries
Step 1: Go to Data > Get External Data > From Web.
Step 2: A browser window named “New Web Query” will appear.
Step 3: In the address bar, write the web address.
Step 4: The page will load and will show yellow icons against data/tables.
Step 5: Select the appropriate one.
Step 6: Press the Import button.
Now you have the web data scraped into the Excel Worksheet – perfectly arranged in rows and columns as you like. Or you can check out from this link.
Method 3: Scrape Web Data with Excel VBA
Most of us would use formula’s in Excel (e.g. =avg(…), =sum(…), =if(…), etc.) a lot, but are less familiar with the built-in language – Visual Basic for Application a.k.a VBA. It’s commonly known as “Macros” and such Excel files are saved as a **.xlsm. Before using it, you need to first enable the Developer tab in the ribbon (right-click File -> Customize Ribbon -> check Developer tab). Then set up your layout. In this developer interface, you can write VBA code attached to various events. Click HERE to getting started with VBA in Excel 2010.
Using Excel VBA is going to be a bit technical – this is not very friendly for non-programmers among us. VBA works by running macros, step-by-step procedures written in Excel Visual Basic.
6 Steps to scrape website data using Excel VBA
Step 1: Open Excel and add a new module by going to the Visual Basic Editor (ALT + F11).
Step 2: Import the MSXML2 and MSHTML libraries. This allows you to interact with websites:
'Import web libraries
Imports MSXML2
Imports MSHTMLStep 3: Declare variables for the XMLHTTP object and HTML document:
Dim xmlHttp As MSXML2.XMLHTTP60
Dim html As MSHTML.HTMLDocument
Step 4: Use XMLHTTP to make a GET request to the target URL and parse the response into an HTML document:
Set xmlHttp = New MSXML2.XMLHTTP60
xmlHttp.Open "GET", "https://website.com", False
xmlHttp.send
Set html = New MSHTML.HTMLDocument
html.body.innerHTML = xmlHttp.responseText
Step 5: Extract the needed data using DOM navigation/selectors, and export the scraped data to Excel.
Dim data As String
data = html.getElementById("element").innerText
Cells(1,1) = dataStep 6: Clean up variables for memory management. Repeat the steps to scrape multiple pages if needed.
Final Words
Now, you have learned 3 different ways to pull data from any website to Excel. Choose the most suitable one according to your situation. However, Octoparse is always your choice if you don’t have coding skills or you want to save more time.
