What do I do to understand an online database to get query results into a spreadsheet?

Question

So very simply, I'm looking at a web query for this database of doctors: http://www.aahivm.org/ReferralLink/exec/frmAdvSearch.aspx

When I search for, say, HIV Primary Care doctors, by state, I get a list of doctors. I've been trying to avoid going through and hand copying and pasting all the results.

BUT

The examples I've seen for things like Excel or Google Sheets, seems to be predicated on having a URL that updates to a new location. Instead for this site, I'm stuck with a static URL.

This is a total newbie question, but I'm curious if there is a way to get the search results into another format like a spreadsheet without cutting and pasting? WHat information in the source should I be looking for? What other options are there that I'm not searching on properly? Is it an issue of it being a ASP .NET database? What am I missing?

Thanks for your help!

Answer 1

Christopher,

You want to find a database that has an REST API, for example. This lets you access their data remotely (without having to access their secure SQL database) using url parameters. For example: www.example.com/api/?location=NewYork would give you all the doctors in New York, and thus it would always be up to date. The data would be in JSON format, so you can use javascript. Are you trying to use the data for another project? If so what type? Mobile app, website?

I don't know if this site has a REST API, but there might be other databases out there.

Answer 2

I'm not sure if this is what you require or if you are aware of it, but you can download web data directly into Excel using Ribbon options. It is also not clear whether or not you have to be a member/signed-in to retrieve data from this site - which may account for why I can't actually download.

From the main Excel Ribbon select Data(Tab)>Get External Data>From Web

Paste your url into the Address bar of the window that opens, and Go. Then navigate to the data table required. In this case select a category and then Search within the web page.

and then click the Import button and follow the Excel prompts for data placement/query maintenance etc.

It may work for you and may beat cut and paste!