SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Outlook Data to SQL Table


Outlook Data to SQL Table

Author
Message
ew0619
ew0619
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 13
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
DBA328
Old Hand
Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)

Group: General Forum Members
Points: 308 Visits: 1123
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

[li][/li]
ew0619
ew0619
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 13
Thank you - hopefully this will work with Exchange 2010. I'll keep you posted.
John Puk
John Puk
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 5
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
ew0619
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 13
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
ew0619
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 13
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 TransferorsTongueleasant 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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39816 Visits: 38563
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

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39816 Visits: 38563
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ew0619
ew0619
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 13
Yes this is real data and it is public record. But I apologize if this information is typically not posted.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39816 Visits: 38563
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search