importing emailed data into Access2000

[michael munson]

I have approximately 200 emails that contain survey data in the format
of FIELDNAME: answer.

I'd like to import the surveys from the emails (or .txt files) into
Access2000. Ideally, of course, I'd like to have each email/recordset
entered into its own record and have the field name *not* be imported
as part of the entered text, but rather the data imported into the
field name that is just before the answer. (See data below.)

Is there a way to import this kind of data? Not every survey
respondant answered all the questions, so some surveys have only 10
answers (fieldname:answer), while others have 90 answers. So, it's
obviously important the the answers are imported into the correct
fields in the database. All emailed surveys are from a webform, so
the fieldnames are consistent throughout all responses.

Ideas? Thank you. Sample data below.

michael munson
(e-mail address removed)
---------------------------------
1a-noaffect: checkbox
2f-committed: checkbox
2g-ltr: checkbox
3a-yes: checkbox
3b-yes: checkbox
3c-no: checkbox
3d-yes: checkbox
3e-yes: checkbox
3f-yes: checkbox
4a-chest: checkbox
5d-front: checkbox
5h-comments: I have MS

 

[Mike Labosh]

I have approximately 200 emails that contain survey data in the format

of FIELDNAME: answer.

I feel your pain. I am working on a similar system where respondent answers
are sent to us in data files like this: FieldName=Value;FieldName=Value;

q01=His Answer;q02=His Answer;q03=his really long answer with carriage
returns embedded semicolons (grrr) and everything;q04=you get the idea.;

Access never heard of stupid file formats like this. You're going to have
to roll up your sleeves and write some pretty hefty VBA file-i/o and string
munching code.

Your best bet to begin your algorithm is to read the file into a big fat
string and then loop on the Instr() position of vbCrLf so you can get the
name-value pairs, and for each pair, get the Instr() position of the ": ".
If you're lucky, you can use the name part for the column name in your
database. Otherwise you get to play with the same name/column mapping
voodoo I've had to conjure up.

I bet the system that generates your data is in UNIX, too. There is
something really disturbing about UNIX coders that seem to always format
data files like that.
--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"