March 8th, 2006

Filed under:
Movies
6 Comments »

LazyWeb Request: Tabulate My Oscar Contest

I’ve got a little problem which I haven’t been able to figure out. I received a bunch of entries for the Oscar Contest. There’s too many to manually go through. Unfortunately, the data in the emails I’ve received is pretty structureless. Here’s what it looks like:

Best Picture: Crash
Best Director: Ang Lee for Brokeback Mountain Best Actor: Philip Seymour Hoffman for Capote Best Actress: Reese Witherspoon for Walk the Line Best Supporting Actor: Jake Gyllenhaal for Brokeback Mountain Best Supporting Actress: Michelle Williams for Brokeback Mountain Best Original Screenplay: Crash - Paul Haggis Best Adapted Screenplay: Brokeback Mountain - Larry McMurtry
Cinematography: Brokeback Mountain
Editing: Munich
Art Direction: Memoirs of a Geisha
Costume Design: Charlie and the Chocolate Factory Original Score: Brokeback Mountain Original Song: “Travelin’ Thru” - Transamerica - Dolly Parton Best Sound: King Kong Best Visual Effects: King Kong Best Animated Feature Film: Wallace & Gromit in The Curse of the Were-Rabbit Best Foreign Language Film: Tsotsi (South Africa)

When I export the emails out of Outlook into Excel or Word, there is a seperator character or line break between each pick, so that’s good news. Unfortunately, when you export to Excel, it truncates the message body, so I can’t get all the results that way. I can export to a comma-separated values file (CSV), but that still leaves the body of the message, with all the picks, in one big blob of data.

Does anybody have any brilliant ideas on how I can quickly render this data in Excel, so that each entry gets a row to itself, and each pick appears in a separate cell? Once I’ve got that, I can do some basic Excel comparison stuff to get it to figure out who got the most correct answers.

UPDATE: Problem solved, thanks to a local braniac.

Comments: 6 Responses so far

gillian
March 8th, 2006
12:36 pm

Well someone could write a quick script to parse this data properly into an excel file (hell, I’ve done it enough at work). Or the lame way could be to use “:” as your field delimiter, and then later do a find/replace on all the “Best Director” and “Best Supporting Actress” in all the fields in the Excel file. Maybe there’s a better way for an Excel user to do it, but I only know open source Excel file hacks, as opposed to using the program itself.

[Reply]

gillian
March 8th, 2006
12:41 pm

… Of course, this depends on how the output you got deals with non-selections.

[Reply]

Antoin O Lachtnain
March 8th, 2006
4:29 pm

I’m not sure I understand the exact problem, but one quick-and-dirty way to solve this sort of problem can be to paste into word and run search and replace. Not sure if this will work for you in this case.

[Reply]

Darren
March 8th, 2006
4:36 pm

Antoin: Yeah, that’s how I usually do it. For various arcane reasons, it’s not that simple in this case. No worries, though, I’ve got somebody smart looking at it.

[Reply]

johnb
March 8th, 2006
5:21 pm

” …there is a seperator character …”

Seperator? Ah, yes, the old “Maschinelle Einrichtung aus Edelstahl für das Entfernen von Stoffen aus Flüssigkeiten. Siehe unter Zentrifuge” trick. You almost caught me, Darren!

[Reply]

Monique
March 9th, 2006
8:51 am

Fun. What you could do is export the data from Outlook and open it in a text editor so that you keep the line break separations between each entry, then save the file without an extension. Open the file in Excel and set : as the delimited character.

[Reply]

Add a comment