Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Receiving  email through SQL server

Posted on 2013-12-23
3
Medium Priority
?
274 Views
Last Modified: 2014-01-16
I am receiving a email every day from my  vendor with CSV file attachment.
The CSV file has a vendor products list.  Every day I import the CSV file in to the SQL sever table (vendor_Products)  manually.

Is it possible to automate the process using SQL job ?
0
Comment
Question by:Varshini S
  • 2
3 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39736331
You can create an Outlook AddIn that when you receive an email from the vendor then check if the email has an attachment. If the attachment is a list then run the store procedure on the sql that import this into the list or import the list directly from the email attachment.

I  Will send you the example to you in a second
0
 
LVL 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1000 total points
ID: 39736353
I agree with the above Expert.  I had a comment typed up, but got distracted; therefore, I will let you two continue.  However, it would be good to mention that SQL integration services could be a good resource for this.  PragmaticWorks has an adapter that may be useful:
http://pragmaticworks.com/Products/Task-Factory/Features/EmailSource.aspx

--Kevin
0
 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 1000 total points
ID: 39736390
This Will Be the code more or less. Has to be reviewed just in case.
Is and Outlook Addin in  VB.net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.Net
Imports System.ComponentModel

Public Class AddIn_CheckMail_from_Vendor
  Public BgWk As BackgroundWorker = New BackgroundWorker

      Private Sub AddIn_CheckMail_from_Vendor_Startup() Handles Me.Startup
        AddHandler BgWkDialer.RunWorkerCompleted, AddressOf BgwDialer_WorkCompleted
        AddHandler BgWkDialer.ProgressChanged, AddressOf BgwDialer_ProgressChanged
        AddHandler BgWkDialer.DoWork, AddressOf BgWDialer_Working
    End Sub

   Private Sub Application_NewMail() Handles Application.NewMail
        Dim tempApp As Outlook.Application
        Dim tempInbox As Outlook.MAPIFolder
        Dim InboxItems As Outlook.Items
        tempApp = CreateObject("Outlook.Application")
        tempInbox = tempApp.GetNamespace("Mapi").GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
        InboxItems = tempInbox.Items
        Dim newMail As Outlook.MailItem
        Dim inBox As Outlook.MAPIFolder = Me.Application.ActiveExplorer().Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
        Dim DestinationFolder As Outlook.MAPIFolder = inBox.Folders("Processed") 'Folder that you will move the email after imported
        For Each newMail In InboxItems
            Dim EmailDesc As String = newMail.Body
            If InStr(UCase(newMail.SenderEmailAddress), "yourvendor.com", CompareMethod.Text) > 0 Then 'Checking for contains the vendor in the email
                Procces_Email(newMail)
                newMail.UnRead=False 
                newMail.Move(DestinationFolder)
            End If
        Next
  End Sub   

   
  Private Sub Procces_Email(NewMail As Outlook.MailItem)
        Dim Attch As Outlook.Attachment
        Dim AttchCount As Integer = 0
        AttchCount = NewMail.Attachments.Count
        If AttchCount > 0 Then
         For I As Integer = 1 To AttchCount
                Attch = NewMail.Attachments.Item(I)
                'Don't forget to check here
                'If the attachment is vendor list and if is a vendor list 
                Dim WholeFile As System.IO.TextReader = System.IO.File.OpenText(Attch )
                Dim TextLines() As String = Split(WholeFile.ReadToEnd, vbCrLf)
                For Each LineOfVendor as String in TextLines
                   Dim Qry as string="INSERT INTO YOUR_VENDOR_TABLE(VENDOR_NAME) VALUES('" & Replace(LineOfVendor,"'","''") & "')"
                   DIM P AS NEW DATATABLE
                   DIM ADP as NEW SLQDATADAPTER(Qry,YOUR_CONEXTION_STRING)
                   P.CLEAR
                   Try 
                     ADP.Fill(P)
                   Catch ex as Exception
                   End Try
                Next
                WholeFile.Close()
                WholeFile.Dispose()
         Next 
        End IF
  End Sub
                      
End Class

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question