I was answering this question when the need arose for a good presentable data set. I settled for using random things but my first thought was to use a Wikipedia list, such as this List of longest suspension bridge spans, for instance.

I thought Wikipedia would have a good way to export lists but it does not. Treating manually the source is too bothersome.

As such, I ask whether there is some way to parse either the list's source or the page's source-code (even the printable version would work) to that the end result would be a .csv of the list?

I don't mind losing the images, as long as the values were exported. I'm tagging Excel because for the purpose of the pointed question the data needed to be treated on it, but since a .csv is (roughly) universal, I will content with any non-Excel answer.

This is an example of the wikipedia table's source:

{| class="wikitable sortable"
|- style="background:#efefef;"
! style="text-align:left;" class="unsortable"|   
! style="text-align:left;"| Rank
! style="text-align:left;"|Name
! style="text-align:center;"| Main<br />span<br />metres
! style="text-align:center;"|Main<br />span<br />feet
! style="text-align:center;"|Year opened
! style="text-align:left;"| Location
! style="text-align:left;"| Country
! style="text-align:left;"|
|[[File:Akashi bridge.jpg|150px]]||1||[[Akashi Kaikyō Bridge]]<br />(The longest since 1998)||1,991||6,532||1998||[[Kobe]] - [[Awaji Island]] ([[Hyōgo Prefecture|Hyōgo]])<br /><small>{{Coord|34|37|1.3|N|135|1|18.9|E|type:landmark|display=inline|name=Akashi Kaikyō Bridge}}</small>||{{JPN}}||<ref>{{cite web|url=http://www.jb-honshi.co.jp/english/technology/akashi.html|title=Akashi-Kaikyo Bridge|publisher=Honshu-Shikoku Bridge Expressway Company Limited|accessdate=2009-04-06}}</ref>
|[[File:Xihoumen Bridge.JPG|150px]]||2||[[Xihoumen Bridge]]||1,650||5,413||2009||[[Zhoushan]] ([[Zhejiang]])<br /><small>{{Coord|30|3|42.4|N|121|54|57.6|E|type:landmark|display=inline|name=Xihoumen Bridge}}</small>||{{CHN}}||<ref>{{cite web|url=http://en.structurae.de/structures/data/index.cfm?ID=s0015467 |title=Structurae [en&#93;: Xihoumen Bridge (2008) |language={{de icon}} |publisher=En.structurae.de |date= |accessdate=2009-04-03}}</ref>
|[[File:Storebæltsbroen-2.jpg|150px]]||3||[[Great Belt Bridge]]||1,624||5,328||1998||[[Korsør]] - [[Sprogø]] ([[Region Zealand]])<br /><small>{{Coord|55|20|31|N|11|2|9.3|E|type:landmark|display=inline|name=Great Belt Bridge}}</small>||{{DNK}}||<ref>{{cite web|url=http://www.storebaelt.dk/kollage/english/bridge |title=Storebælt &#124; The Bridge |publisher=Storebaelt.dk |date= |accessdate=2009-04-03}}</ref>
|[[File:Yi Sun-sin Bridge in construction1.jpg|150px]]||4||[[Yi Sun-sin Bridge]]||1,545||5,069||2012||[[Gwangyang]] - [[Yeosu]] ([[South Jeolla Province]])<br /><small>{{Coord|34|54|21.4|N|127|42|18.1|E|type:landmark|display=inline|name=Yi Sun-sin Bridge}}</small>||{{KOR}}||<ref>{{cite web|url=http://rki.kbs.co.kr/english/news/news_Ec_detail.htm?lang=e&id=Ec&No=89984&current_page=|title=New suspension bridge to break world mark for tower height|publisher=rki.kbs.co.kr|accessdate=200012-06-26}}</ref>
  • Questions about web applications (such as wikipedia's export function) are off topic. – terdon Sep 20 '13 at 17:31
  • Is there a place where I can place them? – Doktoro Reichard Sep 20 '13 at 17:33
  • I would guess it's on topic on Web Applications. In any case, as far as I can tell there is no export option on wikipedia so it will come down to a parsing problem which is perfectly on topic here. If you move the focus away from wikipedia to 'how can I turn something like the wikipedia source for a table into a csv?' that would be on topic. – terdon Sep 20 '13 at 17:36
  • I agree with you. Although on topic there, since Wikipedia doesn't have the option, the probable answers I would receive would be "No, can't do that". Parsing seems like a more feasible solution, and that was what I intended to meant (as if "a way to treat wikipedia lists and then get a .csv). Linguistic barriers are bothersome sometimes. Updating the question to reflect this. – Doktoro Reichard Sep 20 '13 at 17:41
  • What OS are you using? To do this properly and flexibly would probably be easiest with a Python (or other) script that splits on ||. – beroe Sep 20 '13 at 18:21

You can import it in Excel 2010 by going to Data -> From Web, pasting the address, and clicking on the yellow 'table' marker at the top. (Annoyingly, the big bridge table doesn't get its own marker.) That seems to work, although there are two lines per bridge in your example. It's a bit neater using the ImportHtml function in Google Spreadsheets.


Actually I found an answer that served my purposes (and quite surprised it worked on Excel 2003 with no problem whatsoever, barring formatting).

I accessed the Edit beta function and copy-pasted the list, as-is, to Excel. That copied everything although it didn't format as I would expect (there are a lot of empty cells).

  • Pasting plain text into excel will also work, if you don't keep the data in different cells (I assume that's what you mean bu 'formatting'), with the same information for each row in the correct cell, how is this a solution? – terdon Sep 20 '13 at 18:00
  • You're right... I'm wondering how I missed that... About the formatting I assume Excel 2003 can't read the CSS information of the page, that might be correctly interpreted on later versions. – Doktoro Reichard Sep 20 '13 at 18:05

There is a Python script called wiki2csv referred at the Wikipedia:Tools page that was written for this purpose.

This program [wiki2csv] was designed to help edit large tables on wikipedia in the wikitable format. It works by converting the wikitable syntax to comma separated value syntax, that can be edited with Excel or LibreOffice. The result can then be transformed back to wikitable syntax. The program tries to preserve the wikitable formatting and metadata as good as is practically possible.


wiki2csv requires Python 2.7. The core conversion routines will probably also work on older Python versions, but wiki2csv uses the argparse library to parse command line arguments, that is only available since Python 2.7.

Check the wiki2csv BitBucket page for more details.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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