importing essay data into an Excel file

I seem to have forgotten how to import my essay data in the .txt files into Excel (or SPSS) for that matter so that it is in a usable format. It seems that all the fields (e.g., participant number, condition, etc.) are delimited by single quotes and commas, except for the typed response itself. Using comma-delimiting doesn't seem to work because some of my participants used commas in their essays. How to I import the files into Excel, so that there is only one line for each participant and one column for each field?

That's a good question. I don't know if there actually IS a way short of writing an Excel macro. The essay data were written that way (as separate files) in the first place because SPSS and Excel could not accommodate such (potentially) a large number of characters in one field/cell. Newer versions however can I think. Perhaps we ought to allow for an [I]option[/I] to have the essay data written directly to the files now. Or maybe there is a simple way and I'm just forgetting. Personally, if it were me, I'd look into writing an Excel macro if you have a version of Excel that can handle stuffing that much data into its cells. If you (or anyone) has any luck with that, please post it here!!

when I try to open the .txt files, Excel has no problem sorting the initial fields into columns because they are delimited by commas (and because the are "bracketed" by single quotes). The problem is that p's often use commas in their responses, so it throws Excel off. Is there a way to get Medialab to use a less common delimiter in essay .txt files, like a semi-colon?

In general, it would be great if the essay output had all the intial fields and the essay response all on one line and with a unique delimiter. I think Excell would have no problem reading and sorting the data this way.

Would you be so kind as to attach an illustrative sample essay .txt file? I'll open it in Excel and try to replicate--perhaps it will induce an empathic sadness, an "aha!" reaction, and maybe--just maybe--a handy if not ingenious solution! Or not. Who knows. Couldn't hurt, no?

Sure. Here is text from a control condition file where p's wrote about their morning routine. Notice the commas and single quotes for the first fields, but nothing for the typed responses.

'19', '3', 'C:\HAYLEY\Fall 2009\Hayley.exp', 'default.mlp', '11/17/2009', '16:22:37'
Normally, I wake up about an hour or so before my class. But, I have had a lot of emotional strain this past week with little time for rest, so this week, I've found myself waking up ten minutes before a class, dressing, and leaving without breakfast. I always walk to class, for all of them are in the Quad. I have had a lot to worry about this past week, so I look forward to a relaxing time off next week.
'20', '3', 'C:\HAYLEY\Fall 2009\Hayley.exp', 'default.mlp', '11/18/2009', '16:16:27'
I wake up at about 8:30 in the morning. I usually just brush my teeth, change my clothes and get my backpack ready for whatever classes I have. I then proceed to class. I usually get breakfast in between my classes. I then go back to my room and relax for a little before finishing out my day in school.
'33', '4', 'C:\HAYLEY\Fall 2009\Hayley.exp', 'default.mlp', '11/18/2009', '17:27:55'
Every morning I wake up, make my bed, and take a shower. I usually listen to music and pray while I put on my makeup to get me started for a good day. I like to eat cereal or oatmeal if I have enough time. I always check my backpack to make sure I have everything I need for class. I then leave with one of my roommates, Cecilia, to walk to class since we have classes starting at the same time. On days I feel especially motivated, I will wake up 2 hours before my first class so I can get an extra hour of homework in. I always wake up at least an hour before my first class to ensure I'm not rushed in the morning.

I see. Hmm. First off I'll add this to our to-do list in terms of adding an option to use different delimiters. For now perhaps this could be a work around..

1. open the file in WordPad (or whatever text editor you like)
2. search and replace ', with '~ (or other delimiter of your choice)

Then open in Excel or SPSS requesting that it use the new new delimiter for defining columns. The only snag I'm not sure about is the time value--I notice there is no comma between it and the open ended response. I'm wondering if it will then get read in as part of the essay.

If so. then maybe you could open the file in Word and replace ', with '<p> where <p> I believe indicates a hard return. Then do a replace of '<p> with '~ (or whatever delimiter). That way you would catch the time variable as well. But as I said, I don't know if that would be an issue.

Whatever you do, if you find something that works, please let me know!

Thanks much. I'll keep you posted.

I can't shake this nagging feeling that older versions of Medialab didn't have this problem and that I was able to just open up the text files in Excel. I might be imagining this of course :)