e-mail to database or spreadsheet

[bert113]

Can anyone help me....

 

I've got over a 10000 e-mails to go through all which have come in from a standard form, just the titles vary, I want to extract certain fields and import them into preferably a spreadsheet or a database.

 

Is there a freely available script which could do this (via outlook or windows mail etc..) , or am I going to have to purchase something.

 

At the moment I have all e-mails held as web-mail via horde

 

(this is for a football league website I run as a "hobby")

 

Any help or advice would be appreciated

[srochford]

Can you give an example of the content. I'd say it's unlikely that anything exists (free or paid for) which will do the job for you directly but it's the sort of thing which might be easy to script or use sed/awk/grep to work with, particularly if they're reasonably nicely formatted.

 

If you can read them with Outlook then I'd guess you can use VBA - it's possible to step through each message in a folder, get the content and "do stuff" with it. If not, I think you will need to get them into text files which you can process.

 

Next year it would be worth thinking about "what will I do with the data I collect" before you start :-) It would almost certainly be easier to have people fill in a simple web form which submits the results straight into a database but it's very easy for me to say that after the event ...

[bert113]

Thank you, I know.... I should have thought about it, all I want is three fields

 

The e-mails come in like this.... (all I need are the fields: other_team, player_of_the_match and sportsmanship)

 

submit: submit your results

 

date_played: 04/05/10

 

your_team: Norton Eagles

 

your_score: 1

 

other_team: Abbeymead Rovers

 

other_score: 3

 

home_away: Home

 

player_of_the_match: fred flintstone

 

sportsmanship: 10

 

referee: 10

 

player1: name1

 

player8: name2

 

player2: name3

 

player3: name4

 

player4: name5

 

player5: name6

 

player6: name7

 

player7: name8

Edited May 6, 2010 by bert113

[limbo]

If you are using Microsoft Outlook as your mail client and you have got a copy of Microsoft Access then you can set up an imported table in Access from Outlook folders and then set up queries / filters to extract the info.

 

For MSAccess 2007 you go to the external data on the ribbon, in the import section click on more - one of the options is Outlook Folders. This will take you through a wizard to get the data into a table.

 

You could then use the INSTR command to find specific data within the contents field of each message.

 

Might take a while to get the commands right to grab each piece of data, and a vb solution might be quicker, but it depends on what you are most comfortable with.

[keithu]

It's easy to do this in perl. If you have the emails in individual files just pipe them through this program and get the output in csv format. Let me know if you need any help.

 

while (<>) {
tr/,/ /;
$oteam = $1 if /^other_team: (.*)$/;
$pom = $1 if /^player_of_the_match: (.*)$/;
$sports = $1 if /^sportsmanship: (.*)$/;
}
print "$oteam,$pom,$sports\n";