Hi,
Sometimes, we need to get data from the webpage. What we do, we simply copy and paste it in excel however, most of the time, excel does weird data paste because the margin and alignment of the web page are different from excel. In Today’s blog-post we will get rid of this kind of problem. We will understand and learn how we import real-time online data in excel?
Importing excel data from Web-
Ms-Office 2007
- Open excel file
- Go to the data tab in Ribbon
- Find the group get external data from the web and click on From the web
4. Type & search the web address
5. Click on the web link to open the web page
6. Once we open the web page, some of the areas have been surrounded by a little arrow with a yellow colour. Once we click on the arrow, the area gets selected.
7. Click on Import
8. A new dialogue box will open, to asking us, “how would we like to import our Data”
9. when the new database will be added into the website, are those the real-time data will be updated in excel automatically? Yes! That can be possible if we make some more changes in setting before the final click “Ok”
10. As showing in the above screenshot, we can find the “Properties” option on the left side and below the table.
11.
Once we click on the properties option, we will get this dialogue box, in which we can control the “Refresh control”-
- Enable Background refresh- This option by-default enable in excel.
- Refresh every – By click on this option, we can set the auto-refresh time in our database. Simple click on the option “Refresh every” and sets the minutes.
Note: we can set the minimum time is one minute
- Refresh data when opening the file- By clicking on this option, every time when we will open the excel file, it’s auto connects the web page and fetches all the updates into the database automatically.
Web data importing in Ms-Office 2013/2016/office 365
- Open excel file
- Go to Data tab in the ribbon
- Get & Transform data
- Get Data
- From other sources
6. From web
7. Once we click on From Web, we will get a dialogue box in which it will ask for Web Page/URL
8. Open the web page in chrome/internet explorer
9. Copy the Link and paste in from web dialogue box
10. Ok
11. After the connection stablished, we will have a dialogue box in which we have many things to do-
- Data Preview
- Multiple selection option
- Display option
- Table view and Web view
- Load or Load to
- Edit
12. Click on load
Note: In our previous blog, we have already explained these options in detail. To read the full blog-post, simply visit the blog-post column.
13. Data will be uploaded in Excel along with the details of query and connection
To get the data automatic refresh or if wanted to set a time after data would be updated. Just follow the procedure, as explained below-
- Once data will fetch in excel, by-default it comes in Table format
- Just click on the data
- We can find a design in the Ribbon tab
- Design->> Properties->> Query Properties
- When we click on query properties-
We will get the same window of “table properties”, as we have already explained above.
Through the above procedure, we can simply import online or real-time data in excel 2007/2010/2013/2016/Office 365.
Thank you so much for reading this blog-post. Try it in your day to day corporate life and if you are stuck anywhere feel free to contact us. we would love to assist you in your problem.
Stay tuned and connected with nurture tech academy.
Happy learning 😊