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.
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.
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]
-->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
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
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
Response by poster: 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
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]
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]
This thread is closed to new comments.
posted by bitdamaged at 11:33 AM on July 10, 2010 [1 favorite]