Introduction
This article shows how to setup a process to import/upload Leads from an Excel file into a Microsoft CRM via email.
See other Siccolo articles about working with Excel and SQL:
and another article showing MS CRM customization:
1. Transfer Leads from Excel into CRM Leads
Let's say, we have an Excel file, for example, like this:
To access it from SQL:
...
select first_name
, last_name
, company
, number
, street
, city
, state
, zip
from
OpenRowSet('MSDASQL'
,'Driver={Microsoft Excel Driver (*.xls)};' +
'DBQ=c:\inetpub\wwwroot\sfa\leads\leads_to_be_imported\leads.xls',
'SELECT * FROM [excel_data$]') tmp
...
where c:\inetpub\wwwroot\sfa\leads\ is a folder on the CRM server. First, I'll load the Leads
records from Excel into a temp table:
if not exists (select id
from [MSCRM].dbo.sysobjects
where name = 'tmp_leads_to_be_imported' and type ='U')
begin
create table [MSCRM].dbo.tmp_leads_to_be_imported
(
number varchar(150) null
, first_name varchar(50) null
, last_name varchar(50) null
, company varchar(100) null
, street varchar(50) null
, city varchar(50) null
, state varchar(50) null
, zip varchar(50) null
)
end
else
begin
delete [MSCRM].dbo.tmp_leads_to_be_imported
end
insert into [MSCRM].dbo.tmp_leads_to_be_imported
(
number
, first_name
, last_name
, company
, street
, city
, state
, zip
)
select number
, first_name
, last_name
, company
, street
, city
, state
, zip
from
OpenRowSet('MSDASQL'
,'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\inetpub\wwwroot\
sfa\leads\leads_to_be_imported\temp_leads.xls',
'SELECT * FROM [excel_data$]') tmp
select @err_code = @@error
select @records = @@rowcount
if @err_code<>0
begin
set @err = 'Failed to insert into temp table. error=[' +
convert(varchar(10),@err_code) + ']'
set @message = 'Failed Process Excel Leads Records' + char(13) + @err
exec master.dbo.xp_sendmail @recipients ='CRM Person in Charge'
,@message = @message
,@subject = 'Failed Process Excel Leads Records'
RAISERROR (@err, 16, 1)
return
end
if @records=0
begin
set @err = 'Failed to insert into temp table. error=[no records inserted]'
set @message = 'Failed Process Excel Leads Records' + char(13) + @err
exec master.dbo.xp_sendmail @recipients ='CRM Person in Charge'
,@message = @message
,@subject = 'Failed Process Excel Leads Records'
RAISERROR (@err, 16, 1)
return
end
set @file_count = (select count(*) from [MSCRM].dbo.tmp_leads_to_be_imported )
where [MSCRM]
is the name of the CRM database. Usually, it looks like: [company name]_MSCRM; for example, Siccolo_MSCRM.
After that, we can transfer records from the temp table into the Lead
view. As you may know, CRM has an extra layer between the underlying data and the CRM user interfaces - views. For example, the underlying table for storing the leads information is LeadBase; but CRM interfaces work with the Lead
view object.
declare @security_descriptor varchar(555)
declare @modified_by varchar(255)
select @security_descriptor = SecurityDescriptor
, @modified_by = ModifiedBy
from [MSCRM].dbo.SystemUser
where InternalEmailAddress = @user_email or ( isnull(PersonalEmailAddress,'')!=''
and PersonalEmailAddress = @user_email)
insert into [MSCRM].dbo.Lead
(
Subject
, CompanyName
, FirstName
, LastName
, Telephone1
, Address1_Line1
, Address1_City
, Address1_StateOrProvince
, Address1_PostalCode
, Description
, OwningUser
, LeadId
, StateCode
, DeletionStateCode
, SecurityDescriptor
, ModifiedBy
, CreatedOn
, ModifiedOn
)
select company as Subject
, company
, first_name
, last_name
, number
, street
, city
, state
, zip
, 'This Lead was created automatically from Excel file' as Description
, @modified_by as OwningUser
, NEWID() as LeadID
, 0 as StateCode
, 0 as DeletionStateCode
, @security_descriptor as SecurityDescriptor
, @modified_by as ModifiedBy
, getdate() as CreatedOn
, getdate() as CreatedOn
from [MSCRM].dbo.tmp_leads_to_be_imported
select @record_count = @@rowcount
select @file_count as 'original_file_count'
, @record_count as 'processed_record_count'
where [MSCRM]
is the name of the CRM database. Usually, it looks like [company name]_MSCRM; for example, Siccolo_MSCRM.
And now, to make a stored procedure out of this:
CREATE procedure <code>p_Lead_Process_Excel_Leads_File
(
@user_email varchar(50)
,@leads_excel_file_name varchar(255)
,@file_count int =null output
,@record_count int =null output
)
as
set nocount on
...
...
...
...
...
...
set nocount off
Side note:
MS CRM, to handle INSERT/UPDATE/DELETE
, has a set of triggers on the Lead
view:
Unfortunately, the CRM team at Microsoft did not plan for someone inserting more than one Lead
record at a time, so I needed to make some changes to the t_create_lead
trigger, and create a new function f_GetFullName
.
Changes to the t_create_lead
trigger:
...
declare @organizationid uniqueidentifier
select @organizationid = BusinessUnitBase.OrganizationId
from BusinessUnitBase
where BusinessUnitBase.BusinessUnitId = @owningbusinessunit
...
...
insert LeadBase(
...
...
)
select
LeadId,
...
FirstName,
MiddleName,
LastName,
...
EMailAddress3,
dbo.f_GetFullName(@organizationid, FirstName, LastName, MiddleName)
...
from inserted
...
...
Side note:
@@ROWCOUNT
- Returns the number of rows affected by the last statement.@@ERROR
- Returns the error number for the last Transact-SQL statement executed.
2. Process Email and Import Leads
So, at this point, I have a procedure to import/upload Leads records from an Excel file into the Lead
view. Next step - create a procedure to process incoming emails and route them to p_Lead_Process_Excel_Leads_File
. To process emails in/with SQL Server, we can use the xp_readmail
, xp_findnextmsg
, xp_sendmail
, and xp_deletemail
SQL mail extended stored procedures. Something like this:
CREATE procedure p_email_Process_Incoming_Emails
as
set nocount on
...
...
declare @status int
declare @msg_id varchar(94)
declare @mapifailure int
exec @status = master.dbo.<code>xp_findnextmsg
@msg_id = @msg_id output,
@unread_only='true'
if @status <> 0
begin
set @mapifailure=1
select 'failed to execute xp..findnextmessage'
end
while (@mapifailure=0)
begin
if @msg_id is null break
if @msg_id = '' break
declare @originator varchar(255)
,@originator_address varchar(255)
declare @cc_list varchar(255)
declare @msg_subject varchar(255)
declare @msg_message varchar(8000)
exec @status = master.dbo.xp_readmail
@msg_id = @msg_id,
@originator = @originator output,
@cc_list = @cc_list output,
@subject = @msg_subject output,
@message = @msg_message output,
@peek = 'true',
@suppress_attach='true',
@originator_address = @originator_address output
if @status <> 0
begin
select @mapifailure=1
break
end
set @current_msg=@msg_id
exec @status = master.dbo.xp_findnextmsg
@msg_id = @msg_id output,
@unread_only='true'
if @status <> 0
begin
select @mapifailure=1
end
print 'checking email [' + @msg_subject + ']'
declare @attachments varchar(255)
if (lower(@msg_subject) = 'iMport Excel CRM Leads')
begin
print 'import excel CRM leads'
set @bad_input = 0
exec @status = master.dbo.xp_readmail
@msg_id = @current_msg,
@originator = @originator output,
@cc_list = @cc_list output,
@subject = @msg_subject output,
@message = @msg_message output,
@peek = 'false',
@suppress_attach='false',
@attachments = @attachments output,
@originator_address = @originator_address output
if not exists ( select SystemUserId from [MSCRM].dbo.SystemUser
where InternalEmailAddress = @originator or
( isnull(PersonalEmailAddress,'')!=''
and PersonalEmailAddress = @originator)
)
begin
set @msg_message = 'User [' + @originator +
'] does not have access to CRM!'
set @msg_subject = @msg_message
exec @status= master.dbo.xp_sendmail @recipients = @originator
,@message = @msg_message
,@subject = @msg_subject
,@attachments = @attachments
set @bad_input = 1
end
if @bad_input = 0
begin
exec [MSCRM].dbo.p_Lead_Process_Excel_Leads_File
@originator
, @attachments
, @file_count output
, @record_count output
if (@file_count != @record_count)
begin
set @msg_subject = 'Processed ' +
@msg_subject + ' - !Totals Do Not Match!'
set @msg_message = 'Processed ' + @msg_subject + char(13)+ char(13)+
'File Record Count: ' + char(9)+
' convert(varchar,isnull(@file_count,0)) + char(13)+ char(13)+
'Processed Count: ' + char(9)+
' convert(varchar,isnull(@record_count,0)) + char(13)+ char(13)+
char(9)+ '!File Total Does Not Match Processed Total!'
end
else
begin
set @msg_subject = 'Processed ' + @msg_subject
set @msg_message = 'Processed ' + @msg_subject + char(13)+ char(13)+
'Record Count: ' + char(9)+
' convert(varchar,@file_count) + char(13)+ char(13)+
'Processed Count: ' + char(9)+ convert(varchar,@record_count)
end
-- and send email response back to user:
exec @status= master.dbo.xp_sendmail @recipients = @originator
,@message = @msg_message
,@subject = @msg_subject
,@attachments = @attachments
end -- end of if @bad_input = 0
print 'deleting - import excel CRM leads' + convert(varchar, @current_msg)
exec master.dbo.xp_deletemail @current_msg
end -- end of if (lower(@msg_subject) = 'import excel CRM leads')
end -- end of while (@mapifailure=0)
-- done with messages in Inbox
if @mapifailure=1
begin
raiserror(15079,-1,-1,@messages)
return(1)
end
else
begin
return(0)
end
set nocount off
So, in order to have SQL Server process Leads from an Excel file into the Lead
view, simply send email message to SQL Server with the Excel file attached, and put import excel CRM leads as the email subject.
History
No improvements so far. Nearly perfect.
Points of Interest
If you would like to read more on this story - please take a look at Siccolo - Free Mobile Management Tool For SQL Server, and more articles at Siccolo.