Skip to content

Instantly share code, notes, and snippets.

@darrenjrobinson darrenjrobinson/Import.ps1
Last active Mar 29, 2016

Embed
What would you like to do?
MIMCSVviaEWSPSMA
param (
$Username,
$Password,
$Credentials,
$OperationType,
$NBDomain = "ADDomain",
$MailboxName = "newstaffreports@customer.com.au",
$pageupsender = "reports-out@saas.provider.com",
$downloadDirectory = "\\fileserver\appshare$\SaaSReports",
$EWSURI=[system.URI] "https://webmail.customer.com.au/ews/Exchange.asmx",
$ProcessedInboxFolder = "Processed",
[bool] $usepagedimport,
$pagesize
)
[string]$CSVFile = $null
## Load EWS Managed API dll
# https://www.microsoft.com/en-us/download/details.aspx?id=42951
Add-Type -Path "C:\Program Files\Microsoft\Exchange\Web Services\2.2\Microsoft.Exchange.WebServices.dll"
## Set Exchange Version (Exchange2010, Exchange2010_SP1, Exchange2010_SP2, Exchange2013 or Exchange2013_SP1)
$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2013_SP1
## Create Exchange Service Object
$ExchangeService = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)
## Set Credentials (using Username and Password passed from the MA Config
$creds = New-Object System.Net.NetworkCredential($Username,$Password,$NBDomain)
$ExchangeService.Credentials = $creds
## Set AutoDiscover URL from Params
$ExchangeService.Url = $EWSURI
#Bind to the Inbox folder
$SearchFilterAttachments = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::HasAttachments, $true)
$InboxFolderID= new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$MailboxName)
$Inbox = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($ExchangeService,$InboxFolderID)
## Connect to the mailbox, find the attachements
$numItems = New-Object Microsoft.Exchange.WebServices.Data.ItemView(100)
$InboxResults = $Inbox.FindItems($SearchFilterAttachments,$numItems)
[int]$attachmentint = 0
$pageupexport = $false
# Processes by the most recent received message first
foreach($MailItem in $InboxResults.Items){
$MailItem.Load()
foreach($attach in $MailItem.Attachments){
# the first attachment should be the one we're looking for (the most recent received)
# but we'll check to make sure it is from our sender and the attachment name matches what we're expecting
if ($attachmentint = 0 -or !$pageupexport)
{
if ($MailItem.Sender.Address -contains $pageupsender)
{
# make sure the attachment is the CSV file we're after and not a signature graphic
if ($attach.Name.ToString() -like 'IT_Setup*.csv')
{
# Extract the CSV file and save to the File Share
$attach.Load()
$File = new-object System.IO.FileStream(($downloadDirectory + “\” + $attach.Name.ToString()), [System.IO.FileMode]::Create)
$File.Write($attach.Content, 0, $attach.Content.Length)
$File.Close()
$CSVFile = $downloadDirectory + “\” + $attach.Name.ToString()
$pageupexport = $true
}
}
}
$attachmentint = $attachmentint + 1
}
}
#Move the emails with attachments
if ($attachmentint -gt 0)
{
#Get the ID of the folder to move to
$Folder = New-Object Microsoft.Exchange.WebServices.Data.FolderView(100)
$Folder.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Shallow;
$FolderSearchFilter = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.FolderSchema]::DisplayName,$ProcessedInboxFolder)
$ProcessedFolder = $Inbox.FindFolders($FolderSearchFilter,$Folder)
#Define ItemView to retreive just 100 Items
$numItems = New-Object Microsoft.Exchange.WebServices.Data.ItemView(100)
$AttachedFiles = $null
do
{
$AttachedFiles = $Inbox.FindItems($SearchFilterAttachments,$numItems)
foreach($Attachment in $AttachedFiles.Items)
{
# Move the Message
$Attachment.Move($ProcessedFolder.Folders[0].Id)
}
$numItems.Offset += $AttachedFiles.Items.Count
}
while($AttachedFiles.MoreAvailable -eq $true)
}
## Make sure we have a file to import
if ($CSVFile)
{
#We have a new file
$importfile = $true
}
Else
{
## We don't have a new file from the Inbox. We have to locate the last file we put in the fileshare
$newestfiledate = [DateTime]::MinValue
$newestfiledate | Out-File $DebugFile -Append
# Find files in the fileshare
get-childitem $downloadDirectory | Where {$_.name -like 'IT_Setup*.csv'} | ForEach-Object {
if ($_.LastWriteTime -gt $newestfiledate)# -and -not $_.PSIsContainer)
{
# Check the file properties
$newestfiledate = $_.LastWriteTime
$LastFileinDir = $_.Name
$LastFileinDirDate = $_.LastWriteTime
$importfile = $true
$CSVFile = $downloadDirectory + “\” + $LastFileinDir
}
}
}
# Parse the CSV first, then do logic on it in the next step
If ($importfile -and $CSVFile){
$importdata = Import-Csv $CSVFile | foreach {
New-Object PSObject -prop @{
StaffID = $_.'Sign On';
Surname = $_.'Applicant last name';
GivenName = $_.'Applicant first name';
PreferredName = $_.'Preferred name';
MiddleName = $_.'Middle name';
State = $_.'Home state/territory';
Title = $_.'Position Title';
EmployeeType = $_.'Employment type';
Company = $_.Business;
Department = $_.Department;
BirthDate = $_.'Date of birth'
StartDate = $_.'Date Started'
}
}
}
# Perform validation on the data and bring through the new staff accordingly
ForEach($user in $importdata)
{
#Only bring in users we have a StaffID (Anchor for)
if ($user.StaffID)
{
# Only bring in users where their StartDate is within the next 7 days
$todaysDate = get-date
if ($user.StartDate) { $userStartDate = get-date($user.StartDate)
}
if ($userStartDate.AddDays(-7) -lt $todaysDate.AddDays(7))
{
$obj = @{}
$obj.Add("StaffID",$user.StaffID)
$obj.Add("objectClass", "user")
$obj.Add("Surname",$user.Surname)
$obj.Add("GivenName",$user.GivenName)
$obj.Add("MiddleName",$user.MiddleName)
$obj.Add("State",$user.State)
$obj.Add("Title",$user.Title)
$obj.Add("Company",$user.Company)
$obj.Add("Department",$user.Department)
if ($user.BirthDate) {
$birthDate = Get-Date ($user.BirthDate)
[string]$birthDateFormat = $birthDate.ToString("yyyy-MM-dd")
$obj.Add("BirthDate",$birthDateFormat)
}
if ($user.StartDate) {
$startDate = Get-Date ($user.StartDate)
[string]$startDateFormat = $startDate.ToString("yyyy-MM-dd")
$obj.Add("StartDate",$startDateFormat)
}
if ($user.PreferredName) {
$obj.Add("PreferredName",$user.PreferredName)
}
else
{
if ($user.GivenName) {
$obj.Add("PreferredName",$user.GivenName)
}
}
# Pass the object to the MA
$obj
}
}
}
#endregion
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.