Help me sort out my surveys please!

November 3, 2007 1:14 PM

i have a whole bunch of plain text emails that I would like to amalgamate into one spreadsheet. Is this possible - easily?

I have collected some survey data through a simple php script and collected a whole bunch of email answers. Unfortunately (or fortunately depending upon how you look at it) I have far more survey responses than I thought I would get.

Can I somehow automate the process of entering all of this data into an Excel spreadsheet?

posted by chairish to computers & internet (6 answers total)

If you're comfortable with simple scripting, then:

1. Convert them all into one big comma-separated values file.
2. Name that file with the extension "csv".
3. Open it with Excel.
4. If desired, save it in Excel's native format (this is not really necessary, but will allow you to use more of Excel's functionality).

In the first step, make sure that you follow the rules for quoting values properly (to make sure that embedded commas and such aren't interpreted as separators). A general overview can be found here.
posted by Flunkie at 1:23 PM on November 3, 2007

scripting is the way forward for dealing with txt files. After that is just depends on your language of choice. The main 3 are php, vbscript and perl. personally i use vbscript for this kind of thing. such a script is pretty simple to write if all your input data is the same format. a loop that reads the data and rearranges it into a delimited csv format, it doesnt have to be commas if for instance if your data contains commas. iirc you can specify what the delimter is when you import it into excel.
posted by browolf at 3:16 PM on November 3, 2007

I don't know how to write a script.

How do I get "comfortable with simple scripting"? Any pointers where to go?
posted by chairish at 3:57 PM on November 3, 2007

Could you post an example of one or two of the emails?
posted by delmoi at 5:08 PM on November 3, 2007

it doesnt have to be commas if for instance if your data contains commas. iirc you can specify what the delimter is when you import it into excel.

That's asking for trouble. Somewhere in one of those emails may be a pipe character, or a backslash, or an asterisk, or whatever you specify.

Moreover, it really has to be quoted anyway, in order to get these emails (which almost certainly) contain newline characters into a single field apiece.

You might as well just do it right.
posted by Flunkie at 6:41 PM on November 3, 2007

How do I get "comfortable with simple scripting"? Any pointers where to go?

Well, if you want to take the time to learn a scripting language, I would suggest Ruby.

But it's not necessarily trivial to pick up programming from scratch. It might come naturally to you, but it might not. You might have better luck continuing to search for something prebuilt.
posted by Flunkie at 6:43 PM on November 3, 2007