HTML text to Office table

[msmemory_archive]

O mighty intarweb: Is there a quick way to take data that are in html, laid out visually like a table but actually simple text, and import it into a Word or Excel table, with the data properly separated into table columns? I know several brute force ways, but am I missing a friendly little tool that'll do it quick?

ETA: I'm trying to take this data (http://op.eastkingdom.org/Awards/Pelican.html and Laurel.html and Chivalry.html) and put it into a single grand date-sortable table. I figure this is a nice little project for a snowy Saturday morning.

Comments

well...

[capt-elias.livejournal.com]

I do this all the time...
I just click drag to sleect the table cells

and then paste it right into Word.

It works a treat for me.

Re: well...

[capt-elias.livejournal.com]

Whoops I did not read through... my apologies...

Its just text, right?
Spaces format the text?

I copy the text to a notpad.
then I perform the following replacements:
replace all (space)(space) with (tab)
replace all (tab)(tab) with (tab)
replace all (tab)(space) with (tab)
(ctrl-h brings up the edit-replace dialog in Note pad)

then copy paste it into EXCel

It works in most cases.
You could probably write a word macro that does this but why bother unless you have to do this all the time?

Re: well...

[capt-elias.livejournal.com]

Looked at the page you referred to...
All you have to do is copy it to Notepad, replace all double spaces with a tab, copy it and paste it right into an EXcel table.
I just did that, and it worked great.

[oakleaf-mirror.livejournal.com]

Partly depends how the data come out of the HTML. Excel should be able to import both comma and tab separated lists. If cutting and pasting the HTML leaves distinct markers between the columns, it should be trivial to filter that into commas or tabs, if those aren't already there.

[mikekn]

I do this with Excel a lot. Select the table in your browser, copy, go to Excel and select paste special... HTML.

[capt-elias.livejournal.com]

You guys missed that it is not an HTML Table... (so did I in my first post...)

[mayica.livejournal.com]

Here's how I do it:
Save the file. Open it in Word (you need to look at "All Documents," not jus "ALl readable docs") and Save as.. a text file. CLose the file, and open it in Excel. Tell the importer that it's a fixed width file, and it'll work.

This worked 'cause the first field, date, was always the same width. It's a lot more of a pain if that's not true.

[dryfoo.livejournal.com]

I do this all the time now. I use Emacs (since I have that downloaded and installed on chip-sets in my fingers) and create on-the-fly macros to produce Delimited text files. Some advanced tricks include:

-- having three undefined but named macros (m0, m1, m2) already assigned as keystroke commands and available for on-the-fly redefinition

-- using the pipe '|' as field delimited, since it's hardly ever used in the kind of datafiles you're talking about. Of course you have to search and test first.

Then Excel has a command to read in text files. Make sure to select *all* the fields and define them as Text (not "General") at the appropriate step, or various kinds of data will get effed up.

[dryfoo.livejournal.com]

Actually, you know what, it's easier to just do it than to try to explain it if I can't show you in person. So if you go to my website dryfoo.com, there's a file jwaks-awards.xls which is all done for you. You'll have to find something else for your snowy Saturday, I guess, but it took me, like maybe five minutes, and you shouldn't have to muck around for a couple of hours on it.

-- G

[msmemory.livejournal.com]

Thank you to both you and metahacker, I now have two copies in Excel! I guess I'll just bake banana bread and curl up with hot coffee in the morning.

[goldenstag.livejournal.com]

If you have the HTML file, can't you just open it in Word? Once it is in Word, try File, Save As ... and change the file format.