Rafiul Hasan
Sometimes you may need to import data from websites and show it in an Excel file. Most of the time, this happens for data entry purposes. So, if you are a data analyst, this is a must for you to update data from websites into Excel. But, this is not a handy approach to copy the data column by column and then paste it. It is time-consuming as well as not a smart approach. Moreover, this process will not update data with a change in the website. So, as a data analyst, you must need to import data from websites and automate data scrapping into Excel. You have come to the right place if you are searching for the procedure to perform this task. This tutorial will provide you with a complete guideline for automated data scraping from websites into Excel.
📁 Download Practice Workbook
You can download the practice book from the link below.
Stepwise Process of Data Scraping in Automated Way from Websites into Excel
Let’s say, we have to insert a database from a website where current stocks of different types of oil are described.
The website data updates automatically with the change in the stock amount of the oil reserve. We want to automate the update in the Excel file with the change in the websites. Let’s check out the steps for automated data scraping from websites into Excel.
⏩ Step 1: Inserting Web Address into Excel
For automated data scraping, we need to insert the corresponding websites in the Excel file at first.
- So, firstly, open the Excel file and go to the Data tab> select From Web under the Get & Transform Data group.
- Then, a command box with the header From Web will appear on the Excel worksheet.
- Here, in the URL field, input the web address and then click OK.
Read More: How to Use Web Scraping with Excel VBA (3 Suitable Examples)
- Now, the Navigator window will show up.
- Here, select your data from the Display Options (i.e. Stocks million barrels). You can preview the data from the Table View tab.
- Click Load.
- As a result, your data will be imported into the Excel worksheet from the website.
⏩ Step 3: Refresh Data for Automated Updating
As we have imported our data from the websites, we need to update data with any change in the website.
- For updating every time, just go to the Data tab and click Refresh All. Your data will be updated then every time automatically with any change in the websites.
Read More: How to Import Table from Website to Excel (2 Easy Ways)
How to Edit Imported Data from Websites
If your imported data got some blank cells in Excel, then it is possible to confuse the users or readers. Don’t worry! There is a simple solution for that.
⏩ Solution:
- Firstly, go to the right corner of the worksheet where you will find Queries & Connections.
- Here, double-click on the data table.
- Then, the Power Query Editor window will appear.
- Here, go to Transform> click dropdown of the Detect Data Type> select Down.
- As soon as, it will create a new option Filled Down in the APPLIED STEPS field.
- Here, select Changed Type.
- After that, go to the Home tab and select Replace Values under the Transform group.
- Here, the Insert Step command box will show up.
- Just agree and click on Insert on that box.
- Next, the Replace Values dialog box will appear.
- Here, keep the Value To Find field blank and input Null in the Replace With field.
- Then, click OK.
- After that, select Filled Down in the APPLIED STEPS field.
- Now, change the Name from the Properties option in the Query Settings.
- Finally, click Close & Load.
So, your data will show the final output without blank cells.
Conclusion
In this article, I have tried to show you the complete procedure of automated data scraping from websites into Excel. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Have a great day!
Stay Connected.
