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

Correctly copy and paste tables from the web into Excel Web Browsers
Every now and again, I want to transfer a table from the web (such as a list of figures from the UN Forestry database) into an Excel worksheet so I can do further calculations. On my previous Windows PC, I could just select the table, copy, and paste into Excel. With my new Mac, I was disappointed to find that copying a table in Safari and pasting it into Excel puts all the data in a single column.

The solution is to use the Firefox browser. This cuts and pastes tables as expected, saving potentially hours of typing or reformatting.

[robg adds: I do quite a bit of this, and Safari definitely doesn't do a good job at it. I decided to test the rest of the browsers that I have lying around, and found that OmniWeb5 Beta (also using Apple's WebCore) and Opera do not paste "correctly," while Camino, Mozilla, iCab, and Internet Explorer paste the data into individual cells in Excel, as one would expect.]
    •    
  • Currently 3.00 / 5
  • 1
  • 2
  • 3
  • 4
  • 5
  (2 votes cast)
 
[55,024 views]  

Correctly copy and paste tables from the web into Excel | 14 comments | Create New Account
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 | # ]
Correctly copy and paste tables from the web into Excel
Authored by: Kimrey on Mar 04, '04 01:50:55PM

Thanks for this post. I was copying some stuff from my bank site the other day and ended up using the Windows PC because this was driving me nuts. I thought it was an Excel for Mac issue and not a browser issue.

Thanks again.

---
Tim



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

There's another way. I've always accomplished this by saving the page as .html. Open Excel and then do a "File Open", open the .html file (you may have to change it so that you can "View All Files.") The table will show up in the page, you'll probably want to delete out all the other junk, but this has always worked for me. Handy if Safari is your primary browser (although FireFox is coming on STRONG.)



[ Reply to This | # ]
Correctly copy and paste tables from the web into Excel
Authored by: peterjhill on Mar 04, '04 04:28:47PM

I want to figure out how to copy some cells from excel into mail.app without it pasting it as a graphic. WTF is up with that Microsoft?



[ Reply to This | # ]
It's all about NEWLINE replacement...
Authored by: marook on Mar 04, '04 06:05:38PM

The thing happening is that in the copy process, WebKit adds RETURN or NEWLINE instead of TABs.
Excel expect TABS.
I do this in BBEdit, if needed:
1: Past in new window.
2: Find and replace all Double Returns "\r\r" with "$r"
3: Find and replace all Single Returns "\r" with a TAB: "\t"
4: Find and replace all $r with Returns: "\r"

Takes about 10 sec, and your done!

The trick here is that a line has Single Returns between cells, but double Returns at the 'end of line'.

Hope it helps...

---
/Marook



[ Reply to This | # ]
one step less
Authored by: ali baba on Mar 06, '04 08:57:08AM

1: Past in new window.
2: Find and replace all Returns "\r" with a TAB: "\t"
3: Find and replace all Double Tabs "\t\t" with a Return: "\r"

sorry if i'm nitpicking



[ Reply to This | # ]
one step less
Authored by: jmat@mac.om on Mar 08, '04 04:45:19PM

I think that will cause problems if a cell is empty. You'll end up with split lines at the empty cell.



[ Reply to This | # ]
Tex-Edit Plus in between
Authored by: ali baba on Mar 06, '04 08:41:47AM
first thanks for the hint tamc!

i couldn't get the Data-->Text to columns...." method mentioned by EntropyGuru to work so i tried to prepare the copied data in Tex-Edit Plus.. by the time i was done i saw marook's method of preparing in BBEdit. i ‘ll post the Tex-Edit Plus way anyway since many use it and in Tex-Edit Plus you can make a script of it.. maybe in BB too.. open Script Editor and paste the below in a new window:


tell application "Tex-Edit Plus"
     if exists window 1 then tell window 1
          replace window 1 looking for "^c" replacing with "^t"
          replace window 1 looking for "^t^t" replacing with "^c"
     end tell
end tell
and save this as script inside the Tex-Edit Plus scripts folder

[ Reply to This | # ]
Tex-Edit Plus in between
Authored by: ali baba on Mar 07, '04 07:28:42AM

hrmpf - too bad this method doesn't work when data inside a cell contains breaks or is (auto)wrapped by the cell-width.. chocky's method of pasting html-table-data works best, it's even more reliable than pasting from explorer, since explorer puts data separated by a break in another cell as well



[ Reply to This | # ]
Save as .html, open with Word and Copy to Excel
Authored by: milhouse on Mar 08, '04 07:29:07PM

Saving the file as .html, opening with word and then dragging or pasting the table into excel works flawlessly. I just used this method for some tax related stuff this weekend.

If you own office it's a nice timesaver as no formatting is lost.

HTH



[ Reply to This | # ]