Two Approaches to Data Extraction From a Website to Excel
Data extraction from websites to Excel is a common practice. This powerful tool allows you to sort and filter data, perform mass operations like increasing the numbers by a certain percentage, combine data from multiple columns into one, and much more.
In this article, we will compare two main approaches to extracting data from websites to Excel: the functionality inside Excel and web scraping technology.
Tools within Excel for data extraction from a website to Excel
You may be surprised, but Excel can pull data from external sources, including websites. The tools for data extraction are located under the “Data” tab.
Using the “Get External Data” option
- Open the “Data” tab, select “Get External Data” > “From Web”.
- A browser window named “New Web Query” will appear. In the address bar, enter the web address of the page you want to scrape.
- The page will load and you will see yellow icons next to the data in tables. Click the icon against the table you want to pull to Excel.
- Press “Import” and select a cell into which the data will be copied. Wait for Excel to process your request and see the data in a sheet:
Get Data From Web option in Excel
If you want to get fresh data at certain intervals, go to the “External Data Range Properties” and enable “Refresh control” options:
Select Refresh control options to schedule data import to Excel
While using the “Get External Data” tool you may get script errors on some pages. This is a thing that occurs in the Excel web browser. Though you can press “No” several times and until the “Import” button becomes active, this slows down the work:
Script Error message that displays during data import to Excel
An alternative is to use another Excel option “Get & Transform query” which works fine.
Using “Get & Transform Query” option
- Under the “Data” tab press Get Data > From Other Sources > From Web.
- In the “From Web” dialog window enter the desired URL and press “OK”.
- The “Navigator” dialog window with a list of tables from the web page will open. Pick the desired data table.
- Click the “Load” button to copy the data to Excel:
Select an option From Other Sources, From Web
Paste the URL to get data to Excel from
In the Navigator window, select the tables you want to download to Excel
Now let’s summarize the usage of Excel as a tool for data extraction.
- It’s free as long as you have Excel installed on your computer.
- It doesn’t require coding knowledge.
- You can pull live data from a web page into Excel at needed intervals.
- With this method you can only extract the information provided on one page. To extract more data you will need to repeat your actions on every new page.
- Only data organized in a table or as preformatted text can be captured.
Thus, Excel tools will help you to extract and track data like stock prices, sports scores, weather forecasts, exchange rates, and other types of data that are usually organized in a table and tend to change frequently.
What to do if you need to get data from a website into Excel but the data is not displayed in the table format? Or you have to download data from multiple pages and websites? In that case you need a more advanced tool – web scraping.
How to automatically extract website data to Excel using web scraping technology
Web scraping is a technology that automates data extraction from multiple online resources and puts data in a structured format. Most often it is a CSV file or Excel, or Google Sheets.
To automatically extract data from websites into Excel, you can either: write your own web parser if you can code, or employ some DIY tool or scraping software if you have a technical background and are ready to invest your time in mastering and supporting the tool.
If you, though, need to get required data in a structured format ready for usage, the best choice would be an online web scraping service like eScraper.
Let’s see how web scraping is used on the example of eScraper service.
How to perform web scrape to Excel with eScraper
eScraper is an online service that extracts data according to your requirements. You can specify the target website (or multiple websites and marketplaces), entities you need to get extracted, and the frequency of data extraction if you need recurring service. All the data will be sent to your email or FTP in a CSV format.
The possibilities of web scraping technology go much further than downloading tables. For example, when using eScraper to extract data from an eCommerce website you will get:
- Entire product listing (product name, type, full and short description, full category path, full and short description, sale and regular price, etc).
- Meta descriptions, product SKUs not visible on the page.
- Image URLs.
- Product variations (products with drop-downs where customers can choose some options).
Here you can see an abstract from the CSV file that we’ve prepared for one of our clients:
Example of data downloaded from a website to Excel with eScraper
How to save time with the web scraping service
Firstly, you can be sure that you get the data cleaned up from unnecessary tags and ready for usage. Thus you can make mass modifications like increasing prices by a certain percentage as well as modifying product description adding your company name. Or you can request to make necessary mass changes for you.
If you plan to upload scraped data into your website you can get a file ready to be imported to your target platform. Specify it in the order form whether you need an import to Shopify, WooCommerce, PrestaShop, or other target platforms.
And finally, eScraper can automatically extract data from a website into Excel at regular intervals and upload it to the FTP or send you via email. Thus you can get fresh data from your suppliers, manufacturers, vendors, dropshippers, etc.
You can get a sample file with up to 100 records to test the service and when the free test file is perfect, you can order the full file with all records. To get one, follow 3 steps:
- Go to the e-Scaper.com and find the REQUEST FREE SCRAPE form. Enter the website address you want to scrape and list out the details that should be extracted.
- Get the file to your email within 24 b hours and check if it fits you. Suggest any corrections and receive a new sample file.
- Select a suitable pricing plan and order the service to get all the data. You can open the full file in Excel, Google Sheets, or import it directly into your store.
As you can see, a web scraping technology extends the range of tasks you can solve when it comes to extracting web data to Excel. With eScraper you will easily grab data from websites to Excel and speed up your work using additional services.