1

How can I scrape .svg images from Wikipedia into Google Sheets?

What I've discovered so far:

I ultimately would like to have a Google Sheet with:

  • Column A: state name
  • Column B: wikipedia image of state as territory in the US

I thought I could achieve that programmatically with the following:

  • Column A: state name
  • Column B: IMAGE(<image url in column C>)
  • Column C: url (based on formula) of .png Wikipedia image of state as territory in the US

But that won't work for the reasons I gave above. I'm ready to venture into using more complicated tools if that's necessary.

Improve this question

2 Answers 2

Reset to default
1

You can use =IMPORTXML() in Google Sheets to scrape the Wikimedia Commons category page for this. This video is a good tutorial on using the IMPORTXML function.

In your case, you'll want to then filter out the various filenames you don't want (the ones that do not match the common format) and then use regex to grab the name of each state:

Demo

Screenshot of demo spreadsheet

Improve this answer
3
  • How did you find the "category page" you linked above? I didn't know such a page existed! For example, I went to the Washington State svg file page, poked around, but couldn't find a link to the more general "category page"
    – lowndrul
    Jul 17, 2022 at 13:11
  • One more question: how did you generate the image xpath in your Demo file? E.g., using the SelectorGadget tool recommended in the video you linked, I selected one of the .svg images from the "category page" you linked. The xpath generated was just "//img". I then realized I needed to de-select a couple images at the top of the page. When I did, the xpath generated became "//*[(@id = "mw-category-media")]//img". That's almost the xpath in your Demo file, which was actually "//*[(@id = "mw-category-media")]//img/@src". Did you just know to add "/@src" manually?
    – lowndrul
    Jul 17, 2022 at 13:20
  • 1
    @lowndrul To find categories, scroll down to the gray box at the bottom of the page. Some categories are nested within others, so you might need to click around to find the right parent category. These categories are also tagged manually so it's possible that they'll be incomplete. For the image XPath, I saw that I was getting the img and not the URL (which Sheets would need to download the image) so I looked in the source code and saw that the URL was in the "src" attribute within the "img" instance. I believe the video had a demo on using the source code to find attributes.
    – Wolf
    Aug 15, 2022 at 1:42
1

Why don't you use the API?

https://en.wikipedia.org/w/api.php?action=help&modules=main

https://en.wikipedia.org/wiki/Special:ApiSandbox

What are you trying to achieve? Why write software if there are only 50 states?

Improve this answer
7
  • Because I'm actually doing this for more than just US states. But this use case is similar to all my other cases. Otherwise, yeah, as you suggest, I would have just marched through generating each .png link manually.
    – lowndrul
    Apr 18, 2022 at 12:36
  • Can you provide an example of usage for a particular state? I'm not familiar with this at all, and it's kind of a lot.
    – lowndrul
    Apr 18, 2022 at 12:47
  • @lowndrul Well, if you want all the images from the Wikipedia article about Alabama en.wikipedia.org/w/…
    – Gantendo
    Apr 18, 2022 at 12:49
  • 1
    en.wikipedia.org/w/…
    – Gantendo
    Apr 18, 2022 at 12:56
  • 1
    Or ask over at en.wikipedia.org/wiki/Wikipedia:Help_desk
    – Gantendo
    Apr 18, 2022 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.