Email to SQL

[ITGURU]

Hi

 

Not sure if this is the right area, but I'm looking for the easiest/cheapest, free if possible, way to having emails imported into SQL. If possible I would like something to pull the email from exchange and enter into SQL so that I can then build a web based portal for interpreting the data.

 

Thanks in advance.

[dhicks]

I'm looking for the easiest/cheapest, free if possible, way to having emails imported into SQL. If possible I would like something to pull the email from exchange and enter into SQL so that I can then build a web based portal for interpreting the data.

 

Do you mean an historical set of emails of some kind - either emails sent to a particular person or about a particular subject from the last, say, 5 years? Or emails sent from now on? If the latter, you could set up a mail forwarding rule in Exchange to send your emails to whatever destination you liked. If the former, you need to find some way of getting Exchange to list emails and export individual ones for you. Or are you simply wanting to do something with all emails in your Exchange server for some reason?

[pcstru]

Interesting. A good approach to systems design is to start with the outputs. What do you need the thing to do? That is hidden in the the innocent looking phrase "interpreting the data". What does that mean?

 

Exchange itself is managing the mail in databases. If you already have exchange you have a free tool right there. Is being able to interrogate the data using SQL really that important? SQL is just a way to interact with data - if you are not precious about that, you almost certainly widen your options. There might even be tools to do it. You could even drive them from a web server if you are brave or just careful.

 

So, what do you really want to do?

[ITGURU]

Basically when an email is sent to an exchange mailbox, I want that to them transfer the sender email, subject and body into an sql database, so that I can then create an asp page to pull the data from the sql table and display and work with.

 

Essentially a helpdesk system without needing spiceworks etc so can customise and integrate into my in-house developed asset management database.

[pcstru]

I think to access the mailbox you need to either use pop3 or MAPI libraries from within asp (or language of your choice), look for new mail, get it, parse out the info you want and insert that into the table you have created. I've done very similar in python and it was relatively easy.

 

You could probably use a powershell script to access the mailbox and do the same. Running it as a regular scheduled process.

 

Personally, I use humans.

[jinnantonnixx]

Almost certainly Powershell will be to tool of choice here.

 

I've done loads with Powershell and SQL and it works like magic.

http://sqlfool.com/category/powershell/

 

 

There are lots of Powershell cmdlets for Exchange https://technet.microsoft.com/en-us/library/aa998225%28v=exchg.150%29.aspx

Edited July 18, 2015 by jinnantonnixx

[matt40k]

Easy solution. Look at https://zapier.com/.

[NotSoFast]

Off the wall but easy solution: forward it to Gmail and use apps script to save to a Google sheets file, then embed that spreadaheet in a Google site using the awsometable addon for filtering etc?

[KWatson]

If you are using classic asp take a look at this page : www.Dimac.NET : Tools and solutions for web-developers ( you'd need the pro version for $75 = £30 approx)

 

You can pop enable the exchange mailbox, then use the component to read the emails - then update your SQL database all within your ASP application.

Edited August 18, 2015 by KWatson