How do I bring HTML data into Excel?

December 29, 2009 2:54 PM

I once used a web-based spreadsheet tool that let me copy data from pretty much any webpage into a properly formated spreadsheet. Upon pasting an entire page worth of HTML-formatted search results, for example, it prompted me for the desired cell arrangement in the spreadsheet and applied it to the entire set of data. I can't remember what it was called, I can't seem to google the right terms and this will save me (not to mention my employer) countless hours copying and pasting data this week and next. Alternatively if you have another solution, please share.
posted by reeddavid to Computers & Internet (10 answers total)

maybe zoho or google docs?
posted by 3mendo at 3:07 PM on December 29, 2009

Response by poster: I used it before Google Docs was introduced. Just checked Zoho, that's not it either. Thanks though!
posted by reeddavid at 3:09 PM on December 29, 2009

You can do this directly in Excel using a web query:

http://help.isu.edu/index.php?action=knowledgebase&catid=38&subcatid=66&docid=1023

http://www.exceluser.com/explore/webqueries_1.htm
posted by Boobus Tuber at 3:39 PM on December 29, 2009

Response by poster: Web query looked very promising, but Excel doesn't identify the table to be imported. It's in a frame, and when trying to access the frame independent of its parent page I get redirected and the data disappears.

Thanks though!
posted by reeddavid at 4:07 PM on December 29, 2009

If you go to the page in IE, you can normally right click on a table and choose "export to Excel" or "open in Excel" (even if it's in a frame), and it will import that data into a spreadsheet.

It's the only reason I still use IE occasionally.
posted by gemmy at 4:28 PM on December 29, 2009

There's a firefox add-on that I haven't used it in ages but might be what you're talking about:

Table Tools
posted by yarrow at 6:20 PM on December 29, 2009

Sorry, messed up the link.

Table Tools
posted by yarrow at 6:22 PM on December 29, 2009

What happens if you save the page to disk as .html and open that in Excel? Works sometimes when web queries fail to.
posted by Boobus Tuber at 2:44 AM on December 30, 2009

If you're using Firefox, and if the data is already in a table in the website, you don't need the extension. Just CTRL-click on the first cell and drag to highlight (you know it's working because it should be putting little boxes around each cell). Then copy and paste into OpenOffice (or presumably Excel) and it should just work.

If it's not in a fantastic format already but is in some kind of repetitive format, my blunt instrument for structuring text is The Regex Coach (masters, of course, use grep and a multitude of pipes, but the GUI is definitely easier to pick up). This isn't an automated solution, and if you don't already know about regular expressions, this can be slightly hard to get into, but it's way better than copy-paste-copy-paste-copy-paste... and knowing how to use regular expressions is a useful skill.
posted by anaelith at 3:16 AM on December 30, 2009

Your description sounds a bit like DabbleDB. Watch the video and see if it's the tool you were vaguely remembering.
posted by anildash at 3:45 PM on December 30, 2009