Anybody?
I tried copying and pasting the theme camp info from the website. But excel insists on each website line of text being a separate cell in excel. Too many camps to straighten that out manually.
Any techy types no how to get the info into a spreadsheet or have access to the BMorg who must have it already?
My goal is to have the description info from the website in an individual cell...
Thanks for any help!
Igneouss
Looking for Theme Camp Guide in excel
-
swampdog
- Posts: 917
- Joined: Thu Jul 22, 2004 8:27 am
- Burning Since: 2004
- Camp Name: Rising Arms Pub
- Location: Bellingham WA
This SHOULD work
I think it's do-able but kind of tedious. The problem is that the postings aren't consistent, where the description is broken into paragraphs you get different numbers of lines.
The simple but tedious way is to cut and paste from all the pages such as this http://burningman.com/themecamps/09_camp_vill_p.html into excel. When I do that I get
Camp name
Description (possibly multiple lines)
hometown
url (or not)
contact (or not)
on separate lines for each camp.
You can pretty easily make a template where for instance if A1 is camp name, B1 = description (from A2), C1 = hometown (from A3) etc.
The hard hard part here is cleaning up the source. You need to either come up with some clever bit of text matching to see for instance "is this next line a description line or a hometown?" or go through manually and make sure that each camp fits the template by merging (manually - cut and paste 2d cell contents onto end of 1st cell contents) or adding blank lines (contact line = "N/A")
If you do that (I think I figured out a way to do most of it in code in '05, I'll see if I can find it and figure out what I did), you can do the mapping I described and you'll come out with
Camp Name |Description | hometown | ...etc
description
hometown
...
ok, 2 problems here. First, the "everything together on one line" line doesn't actually contain the description text but a pointer to (eg) cell#a2.
Select the whole spreadsheet by clicking in the far upper left box (between the row list and the column list - on the corner between the letter A and the number 1).
Press Ctrl-C to copy the whole spreadsheet.
Click in cell A1
Go to edit-paste special. Click "Values" under paste - that'll replace the pointers with the actual text values.
Now you've got a bunch of long lines that you want (A1 thru E1 filled with values) and a bunch of short lines that you don't (B1 has description but nothing else).
Easy enough to sort the spreadsheet on column B. All the short lines with no value in column B will sort to the top or bottom and you can just delete them.
Ok, so now you want to match it up with the placements here?
http://www.burningman.com/themecamps/placement09.html
The trick with that is that you need to dump the placement listing into something besides Excel to get the line breaks right. Bring up your notepad editor and copy and paste directly from the web site to the notepad. This may look a little funky, but you should have tab characters between each value. Save it as a text (.txt) file and then go back to excel. Go to file/open and click on the "files of type" selector at the very bottom. Select text files (and the directory where you saved your file). Your file should show up. Click on it. You'll get a box with options of "delimited" or "fixed width". Your file is tab delimited (tabs imbedded between what will become columns) so select delimited here, then next. You'll have an option for "delimited by?" and select tab. Then next, next (or finish) and the list will show up in excel.
If you want to get really clever, you can make a lookup function in your first (descriptions) page to look up the placement coordinates in the second (placement) page.
This may sound complicated, but it's really pretty straightforward. Now that I'm thinking about it I may just do it - but if you beat me to it I won't cry.
Good luck!
The simple but tedious way is to cut and paste from all the pages such as this http://burningman.com/themecamps/09_camp_vill_p.html into excel. When I do that I get
Camp name
Description (possibly multiple lines)
hometown
url (or not)
contact (or not)
on separate lines for each camp.
You can pretty easily make a template where for instance if A1 is camp name, B1 = description (from A2), C1 = hometown (from A3) etc.
The hard hard part here is cleaning up the source. You need to either come up with some clever bit of text matching to see for instance "is this next line a description line or a hometown?" or go through manually and make sure that each camp fits the template by merging (manually - cut and paste 2d cell contents onto end of 1st cell contents) or adding blank lines (contact line = "N/A")
If you do that (I think I figured out a way to do most of it in code in '05, I'll see if I can find it and figure out what I did), you can do the mapping I described and you'll come out with
Camp Name |Description | hometown | ...etc
description
hometown
...
ok, 2 problems here. First, the "everything together on one line" line doesn't actually contain the description text but a pointer to (eg) cell#a2.
Select the whole spreadsheet by clicking in the far upper left box (between the row list and the column list - on the corner between the letter A and the number 1).
Press Ctrl-C to copy the whole spreadsheet.
Click in cell A1
Go to edit-paste special. Click "Values" under paste - that'll replace the pointers with the actual text values.
Now you've got a bunch of long lines that you want (A1 thru E1 filled with values) and a bunch of short lines that you don't (B1 has description but nothing else).
Easy enough to sort the spreadsheet on column B. All the short lines with no value in column B will sort to the top or bottom and you can just delete them.
Ok, so now you want to match it up with the placements here?
http://www.burningman.com/themecamps/placement09.html
The trick with that is that you need to dump the placement listing into something besides Excel to get the line breaks right. Bring up your notepad editor and copy and paste directly from the web site to the notepad. This may look a little funky, but you should have tab characters between each value. Save it as a text (.txt) file and then go back to excel. Go to file/open and click on the "files of type" selector at the very bottom. Select text files (and the directory where you saved your file). Your file should show up. Click on it. You'll get a box with options of "delimited" or "fixed width". Your file is tab delimited (tabs imbedded between what will become columns) so select delimited here, then next. You'll have an option for "delimited by?" and select tab. Then next, next (or finish) and the list will show up in excel.
If you want to get really clever, you can make a lookup function in your first (descriptions) page to look up the placement coordinates in the second (placement) page.
This may sound complicated, but it's really pretty straightforward. Now that I'm thinking about it I may just do it - but if you beat me to it I won't cry.
Good luck!
-
swampdog
- Posts: 917
- Joined: Thu Jul 22, 2004 8:27 am
- Burning Since: 2004
- Camp Name: Rising Arms Pub
- Location: Bellingham WA
I found the one I did in '05 (or maybe it was '04). PM me if you want a copy . I suspect it'll take some interpretation - "what the heck did I mean by that?" sort of thing, but it should be viable. Essentially the hometown, contact, and url lines are all coded with the name of what they are at the start (eg: "Hometown: Portland, OR"). So I was able to figure out - if the next line is a blank, that means it's the end of an entry. If it's not blank, and the last line was blank, it means it's the start of an entry. If the previous line was the first line of the entry and this line doesn't start with hometown, etc, then it's part of the description and needs to be concatenated with any other description lines. Yeah, messy. But it worked.
-
Bob
- Posts: 6747
- Joined: Wed Sep 03, 2003 10:00 am
- Burning Since: 1986
- Camp Name: Royaneh
- Location: San Francisco
- Contact:
Jesus Fucking Christ, clean it up in Word first or something.
Amazing desert structures & stuff: http://sites.google.com/site/potatotrap/
"Let us say I suggest you may be human." -- Reverend Mother Gaius Helen Mohiam
"Let us say I suggest you may be human." -- Reverend Mother Gaius Helen Mohiam
-
swampdog
- Posts: 917
- Joined: Thu Jul 22, 2004 8:27 am
- Burning Since: 2004
- Camp Name: Rising Arms Pub
- Location: Bellingham WA
Ok, (grumble grumble) I'll just do it then. There! That make you happy? I should have it done by tomorrow evening.
Seriously, the functions that I worked out in '05 seem to work just fine on this year's data. So I've got the first part done without having to rethink it. The second part is relatively easy.
Editing this stuff out by hand is just mind-numbing. You have to read it, line by line. Building functions to do it took a while to do the first time, but at least it was entertaining. Fortunately the second time is going to be free.
Seriously, the functions that I worked out in '05 seem to work just fine on this year's data. So I've got the first part done without having to rethink it. The second part is relatively easy.
Editing this stuff out by hand is just mind-numbing. You have to read it, line by line. Building functions to do it took a while to do the first time, but at least it was entertaining. Fortunately the second time is going to be free.
-
swampdog
- Posts: 917
- Joined: Thu Jul 22, 2004 8:27 am
- Burning Since: 2004
- Camp Name: Rising Arms Pub
- Location: Bellingham WA
k, try this
Here's the condensed version - columns for camp name, description, facing street & cross street, as well as clock street and ring road.
http://tinyurl.com/mmz4xr
Where the location shows up as N/A, excel couldn't find a match on the placement data for the description name. Probably some mistakes there - typed one way on one source and another way on the other.
This next one has all the raw data and a few manipulation steps along the way. If the first one doesn't give you the flexibility you need, feel free to look at this for other columns to play with. eg, camp size, placer, hometown, contact, and url. It's about 3MB
http://tinyurl.com/ksw8x5
If you sort this it might get ugly unless you do copy special/values only as described previously.
Comments welcome.
http://tinyurl.com/mmz4xr
Where the location shows up as N/A, excel couldn't find a match on the placement data for the description name. Probably some mistakes there - typed one way on one source and another way on the other.
This next one has all the raw data and a few manipulation steps along the way. If the first one doesn't give you the flexibility you need, feel free to look at this for other columns to play with. eg, camp size, placer, hometown, contact, and url. It's about 3MB
http://tinyurl.com/ksw8x5
If you sort this it might get ugly unless you do copy special/values only as described previously.
Comments welcome.
-
swampdog
- Posts: 917
- Joined: Thu Jul 22, 2004 8:27 am
- Burning Since: 2004
- Camp Name: Rising Arms Pub
- Location: Bellingham WA
Igneouss - I'll look you up!
I made a few improvements. The "Clock" and "Ring" columns weren't pulling from the right place, fixed it. I formatted it so that it should print out reasonably well on landscape - although it'll be 55 or more pages. Tidied up the headings, put a grid over the results so you can see how things relate a bit better.
I made a few improvements. The "Clock" and "Ring" columns weren't pulling from the right place, fixed it. I formatted it so that it should print out reasonably well on landscape - although it'll be 55 or more pages. Tidied up the headings, put a grid over the results so you can see how things relate a bit better.
I LOVE you for doing this. Come to Psychic Taxi for thanks!
And a Hug and a drink and a smile.
I did a list like this last year and it took me HOURS to format and match up. Thanks so much for sharing this!!
We create a handy notebook each year which has enabled us to help tons of lost people looking for their friends, loved ones and camps!
By the way, we will have 40' of hammocks and two psychic taxi rickshaws to ferry everyone around. Please come and visit us on the Evolution road, the exact location can be seen on this little maplet:
www.psychictaxi.com
Thanks!!
Drtygrl (Tres)
I did a list like this last year and it took me HOURS to format and match up. Thanks so much for sharing this!!
We create a handy notebook each year which has enabled us to help tons of lost people looking for their friends, loved ones and camps!
By the way, we will have 40' of hammocks and two psychic taxi rickshaws to ferry everyone around. Please come and visit us on the Evolution road, the exact location can be seen on this little maplet:
www.psychictaxi.com
Thanks!!
Drtygrl (Tres)