In this article i will show you how to retrieve data from a web page and import the data into excel worksheet. With this function, you can prepare excel data very efficiently.
The data you want to import from a web page must be included in a html table. Otherwise excel is not easy to retrieve those data.
2. Import Web Page Table Data Into Excel WorkSheet Steps.
There are two menu to import web page data into excel worksheet. We will introduce them one by one.
2.1 Import Web Page Data Into Excel By Data -> From Web Menu.
- Click Data —> From Web ( in Get External Data group).
- Then it will popup a New Web Query window. Input the web page url in address text box and click Go button after it, it will load the web page in web browser below it. And add a yellow arrow at beginning of each html table in the web page.
- If you want to load a html table data, you just need to click the yellow arrow to select it, then click Import button to import the data. During the process, it will prompt excel cell range confirm dialog to you, and you can change the table data target excel cell range.
- If you find above web browser can not parsed out html tables in the web page, and this make you can not get the table data which you need, you can use below method.
2.2 Import Web Page Data Into Excel By Data -> New Query – > From Web Menu.
This method is better than method one. Because it parse the html page at background, it do not use web browser to parse the web page, so it can parse out more html tables.
- Click Data —> New Query ( in Get & Transform group ) —> From Other Sources —> From Web.
- Input the web page url in the popup From Web dialog. If you need to add headers or parameters to the url you can select the Advanced radio button to add more parts to the basic url then click OK button.
- Now it will popup Navigator dialog. In this dialog you can see a list of html tables that excel parsed out in left side, select the table which you want and it will display the table data in right side. There are two tab in right side, one is Table View the other is Web View, you can click each tab to see different data view.
- Now you can click the Load dropdown arrow ( at the dialog bottom right corner ) —> Load To menu item to load the selected html table data into excel worksheet.
- In the next popup Load To dialog, you can select how you want to view this data in the worksheet, and where the data should be loaded into.
- Click Load button, then you can see the html table data has been loaded into your excel worksheet as below.
- There will also show a Workbook Queries panel at excel right side, it list all the workbook queries, you can right click one to edit the query or refresh the query data when the web page data is changed.