|
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
ship wrote:
How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? Try the "Edit | Paste Special" menu, not pasting with a keystroke |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Doesnt work!
Either you paste HTML, or you lose the columns Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take thedata & columns? (but NOT the formatting & URLs!)
Paste into a helper worksheet that will preserve the formatting and the columns.
Then copy from there, but paste special|values to its rightful home. ship wrote: Doesnt work! Either you paste HTML, or you lose the columns Ship Shiperton Henethe -- Dave Peterson |
How can I cut data out of HTML table, into msExcel and just take thedata & columns? (but NOT the formatting & URLs!)
You have another suggestion at your other thread.
ship wrote: Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe -- Dave Peterson |
How can I cut data out of HTML table, into msExcel and just take thedata & columns? (but NOT the formatting & URLs!)
Oops. Never mind.
Dave Peterson wrote: You have another suggestion at your other thread. ship wrote: Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe -- Dave Peterson -- Dave Peterson |
Quote:
|
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Dave
I dont follow your drift! A) What is a "helper worksheet" B) Where is your thread - I dont seem to be able to see it for some reason... Ship Shiperton Henethe Dave Peterson wrote: Oops. Never mind. Dave Peterson wrote: You have another suggestion at your other thread. ship wrote: Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe -- Dave Peterson -- Dave Peterson |
How can I cut data out of HTML table, into msExcel and just take thedata & columns? (but NOT the formatting & URLs!)
Insert a new worksheet (just temporary)
Paste there copy from that new worksheet go to the real worksheet paste special|values delete that temporary worksheet I didn't notice that you cross posted. That's what my oops was for. ship wrote: Dave I dont follow your drift! A) What is a "helper worksheet" B) Where is your thread - I dont seem to be able to see it for some reason... Ship Shiperton Henethe Dave Peterson wrote: Oops. Never mind. Dave Peterson wrote: You have another suggestion at your other thread. ship wrote: Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe -- Dave Peterson -- Dave Peterson -- Dave Peterson |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Ah Yes that's quite clever - why did I think of that - I'll try it.
(Though why the heck Micro$oft couldnt give us better cleaner pasting options is a good point - just the sort of thing that generates MASSIVE bad will against them...!) Thanks. Meanwhile I now have the reverse problem I need to paste OUT of excel and into Dreamweaver without any formatting, table column widths etc... I have to do this operation quite a lot and for some time now I've been saving as a CSV file and then reopening - but there must a better way! Maybe some web-friendly plug-in or something, no? Any thoughts? Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
P.S. Ah sorry yes one problem is that I need the currency
formatting to stay put! e.g. "£1,000.50" needs to stay visible in the HTML exactly like that and not get turned into "1000.5" ! Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
I use a free utility, NoteTab light. I copy from Excel and paste into
NoteTab light. Then I have it convert the ^t (tabs) to </td<td. Finally, I add the new row info in between the lines (</tr<tr). Not bad for 10 rows, but I'm eager to hear what others use to do this operation more efficiently. BTW, NoteTab can take the markup and data (from View Source), and from the Modify - Strip HTML tags, do just that. -Rajah ship wrote: Ah Yes that's quite clever - why did I think of that - I'll try it. (Though why the heck Micro$oft couldnt give us better cleaner pasting options is a good point - just the sort of thing that generates MASSIVE bad will against them...!) Thanks. Meanwhile I now have the reverse problem I need to paste OUT of excel and into Dreamweaver without any formatting, table column widths etc... I have to do this operation quite a lot and for some time now I've been saving as a CSV file and then reopening - but there must a better way! Maybe some web-friendly plug-in or something, no? Any thoughts? Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take thedata & columns? (but NOT the formatting & URLs!)
No extra thoughts from me.
ship wrote: Ah Yes that's quite clever - why did I think of that - I'll try it. (Though why the heck Micro$oft couldnt give us better cleaner pasting options is a good point - just the sort of thing that generates MASSIVE bad will against them...!) Thanks. Meanwhile I now have the reverse problem I need to paste OUT of excel and into Dreamweaver without any formatting, table column widths etc... I have to do this operation quite a lot and for some time now I've been saving as a CSV file and then reopening - but there must a better way! Maybe some web-friendly plug-in or something, no? Any thoughts? Ship Shiperton Henethe -- Dave Peterson |
How can I cut data out of HTML table, into msExcel and just take thedata & columns? (but NOT the formatting & URLs!)
I think I'd apply the formatting after the pasting. I'm not sure how you can do
a single paste and sometimes copy formats and sometimes not. ship wrote: P.S. Ah sorry yes one problem is that I need the currency formatting to stay put! e.g. "£1,000.50" needs to stay visible in the HTML exactly like that and not get turned into "1000.5" ! Ship Shiperton Henethe -- Dave Peterson |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Sounds like far too many clicks! I think there used to be some microsoft plug in for Offfice 2000 (??) but not sure about 2003 which I am now using. (And Dreamweaver MX 2004, fwiw) Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take thedata & columns? (but NOT the formatting & URLs!)
No, I haven't.
Maybe Ship will want to. Dave H wrote: Dave Peterson Wrote: Paste into a helper worksheet that will preserve the formatting and the columns. Then copy from there, but paste special|values to its rightful home. ship wrote: Doesnt work! Either you paste HTML, or you lose the columns Ship Shiperton Henethe -- Dave Peterson Have you tried a web query under data/import external data? -- Dave H -- Dave Peterson |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Fleeing from the madness of the jungle
Dave Peterson stumbled into news:alt.http://www.webmaster,microsoft.publi...lic.excel.misc and said: I think I'd apply the formatting after the pasting. I'm not sure how you can do a single paste and sometimes copy formats and sometimes not. If this is a repetitive task, I'd suggest a little scripting (VBA seems appropriate) would tick the boxes nicely. -- William Tasso http://williamtasso.com/words/what-is-usenet.asp |
How can I cut data out of HTML table, into msExcel and just take thedata & columns? (but NOT the formatting & URLs!)
That seems like a reasonable approach. Maybe the OP will try it.
William Tasso wrote: Fleeing from the madness of the jungle Dave Peterson stumbled into news:alt.http://www.webmaster,microsoft.publi...lic.excel.misc and said: I think I'd apply the formatting after the pasting. I'm not sure how you can do a single paste and sometimes copy formats and sometimes not. If this is a repetitive task, I'd suggest a little scripting (VBA seems appropriate) would tick the boxes nicely. -- William Tasso http://williamtasso.com/words/what-is-usenet.asp -- Dave Peterson |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
This removes formatting so it won't help your currency need, but
anyway: Sub PasteHtml() ' ' Keyboard Shortcut: Ctrl+Q ' PasteSpecial(Format, Link, DisplayAsIcon, IconFileName, IconIndex, IconLabel, NoHTMLFormatting) ActiveSheet.PasteSpecial NoHTMLFormatting:=True End Sub |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Like you say it wont help the currency requirement
but might be helpful on occassion. But please can you tell me how & where exactly I need insert this bit of code. Is it an ms Excel macro? If so how do I get it inserted & working whenever I open msExcel....? Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
It might be easier to record a macro and then change it.
Tools Macro Record New Macro Name PasteHTML Shortcut Ctrl+Shift+V (or some other key you don't already use) Store Macro in: Personal Macro Workbook Click OK Switch to a web page and copy some text Switch back to Excel Edit Paste Special Text Click Stop Recording on the macro toolbar Window Unhide Personal.xls Tools macros macros Select PasteHTML, click Edit change the line to read ActiveSheet.PasteSpecial NoHTMLFormatting:=True File Close and return to Excel Window Hide File Exit and save changes to Personal.xls I'd also recommend a good book on learning VBA such as John Walkenbach's, see http://www.sysmod.com/spreads.htm#Books Good luck, Patrick |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi Nice try - except that you slightly miss the point. The problem is that as things stand it has to be Pasted "AS HTML" (i.e. not "as text") otherwise msExcel loses the table structure! And of course if you paste a large table "as HTML" into a spreadsheet it takes all the stupid formatting including all the URLs etc... and this will at best massively slow up msExcel - but at worse actually crash it! Conclusion Yes may be one could some macro way of doing it involving saving as .CSV closing and reopening but that strikes me as EXTREMELY MESSY...! Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
P.S.
Plus it often goes wrong! When you paste HTML into a spread sheet you sometimes get a table cell throwing a carriage return, with the result that the data in excel ends up on TWO (rather than just one) lines. Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
P.S.
Plus it often goes wrong! When you paste HTML into a spread sheet you sometimes get a table cell throwing a carriage return, with the result that the data in excel ends up on TWO (rather than just one) lines. Ship Shiperton Henethe |
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
P.S.
Plus it often goes wrong! When you paste HTML into a spread sheet you sometimes get a table cell throwing a carriage return, with the result that the data in excel ends up on TWO (rather than just one) lines. Ship Shiperton Henethe |
| All times are GMT +1. The time now is 08:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com