importing data from email to Access2000

[tgwarrior]

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

 

[MacDermott]

Not sure about that "of course".
What would you want to do with that data that can't be done in this format?
(Crosstab queries can be very useful with data like this.)

Short of writing a routine which parses every line and enters it into a
table, any built-in import utility will bring your data into a table in the
same format you see in the external data. You'll probably want to make this
a temporary table, then run a separate routine to add the data from this
temporary table to your permanent table. (for one thing, you'll want to add
some sort of Primary Key)

HTH

 

[DevalilaJohn]

You can probably do what you want, but considering that you have very widely
differing numbers of answers, you will be getting some strange looking
records. You'd be better off setting up a table consisting of:
Survey #
Question
Answer

With the data in that form, you can do a make table query if you really want
to recreate each reply, while retaining a lot more flexibility to work with
your data.

Assuming that your eMails are in a consistent form, the code to import the
data should not be difficult to write.