Receiving email through SQL server

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 ?
Varshini SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jesus RodriguezIT ManagerCommented:
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
Kevin CrossChief Technology OfficerCommented:
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
Jesus RodriguezIT ManagerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.