Outlook Data to SQL Table

Question

Outlook Data to SQL Table

ew0619

SSC-Addicted

Points: 497
More actions
September 18, 2012 at 8:53 pm

#281778

Hi Everyone,
I want to be able to automatically populate an SQL table with data from emails I receive in a specific folder in my Outlook. Each email would be its own record and be broken into 4 different fields. Ultimately, I would like to join this table with my existing tables in my DB and generate reports. My first problem is getting the data in there in real time (or maybe at specific time everyday like 6pm). My second problem is breaking this data out into these different fields. For now, I would just be interested in getting the data there without human involvement and I will deal with organizing it at a later time. Anyone have any suggestions??

DBA328 Hall of Fame Points: 3952 More actions · Answer 1

Microsoft SQL Server distributed queries can be used to query data from the Exchange Web Store through OLE DB Provider for Exchange and can be joined to tables in SQL Server, you could try it.

http://msdn.microsoft.com/en-us/library/ms191461(v=SQL.100).aspx

ew0619 SSC-Addicted Points: 497 More actions · Answer 2

Thank you - hopefully this will work with Exchange 2010. I'll keep you posted.

John Puk SSC Enthusiast Points: 148 More actions · Answer 3

You can try GeniusConnect for Microsoft Outlook.GeniusConnect will synchronize Outlook data with any relational database that supports ODBC (MS Access, mySQL, MS SQL Server, Oracle etc.): http://www.geniusconnect.com/articles/Products/2/3/

ew0619 SSC-Addicted Points: 497 More actions · Answer 4

GeniusConnect turned out to be exactly what I was looking for. Thank you very much.

NOW that I have this data in a table, I'd like to create a view that parses the email message into different fields, and join with my data in other tables. Anyone have any resources for learning how to parse data??

ew0619 SSC-Addicted Points: 497 More actions · Answer 5

Out of the string below, I would like to create a view that will show the following fields (target values are in quotes). I have tens of thousands of these records in SQL and all are generally in this format. Is it possible to break these values out of this string of text?

Seller "Pleasant Field Farms"

Buyer "CRT Special Investments LLC"

Case Name "MF Global Inc."

Case Number "11-02790-mg"

Document Number "4015"

Claim No. "900007605"

***NOTE TO PUBLIC ACCESS USERS*** Judicial Conference of the United States policy permits attorneys of record and parties in a case (including pro se litigants) to receive one free electronic copy of all documents filed electronically, if receipt is required by law or directed by the filer. PACER access fees apply to all other users. To avoid later charges, download a copy of each document during this first viewing. However, if the referenced document is a transcript, the free copy and 30-page limit do not apply. U.S. Bankruptcy Court Southern District of New York Notice of Electronic Filing The following transaction was received from Joseph E. Sarachek entered on 10/24/2012 at 4:06 PM and filed on 10/24/2012 Case Name: MF Global Inc. Case Number: HYPERLINK "https://ecf.nysb.uscourts.gov/cgi-bin/DktRpt.pl?221511"11-02790-mg Document Number: HYPERLINK "https://ecf.nysb.uscourts.gov/doc1/126012855818?pdf_header=&magic_num=0&de_seq_num=10319&caseid=221511"4015 Docket Text: Transfer Agreement FRBP Transfer Agreement 3001 (e) 2 Transferors:Pleasant Field Farms(Claim No. 900007605). To CRT Special Investments LLC. filed by Joseph E. Sarachek on behalf of CRT Special Investments LLC.(Sarachek, Joseph)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

DBA328 (9/18/2012)
Microsoft SQL Server distributed queries can be used to query data from the Exchange Web Store through OLE DB Provider for Exchange and can be joined to tables in SQL Server, you could try it.
http://msdn.microsoft.com/en-us/library/ms191461(v=SQL.100).aspx

Let's make this easier for others:

http://msdn.microsoft.com/en-us/library/ms191461(v=SQL.100).aspx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

ew0619 (10/25/2012)
Out of the string below, I would like to create a view that will show the following fields (target values are in quotes). I have tens of thousands of these records in SQL and all are generally in this format. Is it possible to break these values out of this string of text?
...

I really hope that the information in the above post (removed from the quoted version) isn't real data. Although potentially public record, I don't think this is something that should be posted. Data like this should really be sanitized so that it doesn't reflect a real person or organization.

ew0619 SSC-Addicted Points: 497 More actions · Answer 8

Yes this is real data and it is public record. But I apologize if this information is typically not posted.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

ew0619 (10/26/2012)
Yes this is real data and it is public record. But I apologize if this information is typically not posted.

Well, public record or not, if it were about me I would be complaining to have it removed. Not everyone needs to see it if they have no need to know.

Pedro Oliveira Newbie Points: 1 More actions · Answer 10

Once I have created a Table-valued Function to extract from known expressions from text. It is easy because the tool that is used to write this automatic text repeats the same procedures and you can parse and split it into record columns.

In this example it would be a function <b>AnyFunction</b> with 2 parameters like @FullText and @ValueFromExpression.

To take buyer information from the line «Buyer "CRT Special Investments LLC"»: I would call AnyFunction(FullText, 'Buyer ')

Your parsing code should look in this case for first «"» and a second «"» after ValueFromExpression. All the string between first «"» and second «"» should be the result.

My function uses an extra parameter to determine automatic separators eg "=" to extract all possible pairs of category/value from strings with known separators.