Powershell can read email & insert excel file attachment into a SQL Database

So at our SQL SouthWest User Group session last week we had sessions from Jonathan @fatherjack and Annette @MrsFatherjack on SSRS and SSIS respectively. During Annettes SSIS session a question was asked about reading email attachments and then loading them into a database. No-one had an answer using SSIS but I said it could be done with Powershell . So I have written the following script.

What it does is open an Outlook com object, search for an email with a certain subject and save it in the temp folder and then import it into a SQL database. This needs to be done on a machine with Outlook and Excel installed. It is possible to process the email using EWS in an Exchange environment and other people have written scripts to do so.

It uses two functions Out-Datatable from http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

and Write-Datatable from

http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

The first takes the output from parsing the Excel File and converts it into a datatable object which can then be piped to the second which uses the BulkCopy method. Alternatively if you require it you could add each row of the excel file to an array and then use Invoke-SQLCmd to insert the data row by row.

  1. while($row1
    -le
    $lastusedrange)
  2. {
  3. $Col1
    =
    $ws.Cells.Item($row1,1).Value2
  4. $Col2
    =
    $ws.Cells.Item($row1,2).Value2
  5. $Col3
    =
    $ws.Cells.Item($row1,3).Value2
  6. $query
    =
    “INSERT INTO Database.Schema.Table
  7.           (Column1
  8.           ,Column2
  9.           ,Column3 )
  10.           VALUES
  11.           (‘$Col1’
  12.           ,’$Col2′
  13.           ,’$Col3′)
  14. GO

  15. $dt=invokesqlcmd -query
    $query
    ServerInstance $Server
    database $database
  16. ## For Testing Write-Host $query

Here is the script

  1.  #############################################################################################
  2. #
  3. # NAME: ExcelEmailAttachmentToDatabase.ps1
  4. # DATE:15/06/2013
  5. #
  6. # COMMENTS: This script will read your email using outlook com object and save Excel Attachment
  7. # and import it into a database
  8. # REQUIRES: It uses two functions Out-Datatable from
  9. # and Write-Datatable from
  10. #
  11. # ————————————————————————
  12. # Create Outlook Object
  13. Addtype
    assembly “Microsoft.Office.Interop.Outlook”
    |
    out-null
  14.  $olFolders
    =
    “Microsoft.Office.Interop.Outlook.olDefaultFolders”
    -as
    [type]
  15.  $outlook
    =
    new-object
    -comobject outlook.application
  16.  $namespace
    =
    $outlook.GetNameSpace(“MAPI”)
  17.  # Set Folder to Inbox
  18.  $folder
    =
    $namespace.getDefaultFolder($olFolders::olFolderInBox)
  19.  # CHeck Email For Subject and set to variable
  20.  $Email
    =$folder.items |  Where-Object Subject -Contains
    $Subject
  21. $Attachments
    =
    $Email.Attachments
  22. $filepath
    =
    $env:TEMP
  23. $filename
    =
    “TestFilename.xlsx”
  24. $Subject
    =
    “This is a Test”
  25. $server
    =
    ‘test server’
  26. $Database
    =
    ‘Test Database’
  27. $Table
    =
    ‘tbl_DataloadTest’
  28. foreach($Attachment
    in
    $Attachments)
  29. {
  30. $attachName
    =
    $Attachment.filename
  31. If
    ($attachName.Contains(“xlsx”))
    {
  32. $Attachment.saveasfile((Join-Path
    $filepath
    $filename))
  33. }
  34. }
  35. # Create an Excel Object
  36. $xl
    =
    New-Object
    -comobject Excel.Application
  37. <#
  38. ##For testing
  39. $xl.visible = $true
  40. #>
  41. # Open the File
  42. $wb
    =
    $xl.WorkBooks.Open(“$filepath\$filename”)
  43. $ws
    =
    $wb.Worksheets.Item(1)
  44. # If your data does not start at A1 you may need
  45. $column1
    =
    1
  46. $row1
    =
    2
  47. $lastusedrange
    =
    $ws.UsedRange.Rows.Count
  48. $dt
    =
    @()
  49. while($row1
    -le
    $lastusedrange)
  50. {
  51. $Col1
    =
    $ws.Cells.Item($row1,1).Value2
  52. $Col2
    =
    $ws.Cells.Item($row1,2).Value2
  53. $Col3
    =
    $ws.Cells.Item($row1,3).Value2
  54. $newrow
    =
    ($Col1,$col2,$col3)
  55. $dt
    +=
    $newrow
  56. # Move to next row
  57. $row1
    =
    $row1
    +
    1
  58. }
  59. $xl.Quit()
  60. [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
  61. $Input
    =
    $dt|OutDataTable
  62. WriteDataTable ServerInstance $server
    Database $Database
    TableName $Table
    Data $Input

Visit your own User Group – You can find them here
http://www.sqlpass.org/

If you are in the South West UK then come and join our group. Free training and conversation with like minded people once a month and pizza too what could be better!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s