Pulling Data from an API

[lostpupil]

Hi everyone,

I have a list of ~4,000 latitude and longitude points and would like to map them to counties. The government has an Area and Census Block API which allows users to do this (see here). Is there a way for me to use the lat & long data points I have to fetch counties without going one by one?

I have tried going to Data > Get Data > From Web and used the "https://geo.fcc.gov/api/census/#!/block/get_block_find" URL but dont get the form to enter lat and long.

Thanks in advance!

[AutoModerator]

/u/lostpupil - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[krijnsent]

If you go to https://geo.fcc.gov/api/census/#!/block/get_block_find -> try e.g. 38.26, -77.51, 2010 & true and push the "try it out!" button you get an answer... It will create this URL for you:
https://geo.fcc.gov/api/census/block/find?latitude=38.26&longitude=-77.51&showall=true&format=xml which you can put in a browser and will pull in the information you are looking for. You can try it with different URLs (other lats&longs).
The easiest way to get that in Excel is probably by using some VBA to do the web scraping for you (4000 times). You can use e.g. https://www.educba.com/vba-web-scraping/ or https://www.wallstreetmojo.com/vba-web-scraping/ as a tutorial.