Outlook Email to Excel...

dwuensch · Feb 26th, 2002, 01:34 PM

Hello All,

I have an email box full of survey results from a HTML form. My problem is taking those individual emails and creating an Excel spreadsheet, as to make the data manageable.

The data is seperated in to the following format:

fname: joe
lname: schmoe

After many frustrating hours, I have yet to figure a way to get the data into a columnar format, much less deal with 150 individual email responses to a 40 question survey. I have tried manipulating the data through various exporting scenarios, and tried my best to understand how a pivot table might bail me out.

I would appreciate ANY insight in dealing with this mess!

Thank you!!!
David

Helen · Feb 26th, 2002, 02:48 PM

David,

You can open an HTML file in Excel, as I have just tried this out. This will then able you to format the data how you like.

I am a bit unsure how the data appears on the HTML file, but if you wanted to get the data into columns if it's in a long row etc then you could use Data/Text to columns to helo you.

It's very difficult to help you when I don't have a sample of the data to look at and fully understand what it is you require.

Helen

dwuensch · Feb 26th, 2002, 03:11 PM

Thanks for the response Helen-- The data I have is not in HTML format, but actually individual emails in Outlook. The farthest I am able to get them in some ledgible format, is by exporting the entire Outlook folder into a text file. From there, I am able to import it into Excel using some delimiters to seperate the information into columns. Below is an example of how the text might appear in Excel.

A B
fname: Joan
lname: Doe
company: Some Electronics
position: CFO
address1: 10901 Someplace
address2:
city: Tampa
State: Fl
country: USA
textfield: 33624
phone: (813)xxx-xxxx
email: Joan.Doe@mystie.com
www:
select_version: IVb

The problem is that the fields of the survey are in one column, when I want them in seperate columns. I guess the simplistic way of looking at this, is that I want to 'turn' the spreadsheet.

The complicating factor, is that there are 150 of these entries in one Excel Sheet, formatted into 2 columns. Is there a way for me to grab a certain range of cells, based on the content of the first column-- and 'turn' all of them, including the B column? Or maybe simply define the range, tell Excel to look in the B column, and populate a new sheet? I am so lost...

Again, thank you for your time and patience!
-David

dwuensch · Feb 26th, 2002, 03:13 PM

Helen,

Unfortunately, the BBS removed the spaces in my data example. The Field name is in the A column, while the corresponding information is in the B column.

Thanks again!
-David

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 26th, 2002, 01:34 PM	#1
dwuensch New Member Join Date: Feb 2002 Posts: 3	Hello All, I have an email box full of survey results from a HTML form. My problem is taking those individual emails and creating an Excel spreadsheet, as to make the data manageable. The data is seperated in to the following format: fname: joe lname: schmoe After many frustrating hours, I have yet to figure a way to get the data into a columnar format, much less deal with 150 individual email responses to a 40 question survey. I have tried manipulating the data through various exporting scenarios, and tried my best to understand how a pivot table might bail me out. I would appreciate ANY insight in dealing with this mess! Thank you!!! David

Feb 26th, 2002, 02:48 PM	#2
Helen Board Regular Join Date: Feb 2002 Posts: 103	David, You can open an HTML file in Excel, as I have just tried this out. This will then able you to format the data how you like. I am a bit unsure how the data appears on the HTML file, but if you wanted to get the data into columns if it's in a long row etc then you could use Data/Text to columns to helo you. It's very difficult to help you when I don't have a sample of the data to look at and fully understand what it is you require. Helen

Feb 26th, 2002, 03:11 PM	#3
dwuensch New Member Join Date: Feb 2002 Posts: 3	Thanks for the response Helen-- The data I have is not in HTML format, but actually individual emails in Outlook. The farthest I am able to get them in some ledgible format, is by exporting the entire Outlook folder into a text file. From there, I am able to import it into Excel using some delimiters to seperate the information into columns. Below is an example of how the text might appear in Excel. A B fname: Joan lname: Doe company: Some Electronics position: CFO address1: 10901 Someplace address2: city: Tampa State: Fl country: USA textfield: 33624 phone: (813)xxx-xxxx email: Joan.Doe@mystie.com www: select_version: IVb The problem is that the fields of the survey are in one column, when I want them in seperate columns. I guess the simplistic way of looking at this, is that I want to 'turn' the spreadsheet. The complicating factor, is that there are 150 of these entries in one Excel Sheet, formatted into 2 columns. Is there a way for me to grab a certain range of cells, based on the content of the first column-- and 'turn' all of them, including the B column? Or maybe simply define the range, tell Excel to look in the B column, and populate a new sheet? I am so lost... Again, thank you for your time and patience! -David

Feb 26th, 2002, 03:13 PM	#4
dwuensch New Member Join Date: Feb 2002 Posts: 3	Helen, Unfortunately, the BBS removed the spaces in my data example. The Field name is in the A column, while the corresponding information is in the B column. Thanks again! -David