Skip

HTML Table to Excel to CSV
July 10, 2010 11:12 AM   Subscribe

I would like to take the information from this Wikipedia page (the HTML table parts) and dump it into Excel. I don't need the flags, I just need the latitude, longitude, city name and country. What's the easiest way of doing this?

I will eventually be dumping it out to a CSV but I need to do some manipulation of it in Excel first. I'm on a Mac if that helps.
posted by jontyjago to Computers & Internet (7 answers total) 1 user marked this as a favorite
 
Cut and paste in chrome will keep the table layout in Excel. Firefox will not, if Chrome does it, Safari likely will as well.
posted by bitdamaged at 11:33 AM on July 10, 2010 [1 favorite]


Copy and paste works fine from Firefox, but in Excel you have to use "Paste Special," then choose to paste as Text. (Tested in Windows and Excel 2007, but I imagine the Mac version has the same option and should act the same.)

Otherwise, pasting into a text editor like TextEdit should work, and then load into Excel as Tab-Separated Values.
posted by CrayDrygu at 11:36 AM on July 10, 2010


In Excel 2003
-->Data
-->Import External Data
-->New Web Query

Then bring up the webpage you want in the box and select the tables you want to import to Excel from the webpage. I don't have Excel 2007 in front of me to write the specific commands, but the feature must still exist.
posted by unannihilated at 11:38 AM on July 10, 2010 [2 favorites]


I just tested unannihilated's suggestion in 2007 on a PC, and it works great! There's some cleanup required, but a hell of a lot less than there would be with copying and pasting. That is one awesome technique!
posted by SuperSquirrel at 12:02 PM on July 10, 2010


The main sources listed are this and this - both of which have tables I can copy-and-paste from Firefox into Excel 97 no problem.

If you're converting to decimal degrees, remember that there are 60 minutes per decimal degree, and that north and east are normally positive. So for example Aberdeen, Scotland, at 57°15′N, 02°11′W is, in decimal degrees, 57.25,-2.18. Also, 57°15′N, 02°11′W is a kinda inaccurate position. If it was 57°9′N, 2°7′W it would be more accurate. So I guess the list might not always be entirely accurate?
posted by Mike1024 at 2:01 PM on July 10, 2010


I've got it working with Paste Special and using the Unicode Text option, using Text alone just pasted the first character (which is what I had tried earlier).

I have Excel 2008 Mac Spanish version and there is no Web Query option, only Text File and ODBC link.

But it's working, so thank you all!
posted by jontyjago at 2:05 PM on July 10, 2010


In Excel 2004 Mac you can use the web query option.
Create a plain text file containing the following and save it:

WEB
1
http://en.wikipedia.org/wiki/List_of_cities_by_latitude

Selection=EntirePage
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False


Then back in Excel, choose Data, Get external data, Run Saved query... and browse to the file you saved above.
posted by Lanark at 3:36 PM on July 11, 2010 [1 favorite]


« Older Looking for information/recomm...   |  I hate my big corporate bank t... Newer »
This thread is closed to new comments.


Post