Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!


Click here to return to the 'Correctly copy and paste tables from the web into Excel' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Correctly copy and paste tables from the web into Excel
Authored by: EntropyGuru on Mar 04, '04 12:12:45PM

Excel has a nice feature that can convert any data (not just web tables) in a single column to separate columns. Use: Data-->Text to columns....
You can then choose how the data is delimited and in a few clicks, you'll have your data in separate columns.

If you're a mainly Safari user such as myself, this method migh be faster than loading up another browser and doing the cut and paste again.

Note: I know this works with Excel v.X, but I don't know about older verisons.



[ Reply to This | # ]
Correctly copy and paste tables from the web into Excel
Authored by: lnzdingo on Mar 04, '04 12:39:48PM

To make your life even easier, you can customize a toolbar to include that command as a feature.

If however, you do the same thing to the same page frequently, create a Query for it. Here is a basic web query:
WEB
1
http://www.cluelessmailers.org/index.html#blacklist

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

replace the url with the url of the page you want the data from
save the block into a textfile of any name in /Applications/Microsoft Office X/Office/Queries, and it will be available in the saved external query area under the Data menu
this is an old query of mine, that used to return a list of blacklisted spam houses. it doesn't do that anymore.

I don't really know what the extra options are that you can pass to the web query engine, but i haven't needed them before.



[ Reply to This | # ]
Excel Web Query Creation instructions
Authored by: lnzdingo on Mar 04, '04 01:58:57PM

took some diggin, but:
http://support.microsoft.com/default.aspx?scid=kb;en-us;178870

explains what all the lines should have on them, how to POST data in your query, and all other formatting options



[ Reply to This | # ]
Correctly copy and paste tables from the web into Excel
Authored by: chocky on Mar 04, '04 02:03:31PM

I tried the suggestion using Firefox and still wound up having everything pasted into a single column. But I did find a solution.
Choose "View Source" from within any browser. Select everything from the appropriate <table> tag all the matching </table> tag and past that into Excel. Bingo. Works from all the browsers I tried, even Safari.



[ Reply to This | # ]
Correctly copy and paste tables from the web into Excel
Authored by: bignumbers on Mar 05, '04 04:50:18PM

Thie method (viewing the source then copy/paste into Excel) is the method I've been using for quite a while. I'm pretty sure it's worked since Excel 2001 on OS 9. I use it all the time from any browser into Excel v.X.



[ Reply to This | # ]