Import data from Web

Copper Contributor

Hello,

I'm after some advice regarding importing website data into excel. I'm trying to import numbers from a website that are not in tables. When I paste the website address into excel, it comes up with lots of errors and won't even preview the webpage. The first error is JSON is undefined. If I close this it comes up with more errors about the content. I have attached a picture of the content of the webpage which is on an internal system. I thought the issue might be that the boxes on the webpage are expandable if you click on them?

Any advice would be greatly appreciated.

9 Replies
Maybe that website comes with a webservice to cnnect to the data in other ways?

@Jan Karel Pieterse 

Thanks so much for your prompt response.

Do you mean where the source of data comes from?

I've looked into getting the source data that is displayed on the webpage but that is not possible.

No, I mean companies sometimes offer a separate means of getting data than via their website. One technology used for that goal is called a webservice. Search the website for the keyword "webservice". If nothing comes up they don't offer that. Which precise button did you use (and in which version of Excel) to try to get the data?

@Jan Karel Pieterse 

 

There is no indication on the webpage that there is a webservice.

 

I tried 2 options:

Data - From Web

Data - New Query - From Other Sources - From Web

 

I have identified the element on the webpage using F12. Is it possible to use the location of the data on the webpage to pull into Excel?

Is it possible to share the url?

@Jan Karel Pieterse 

Unfortunately not, it is on an internal system.

Is it possible to use the Xpath of the specific number I need and use this in Excel to pull the data?

You would probably have to revert to VBA for this. Do you have any VBA experience?

@Jan Karel Pieterse 

 

I have a little experience but so far I've been unable to get it to work. I've attached a picture of the data I'm trying to get. I need the number 17387. Image.png

Please share any code you have and indicate what doesn't work.