L
Logical Paradox
PROBLEM:
I have an HTML form on a webpage that submits (posts) the form data results
to me via email. I now have hundreds of emails (.eml files from Windows Live
Mail) in a folder on my desktop that contain newsletter signup data from
people wanting to join my mailing list. These are names, email addresses,
phone numbers, and some check box information. The data is in the body of the
messages in the following format:
Field 1: User Entry 1
Field 2: User Entry 2
Field 3: User Entry 3
SOLUTION NEEDED:
I need a way to automate importation of the data from the text inside these
emails into an Excel (2007) spreadsheet. This needs to be automatic, to
format the data properly, and to work for multiple files in a directory (or
it could work for one file at a time as long as it is as simple as clicking a
button for each one). I also would like this to be formatted so that each
entry is a ROW, and each field is a COLUMN (so that you can scroll DOWN
through the various entries rather than ACROSS). The last point is less
important, but worth mentioning since everything I've tried so far also seems
to import the data the opposite way (with entries in columns and fields in
rows).
DETAILS:
I'm competent in Excel, but I'm no expert by any means. I hoped there was an
easy and obvious way to do this and I've tried many things, but so far I've
had no luck. I can import them one by one using the "data > import from text"
command. But this only imports one email at a time; Excel does not allow
importing multiple text files or a directory full of text files at once. I
have hundreds of these to import, so this would be tedious. I tried recording
a macro to automate this process, but the resulting macro simply imports the
same email files over and over rather than moving through them. I can't get
it to import all the email files in a folder or to import them in bulk. I
need a "one click" solution since I not only have many of these, but will
continue getting them on a regular basis as people sign up for the
newsletter. I need something automatic where I set it up, run it, and the
data gets stripped out, imported to the appropriate cells, and I'm done.
Also, this method imports the data along a horizontal axis, with each entry
as a COLUMN and the data headers for each field as ROWS, when I really would
like it to be the opposite (which each entry as a row and the headers for
each field as a column). This is a smaller point, since just getting the data
into Excel is my main problem right now, but it's worth mentioning. Perhaps
there is a way to manipulate the data and rotate a worksheet's cells 180
degrees so to speak? If anyone knows of a way to do this, please enlighten me.
I've tried looking for software or instructions to do this, but have not yet
had any luck. I am not in any way a script or macro writer, by the way, so
these are not good options for me unless they come with specific
instructions. I am hoping there is a simple and quick way to make short work
of this task. There should be, since it seems like this would be a common
problem!
I would appreciate some help from an Excel guru more learned than I! Thanks
guys!
I have an HTML form on a webpage that submits (posts) the form data results
to me via email. I now have hundreds of emails (.eml files from Windows Live
Mail) in a folder on my desktop that contain newsletter signup data from
people wanting to join my mailing list. These are names, email addresses,
phone numbers, and some check box information. The data is in the body of the
messages in the following format:
Field 1: User Entry 1
Field 2: User Entry 2
Field 3: User Entry 3
SOLUTION NEEDED:
I need a way to automate importation of the data from the text inside these
emails into an Excel (2007) spreadsheet. This needs to be automatic, to
format the data properly, and to work for multiple files in a directory (or
it could work for one file at a time as long as it is as simple as clicking a
button for each one). I also would like this to be formatted so that each
entry is a ROW, and each field is a COLUMN (so that you can scroll DOWN
through the various entries rather than ACROSS). The last point is less
important, but worth mentioning since everything I've tried so far also seems
to import the data the opposite way (with entries in columns and fields in
rows).
DETAILS:
I'm competent in Excel, but I'm no expert by any means. I hoped there was an
easy and obvious way to do this and I've tried many things, but so far I've
had no luck. I can import them one by one using the "data > import from text"
command. But this only imports one email at a time; Excel does not allow
importing multiple text files or a directory full of text files at once. I
have hundreds of these to import, so this would be tedious. I tried recording
a macro to automate this process, but the resulting macro simply imports the
same email files over and over rather than moving through them. I can't get
it to import all the email files in a folder or to import them in bulk. I
need a "one click" solution since I not only have many of these, but will
continue getting them on a regular basis as people sign up for the
newsletter. I need something automatic where I set it up, run it, and the
data gets stripped out, imported to the appropriate cells, and I'm done.
Also, this method imports the data along a horizontal axis, with each entry
as a COLUMN and the data headers for each field as ROWS, when I really would
like it to be the opposite (which each entry as a row and the headers for
each field as a column). This is a smaller point, since just getting the data
into Excel is my main problem right now, but it's worth mentioning. Perhaps
there is a way to manipulate the data and rotate a worksheet's cells 180
degrees so to speak? If anyone knows of a way to do this, please enlighten me.
I've tried looking for software or instructions to do this, but have not yet
had any luck. I am not in any way a script or macro writer, by the way, so
these are not good options for me unless they come with specific
instructions. I am hoping there is a simple and quick way to make short work
of this task. There should be, since it seems like this would be a common
problem!
I would appreciate some help from an Excel guru more learned than I! Thanks
guys!