Title:       Customer Relationship Management Integration - MS CRM Customization - Import Leads from Excel via Email - Create MS CRM Leads Automatically
Author:      Greg Dubinovskiy 
Email:       siccolo_mobile_management@yahoo.com
Environment: Microsoft CRM, SQL Server, SQL
Keywords:    Customer Relationship Management Integration, Microsoft CRM, MS CRM Customization, MS CRM Leads, Import Leads from Excel via Email, Create MS CRM Leads Automatically, MS CRM Customization Tools
Level:       Intermediate
Description: Build Process to Import Leads Records from Excel into MS CRM via Email
Section      Miscellaneous
SubSection   General

Siccolo Development Articles - Customer Relationship Management Integration - MS CRM Customization - Import Leads from Excel via Email - Create MS CRM Leads Automatically
Google

Introduction

The article presented bellow, shows how to setup a process to allow import/upload Leads from Excel file into Microsoft CRM via email.
(see other Siccolo articles about working with Excel and SQL:
Mass Export All Tables Into Excel Files
Stored procedure to convert Excel file into SQL table ) Customizing CRM is not hard at all, thanks to semi-open web interface and SQL Server database. This article shows how to develop your own CRM Customization Tool for your business solutions.
Quickly customize Microsoft CRM - CRM Leads - so that you capture and manage the exact data you need to increase sales and service effectiveness. Customize the Microsoft CRM user interface - Learn how to customize the Microsoft CRM user interface by creating and modifying forms, views, and previews. Microsoft CRM allows system integrators to create a highly customized and integrated application. In addition, the solution supports flexible options that enable users to focus only on the features they need to perform the tasks their jobs require.
(see other Siccolo articles about working with Microsoft CRM:
MS CRM Customization - How to add custom CRM Report to Reports Grid in CRM and and CRM Customization - How to Qualify Leads in CRM - Set Lead State and Status in CRM )

1. Transfer Leads records from Excel into CRM Leads

First, given 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\ folder on CRM server.

Side Note:
OPENROWSET OPENROWSET - Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.






So, first, I'll load Leads records from excel into temp table:
	if not exists (select id 
			from [MSCRM].dbo.sysobjects
			where name = 'tmp_leads_to_be_imported' and type ='U')
	begin
		-- create temp table:
		create table [MSCRM].dbo.tmp_leads_to_be_imported
		(
			 number 	varchar(150)	null	-- phone number
			, 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

	-- to handle possible "oops":
	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

	-- get number of Leads records from the original Excel file
	set @file_count = (select count(*) from  [MSCRM].dbo.tmp_leads_to_be_imported )

(where [MSCRM] is the name of CRM database, usually it looks like [company name]_MSCRM, for example Siccolo_MSCRM)

After that, we can transfer records from temp table into Lead view.
(As you may know, CRM has an extra layer between underlying data and CRM user interfaces - views. For example, underlying table for storing leads information is LeadBase; but CRM interfaces work with Lead view object)


	-- in order insert new records into Leads we need  SecurityDescriptor and ModifiedBy values:
	declare @security_descriptor 	varchar(555)
	declare @modified_by 		varchar(255)	 
	
	select @security_descriptor = SecurityDescriptor
		, @modified_by = ModifiedBy
	from [MSCRM].dbo.SystemUser
	-- find CRM user by using originator email address
	where InternalEmailAddress = @user_email or ( isnull(PersonalEmailAddress,'')!='' and PersonalEmailAddress = @user_email)


	-- now, insert into Leads view:
	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		-- open lead
		, 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 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 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
		...
		...
		-- insert into temp table from Excel
		...
		...
		-- insert into Lead view from temp table
		...
		...

	set nocount off
	


Side Note:
MS CRM, to handle INSERT/UPDATE/DELETE, has a set of triggers on Lead view :

Unfortunately, CRM team at Microsoft did not plan for someone inserting more than one Lead record at the time, so I needed make some changes to t_create_lead trigger, and create a new function f_GetFullName
Changes to t_create_lead trigger (in blue):
		...
		declare @organizationid uniqueidentifier
		select @organizationid = BusinessUnitBase.OrganizationId
		from BusinessUnitBase
		where BusinessUnitBase.BusinessUnitId = @owningbusinessunit

		
		-- -----------------------------------------------------------
		-- Before changes:
		/*
			declare @firstname nvarchar(50)
			declare @lastname nvarchar(50)
			declare @middlename nvarchar(50)
			declare @fullname nvarchar(160)

			select @firstname = FirstName, @middlename = MiddleName, @lastname = LastName, @fullname = FullName
			from inserted

			if @fullname is null 
			   exec p_GetFullName @organizationid, @firstname, @lastname, @middlename, @fullname output
		*/

		...
		...

		insert LeadBase(
				...
				...
				)
		select 
			    LeadId, 
				...
			    FirstName, 
			    MiddleName, 
			    LastName, 
				...
			    EMailAddress3, 
				-- ------------------------------------------------------------------------------------------------------
				-- full name!:
			    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 Excel file into Lead view. Next step - create a procedure to process incoming emails and route them to p_Lead_Process_Excel_Leads_File. To process email in/with SQL Server, we can use xp_readmail, xp_findnextmsg, xp_sendmail, xp_deletemail SQL mail extended stored procedures. Something like this:
	CREATE procedure p_email_Process_Incoming_Emails
	as

		set nocount on
			...
			...
		/* get first message id */
		declare @status int
		declare @msg_id varchar(94)
		declare @mapifailure int
	
		exec @status 	= master.dbo.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
	
			-- peek at incoming email message and see if we need to process it:

			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

			/* get new message id before processing & deleting current */
			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 message subject contains "magic" words:
			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', 		-- this time no peeking, get email message
					@suppress_attach='false',
					@attachments 	= @attachments output, 	-- and get attachted Excel file!
					@originator_address = @originator_address output
					-- make sure user has rights to import CRM Leads!!!
				/* 
					check user credentials bases on user email address
				*/
				-- also, check if user email is in CRM SystemUser:
				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 number of Leads records in Excel file is different then number of records inserted into CRM Leads,
					-- let user know:
					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
						-- number of records in Excel is the same as number of records inserted into CRM Leads:
						-- simply let user know that Leads Excel file is processed
						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 Excel file into Lead view, simply sends email message to SQL Server with attached Excel file and put import excel CRM leads in a email subject. And that's it. We just developed our own CRM customization tool - Customer Relationship Management Integration Tool. As you can see Microsoft CRM Customization can be done with various tools.

History

no improvements so far. nearly perfect.


Article keywords: Microsoft CRM customization, CRM Management, Leads, OPENROWSET, @@error, @@rowcount, t_create_lead trigger, xp_readmail, xp_findnextmsg, xp_sendmail, xp_deletemail, CRM SystemUser

Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky © 2007
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.
Siccolo - SQL Server Management Tool For Mobile Devices is packed with built-in functionality and tools. Siccolo delivers a rich set of management tools for both DBAs and sys admins. SQL Server management has always been an area of DBA concern. The new Management Tool For Mobile Devices - Siccolo - has simple "Enterprise Manager" and the "Query Analyzer". Siccolo is a management tool for the MS SQL Server with administration capabilities and a database query tool. The administration features provide users the ability to browse database structures. An integrated query tool allows users to quickly create, edit and execute SQL queries and scripts. Siccolo also provides an export tool to allow users to easily save and email execution results. Siccolo helps database professionals save time and increase their productivity by utilizing a more efficient approach to database management - use their Windows Mobile empowered device while sipping margarita on the beach For increased security, Siccolo is configured to run under SSL with IIS authentication.