Automate Excel Imports to SQL Server Database

Introduction

If you have worked in an IT department it is more than likely that you have been asked, more than once, to update a database from an Excel spreadsheet. It might have been a new price list, customer discounts, item master changes, product specifications, supplier lead times or something similar.

Business user’s preference to use Excel as their primary tool for creating these files is perfectly understandable; after all it is great tool for quickly adjusting, copying and transforming data. However getting the Excel file into a SQL database and process the data requires many steps and is often a tedious and time consuming prospect.

For example the excel file delivered as an email attached has to be saved to a file share. Using the import wizard a number screens has to be navigated and selection made. Once the data is imported the actual update has to be done and verified. For a one off import this may be the best option however many times then same type of update is needed on a regular basis. In these instances there is an opportunity to streamline and automate the import to save time and improve service levels.
Automatic Excel to SQL Import | Boomerang Notification Framework
The Boomerang framework may be used to quickly create solid solutions where communication of any sort is needed. In this specific example we will illustrate a simple way to automate an Excel import containing a price update as well as dynamically import any excel file into a SQL database server.

Solution

To do a automatic Excel to SQL import we are going to use three core features in the Boomerang Notification Framework, Email In, File Out and Email Out.

Email In allows business users to send an email, containing the Excel price update attachment, directly to the Boomerang services rather than to a person in the IT department.

The File Out service will be used to store the email attachment on a network share so that it can be read and imported using Microsoft OLEDB driver. The third service, Email Out, will be used to notify the business user of the import result.

Automatic Excel to SQL Import | Boomerang Notification Framework

Preparation

Our first step is to make an Excel template file where we specify what columns (fields) are needed for the price update. The Excel template will then be used and reused by business users whenever a price update is needed. Automatic Excel to SQL Import | Template | Boomerang Notification Framework

For simplicity and convenience we created a specific email account to be used for this purpose; price_import@domain.com. This email account is associated with Boomerang meaning that mail sent to this address will automatically be parsed and stored in the Boomerang database in the tables IN_EMAIL, IN_EMAIL_RECIPIENT and IN_EMAIL_CONTENT (email attachments).

To import data from Microsoft Excel to SQL Server OPENROWSET (and OPENDATASOURCE) functions with OLE DB data source will be used. For this to work the OLE DB driver needs to be installed and configured. Here is good article describing how to go about setting up SQL to query Excel files.

Step 1 – On Email In

Every time an email is received by Boomerang Notification Framework the email will automatically be parsed saved into various tables like IN_EMAIL, IN_EMAIL_CONTENT, IN_EMAIL_RECIPIENT.

Each received email will be assigned a unique key, the rKey (e.g. dbo.IN_EMAIL.rKey). After any email has been parsed and inserted the stored procedure sp_On_Email_In is executed with the rKey. We will use this trigger to verify that the received email is an excel price update by querying the email subject line for the word “ExcelImport”:

USE [Boomerang]
 
ALTER procedure [dbo].[sp_On_Email_In]
(
@rKey uniqueidentifier
)
as begin
set nocount on 
-- Automatic Excel to SQL Import | Boomerang Notification Framework ----------
-- Excel Import : Email In -- File Out -- Import -- Email Out ----------------  
select * from IN_EMAIL where rKey = @rKey and Subject like '%ExcelImport%'
if @@ROWCOUNT = 1

If the received email is indeed a price update we will proceed by saving the excel attachment and information like email sender, file name into a temp table. At this time we are also adding some keys (gKey, jKey) needed in next step.

set xact_abort on
begin
	-- Automatic Excel to SQL Import | Boomerang Notification Framework ----------
        --- Get file or files that needs to be processed -----------------------------
	select NEWID() as gKey, NEWID() as jKey, ie.From_Email as Received_From_Email, iec.aKey as Received_File_Key, iec.Name as Received_File_Name, Data as Received_File 
	into #_tmp_tbl
	from IN_EMAIL ie inner join IN_EMAIL_CONTENT iec on ie.rKey = iec.rKey and iec.Name like '%.xls%'
	where ie.rKey = @rKey

Next we will save all email attachments as files by using the File Out feature. We start by creating a new event by inserting a row in EVENT_MASTER and saving file name and sender information (which will be used in step 2)
in the user defined fields (Str1, Str2). The event master insert is followed by inserts in OUT_FILE and EVENT_CONTENT which specifies where and what is going to be part of the file out job i.e. the received Excel file or files.

        -- Automatic Excel to SQL Import | Boomerang Notification Framework ----------
        --- create a new event for every file received  -----------------------------
	insert into dbo.EVENT_MASTER (gKey, Source, Created_By, Str1, Str2, Uid1) 
	select gKey, 5, 'sp_On_Email_In', Received_File_Name, Received_From_Email, Received_File_Key from #_tmp_tbl -- Source 5 = Excel Import
	--- do file out for every file 
	insert into dbo.OUT_FILE(gKey, jKey, [Path], Mode) 
	select gKey, jKey, 'c:\temp', 1 from #_tmp_tbl -- 1 = create new file
	--- insert file or files into event content as stream
	insert into dbo.EVENT_CONTENT (gKey, jKey, Src_Type, Name, Stream) 
	select gKey, jKey, 0, convert(varchar(40), Received_File_Key) + '_' + Received_File_Name, Received_File from #_tmp_tbl -- Src_Type 0 = Streaming

Next we update release flag in the EVENT_MASTER to indicate that job can be released to be processed by Boomerang.

	--- release job to be processed
	update dbo.EVENT_MASTER set Status = 0 where gKey in (select gKey from #_tmp_tbl) -- Status 0 = release job
end

Step 2 – After File Out

Every time a file out job has been processed by Boomerang Notification Framework the stored procedure sp_After_File_Job is triggered. We will use this trigger to kick off the import of the Excel file. We start by retrieving information from the File Out event in Step 1 followed by executing our price update stored procedure; dbo.sp_Demo_Excel_Price_Update

ALTER procedure [dbo].[sp_After_File_Job] 
(
  @jKey uniqueidentifier ,
  @lKey uniqueidentifier ,
  @error_level int 
 
)
as begin
set nocount on
SET QUOTED_IDENTIFIER OFF
set xact_abort on
--  Automatic Excel to SQL Import | Boomerang Notification Framework ----------
--- Evalute file out event ---------------------------------------------------- 
Declare @Path_File_Name nvarchar(500), @Received_File_Key varchar(40), @Received_From_Email varchar(100)
,@DateCreatedStr varchar(20) , @Received_File_Name varchar(255)
 
Select 
 @Path_File_Name = ouf.[Path] + '\' + CONVERT(varchar(40), em.Uid1) + '_' + em.Str1
,@Received_File_Key = convert(varchar(40), em.Uid1)
,@Received_File_Name = em.Str1
,@Received_From_Email = em.Str2
,@DateCreatedStr = convert(varchar(20), GETDATE(), 120)
from OUT_FILE ouf inner join EVENT_MASTER em on ouf.gKey = em.gKey 
where ouf.jKey=@jKey
and em.Source = 5 -- Excel Import 
and @error_level = 0 -- Sucessfull file out event
 
--- If excel price update template --------------------
if @Received_File_Name = 'PriceUpdate.xlsx' 
	begin
	exec dbo.sp_Demo_Excel_Price_Update @jKey=@jKey, @Path_File_Name = @Path_File_Name, @Received_File_Key = @Received_File_Key, @Received_From_Email = @Received_From_Email, @DateCreatedStr = @DateCreatedStr, @Received_File_Name = @Received_File_Name
	return
	end

Step 3 Import Excel File

This step will import the excel file using the MS OLEDB driver and the OPENROWSET function. The import is made to an intermediate table called Excel_Import created to provide a log of price changes. The insert query is dynamically constructed with the information saved in step 1 and retrieved in step 2. Note that SET QUOTED_IDENTIFIER needs to be set to OFF for the quotations to be parsed correctly.

ALTER procedure [dbo].[sp_Demo_Excel_Price_Update] 
(
  @jKey uniqueidentifier,
  @Path_File_Name nvarchar(500), 
  @Received_File_Key varchar(40), 
  @Received_From_Email varchar(100),
  @Received_File_Name varchar(255),
  @DateCreatedStr varchar(20)
)
as begin
set nocount on
SET QUOTED_IDENTIFIER OFF
set xact_abort on
 
Declare @SQLQuery NVARCHAR(500), @RowCount int, @gKey uniqueidentifier ,@jKeyO uniqueidentifier, @aKey uniqueidentifier, @BodyStr nvarchar (500) 
set @gKey = NEWID()
set @jKeyO = NEWID()
set @aKey = NEWID()
 
--- Automatic Excel to SQL Import | Boomerang Notification Framework ----------
--- import price updates ------------------------------------------------------
begin try
	--- construct openrowset query ----
	SET @SQLQuery = 
	"insert AdventureWorks.dbo.Excel_Import SELECT '" + @Received_File_Key+ "','"+ @DateCreatedStr + "','" + @Received_From_Email + 
	"', ProductID ,VendorId, AverageLeadTime, StandardPrice, null, null 
	 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=" + @Path_File_Name + "', [Sheet1$])"
 
	--- create table to be imported to if it does not exist ----
	IF NOT EXISTS (Select * FROM AdventureWorks.sys.sysobjects WHERE name = 'Excel_Import') 
		create table  AdventureWorks.dbo.Excel_Import 
		(Received_File_Key uniqueidentifier, DateCreated datetime
		,Received_From_Email varchar(100), ProductID int
		,VendorID int, AverageLeadTime int, StandardPrice float
		,AverageLeadTimePrevious int, StandardPricePrevious  float)
	--- execute import ----------	
	exec(@SQLQuery)
end try

Step 4 Update AdventureWorks database

The update of the AdventureWorks database is done in two steps in order to easily be able to track changes. First the Excel_Import table is updated with current prices from ProductVendor as ‘Previus’. Secondly the ProductVendor table is updated with new prices from Excel_Import.

begin try
	--- Update Excel Import Table with Current Prices ---
	update AdventureWorks.dbo.Excel_Import
	set 
	 AverageLeadTimePrevious = pv.AverageLeadTime
	,StandardPricePrevious =  pv.StandardPrice
	from AdventureWorks.Purchasing.ProductVendor pv
	inner join AdventureWorks.dbo.Excel_Import ei on pv.ProductID = ei.ProductID and pv.VendorID = ei.VendorID
	where ei.Received_File_Key = @Received_File_Key
end try
 
begin try
	--- Update Price table in AdventureWorks from Excel Import ---
	update AdventureWorks.Purchasing.ProductVendor
	set 
	 AverageLeadTime = ei.AverageLeadTime
	,StandardPrice =  ei.StandardPrice
	,ModifiedDate = CONVERT(datetime, @DateCreatedStr) 
	from AdventureWorks.Purchasing.ProductVendor pv
	inner join AdventureWorks.dbo.Excel_Import ei on pv.ProductID = ei.ProductID and pv.VendorID = ei.VendorID
	where ei.Received_File_Key = @Received_File_Key
	set @RowCount = @@ROWCOUNT
end try

Step 5 Send out notification

The last and final step is to send a notification to the user that originally sent the price update. To begin with we declare needed keys and insert a new record in EVENT_MASTER. This is followed by insert into OUT_EMAIL

--- Update Event Master to note that import job has been processed --- 
update EVENT_MASTER 
set 
 Dte1 = CONVERT(datetime, @DateCreatedStr) -- DateTime processed
,Int1 = @RowCount -- No of records updated
where Uid1 = @jKey
 
--- send out receipt of import --------------
set @BodyStr = 'Number of Lines Updated:' + CONVERT(varchar(10), isnull(@RowCount, 0))
 
email_out:
	insert EVENT_MASTER (gKey, Source, Created_By, Str1) values (@gKey, 5, 'sp_After_File_Job', 'Email Notification') -- Source 5 = Excel Import
	insert OUT_EMAIL (gKey, jKey, Subject, Body) values (@gKey, @jKeyO, 'Excel File: ' + @Received_File_Name + ' Import Receipt', @BodyStr)
	insert OUT_EMAIL_RECIPIENT (jKey, Email) values (@jKeyO, @Received_From_Email)
	insert EVENT_CONTENT (gKey, jKey, aKey, IsAttachment, Src_Type, [Path], Format) values (@gKey, @jKeyO, @aKey, 1, 2, 'Boomerang_Demo/Excel_Import_Receipt', 'XLS') -- IsAttachment 1 = Yes, Src_Type 2 = SSRS Report
	insert CONTENT_PARAMETER (aKey, Name, Value) values (@aKey, 'Received_File_Key', @Received_File_Key) --- reference the import file

Finally we update the release flag in the EVENT_MASTER to indicate that job can be released to be processed by Boomerang.

update EVENT_MASTER set Status = 0 where gKey = @gKey

Once the job have been released the message will be created and sent by Boomerang. The email looks like this:

The attached file sent with together with the email notification outlining the differences and the result of the import and subsequent update of the AdventureWorks database.

Dynamic Import of Random Excel Files

To import any Excel attachment where the number of columns, column names and formatting varies the following approach can be taken. After the File Out event we dynamically construct the import query into a temporary table.

ALTER procedure [dbo].[sp_Demo_Excel_Random_File] 
(
  @jKey uniqueidentifier,
  @Path_File_Name nvarchar(500), 
  @Received_File_Key varchar(40), 
  @Received_From_Email varchar(100),
  @Received_File_Name varchar(255),
  @DateCreatedStr varchar(20)
)
as begin
set nocount on
SET QUOTED_IDENTIFIER OFF
set xact_abort on
 
Declare @SQLQuery NVARCHAR(500), @RowCount int, @gKey uniqueidentifier ,@jKeyO uniqueidentifier, @aKey uniqueidentifier, @BodyStr nvarchar (500) 
set @gKey = NEWID()
set @jKeyO = NEWID()
set @aKey = NEWID()
 
begin try
	--- construct openrowset query ----
	SET @SQLQuery = "SELECT * into ##tmp_imp_20120808 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=" + @Path_File_Name + "', [Sheet1$])"
 
	--- run query ----------------------
	exec (@SQLQuery)

Result:

COLUMN_NAME          DATA_TYPE  CHARACTER_MAXIMUM_LENGTH ORDINAL_POSITION
-------------------- ---------- ------------------------ ----------------
PartNumber           float                               1
Column2              nvarchar   255                      2
Column3              float                               3
 
(3 row(s) affected)

Once the excel file has been imported into the temp table we query the temp table schema to retrieve column names and type. After that we assemble the create table syntax and run it.

	-- get table schema -------------------------
	select COLUMN_NAME, DATA_TYPE, isnull(cast (CHARACTER_MAXIMUM_LENGTH as varchar(100)), '') CHARACTER_MAXIMUM_LENGTH, ORDINAL_POSITION
	into #imp_table
	from tempdb.INFORMATION_SCHEMA.COLUMNS
	where table_name like '##tmp_imp_20120808%'
	order by ORDINAL_POSITION
 
-- assemble table schema --------------------------------------
declare @query_str varchar(max), @Column varchar(250), @DataType varchar(250), @ColumnLen varchar(250), @TableName varchar(50), @Pos int, @EoL char(1)
set @TableName = CONVERT(varchar(50), NEWID())
set @query_str = 'create table AdventureWorks.dbo.[Dynamic_Excel_Import_' + @TableName + '] (' 
 
begin try
	while 1=1
		begin
		select top 1 
		@Pos = ORDINAL_POSITION, @Column = COLUMN_NAME, @DataType = DATA_TYPE, @ColumnLen = CHARACTER_MAXIMUM_LENGTH
		from #imp_table order by ORDINAL_POSITION
		if @@rowcount = 0 break
		set @query_str = @query_str + '[' + @Column + '] ' +  @DataType + ' ' + case when  @ColumnLen != '' then '(' + @ColumnLen + ') NULL,'  else @ColumnLen + ' NULL,' end 
		delete from #imp_table where ORDINAL_POSITION = @Pos
		end
 
	set @query_str = left(@query_str, len(@query_str)-1) + ') '
 
	-- create table ------------------------------------------------
	exec (@query_str)

Result:

create table AdventureWorks.dbo.[Dynamic_Excel_Import_52D30EA1-EF25-439C-97EE-4779718496BC] ([PartNumber] float  NULL,[Column2] nvarchar (255) NULL,[Column3] float  NULL)

After the new table has been created we insert data from the temp table into the newly created table.

	-- insert data to new table ------------------------------------
	exec ('insert into AdventureWorks.dbo.[Dynamic_Excel_Import_' + @TableName + '] select * from ##tmp_imp_20120808')
	set @RowCount = @@rowcount

Lastly we send a receipt specifying the new table name and number of lines imported to the table to the individual how sent the excel file.

--- send out receipt of import --------------
set @BodyStr = 'Excel File: ' + @Received_File_Name + CHAR(10) + CHAR(13) +
               'SQL Table: [Dynamic_Excel_Import_' + @TableName + ']'  + CHAR(10) + CHAR(13) +
               'SQL Database: AdventureWorks'  + CHAR(10) + CHAR(13) +
               'Number of Lines Imported: ' + CONVERT(varchar(10), isnull(@RowCount, 0)) 
email_out:
	insert EVENT_MASTER (gKey, Source, Created_By, Str1) values (@gKey, 5, 'sp_After_File_Job', 'Email Notification') -- Source 5 = Excel Import
	insert OUT_EMAIL (gKey, jKey, Subject, Body) values (@gKey, @jKeyO, 'Excel File: ' + @Received_File_Name + ' Import Receipt', @BodyStr)
	insert OUT_EMAIL_RECIPIENT (jKey, Email) values (@jKeyO, @Received_From_Email)
	update EVENT_MASTER set Status = 0 where gKey = @gKey

To make things easier we add an attachment to the email that contains a simple select statement that can easily be opened in Microsoft SQL Server Management Studio. We start by adding the beginning of the select query

--- select query ----------------------
set @query_str_select = '/*          New object            */' + CHAR(10) + CHAR(13) +
                        'Select Top 100 '  + CHAR(10)

Then adding the columns

--- select query columns --------------
set @query_str_select = @query_str_select + '[' + @Column + '],' + CHAR(10)

Last step is to add a attachment to the outgoing email.

--- insert sql query attachment --------------
insert EVENT_CONTENT (gKey, jKey, Name, Src_Type, Stream) values (@gKey, @jKeyO, 'SQL Query.sql', 0, convert(varbinary(max), @query_str_select))

Now we get an email that looks like this:

With an attachment that can be opened in the Microsoft SQL Server Management Studio:

Conclusion

Boomerang Notification Framework takes away the chore of writing complex code to interface with notification infrastructure like e-mail, printers, file and fax servers. By means of the Boomerang Notification Framework IT Professionals can create robust and dynamic notification solution with a minimum amount of T-SQL code.

As demonstrated above we created a tailored and fully automated and dynamic Excel to SQL import using three core Boomerang features, Email In, File Out, Email Out.

About Boomerang Notification Framework

Boomerang is a collection of notification services for creating applications that generate and send, as well as receiving notifications. Using the Boomerang notification framework, you can quickly create applications to generate and send notifications to customers, suppliers, partners and employees.

The application interface of Boomerang is entirely based on SQL Server (MS) meaning that each notification service has a corresponding table object that represents the service in question. For example to send and email out you would insert a record into the OUT_EMAIL table. To send a fax you would insert a record into the OUT_FAX table.

Scenario Information

This application scenario is based on the following technologies: Boomerang (Basic Edition), SQL 2008 R2 (64 bit), SSRS, Microsoft Access Database Engine 2010 Redistributable (OLEDB driver), Excel 2010.

Configuration Checklist

Download Sample

Excel Import Sample

	Why Boomerang? Find out why IT professionals and successful businesses love Boomerang.
	Application Scenarios View examples of different business scenarios where notifications, alerts and communication will make a difference.
	How Does Boomerang Work? Understand the ins and outs of the architecture and how it will fit in your environment.
	Feature Summary and Pricing Plans Learn more about all available features and current pricing.
	Notification Awesomeness in 3 Easy Steps A step by step guide to build your first notification solution in less than 5 minutes.
	Feature (Code) Samples Learn how to use and combine different features to integrate and automate business processes.
	Download a Free 90 Day Trial Free Support, No credit card, no hassle. Get started today.