Welcome In this tutorial we will help you complete the task of validating and processing data from a Microsoft Excel Workbook File using a Microsoft SQL Stored Procedure. We will demonstrate how to configure a task to complete this goal, and be able to quickly repeat the process time and time again.   If you need help with this task, drop us an email to Support@LeanSoftware.net, with an example spreadsheet, and we may well be able to help you.   Setting up the task is a do it once process - EDT saves all your task settings into a task XML file.  The next time you want to repeat the process you simply select the Task from the main EDT interface.

You can later fully automate / schedule the task using EDT exe command line options, and even launch the task from a task report email hyperlink. See this page for command line details

Be aware before we start - What are the pitfalls of importing Excel data into SQL Server?

Often companies experience intermittent data import failures when trying to import Excel data.

Your database administrator will be quick to tell you there are going to be problems if you plan to do this regularly.

So how can we make this a secure and reliable process?  How can the EDT software help you?

Validation, validation, validation..

• With a visual interface that allows you to correct the data
• Automatic validation highlighting any problems on a per-row basis.
• Validation is not just data type validation - but validation with the destination database itself - this being wrapped in a transaction that is always undone on any validation problem.
• Any number of validation rules can be added.
• Data can be sent to a stored procedure for more advanced validation, the procedure can return validation error messages.

How does EDT compare to other import techniques?

Compared to any other import method EDT gives a huge advantage, and provides a highly reliable import method.

The reason companies have problems importing Excel data is the fact that users can modify data formats or make other changes that render the data or file invalid. EDT efficiently overcomes all these Excel import problems improving business efficiency and data accuracy.

Worked example - Transfer Excel worksheet data to SQL Server Table using a Store Procedure

Here we assume you have already downloaded the EDT software (you can run this example even with the trial version, please see download page).

In this example we will load, modify, validate and upload this sample worksheet:

We will see how EDT can help you to easily :

• map columns

• exclude unwanted columns

• exclude unwanted rows

• validate column values

• highlight and reject rows with missing essential data

Example source data:

 

Here is the SQL Stored Procedure we are going to use in this example.

USE [test]

GO

/****** Object:  StoredProcedure [dbo].[sp_Import_WebUsers]    Script Date: 7/10/2016 3:48:55 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_Import_WebUsers]

       -- Add the parameters for the stored procedure here

       @WebCustomerID Integer,

       @Email varchar(100),

       @Country varchar(150),

       @Telephone varchar(100),

       @FirstName varchar(100),

       @Surname varchar(100),

       @CompanyName varchar(200)

 

AS

BEGIN

       -- EDT Import Web Users file

       -- UPDATE existing Customer records

       -- INSERT new Customer records

       -- Validate the data

       -- Return any validation messages to EDT using simple SELECT statements and Return code <> 0

       -- ==============================================================================================

      

       -- VALIDATION

       -- Validate Country

       IF NOT EXISTS (SELECT Country FROM Country WHERE Country = @Country)

       BEGIN

              SELECT 'Country name does not exist'

              RETURN -1

       END

 

       --***

 

       -- EXISTING Customer ? - UPDATE DATA

       IF EXISTS(SELECT WebServerUserID FROM Customer WHERE WebServerUserID = @WebCustomerID)

       BEGIN

              UPDATE [dbo].[Customer]

              SET [Email] = @Email,

              [Country] = @Country,

              [Telephone] = @Telephone,

              [Name] = @Firstname + ' ' + @Surname,

              [Company Name] = @CompanyName

              WHERE WebServerUserID = @WebCustomerID

       END

       ELSE

       BEGIN

              -- NEW Customer ? - INSERT DATA

              INSERT Customer([WebServerUserID],[Email],[Country],[Telephone],[Name],[Company Name])

              VALUES (@WebCustomerID,@Email,@Country,@Telephone,@Firstname + ' ' + @Surname,@CompanyName)

       END

 

 

       RETURN 0 -- Return code zero by convestion indicates procedure success

END

The store procedure example above validates the country name and here you can see the invalidated rows highlighted in yellow after clicking the validate button.

 

Stored Procedure Features

EDT calls the Stored Procedure for each row of data to be processed.

Here are the common Stored Procedure Features that work with EDT;

• Create parameters for each destination data column
• Validation Checks of any kind, referencing other tables if required
• Return messages to EDT using simple SELECT statements
• Insert or update new rows depending on data checks
• Optionally insert or modify multiple tables
• By convention return a status code of 0 for success or -1 for failure
• Note that when the EDT interface is Validating data it always wraps the Stored Procedure in a transaction and automatically rolls it back
• When Sending the data the EDT interface again wraps the Stored Procedure in a transaction and commits a batch of Stored Procedure Calls on success.
• Optional EDT Mode Parameter can be used for differing Validate/Send EDT Operations

 

 

 

STEP 1 - Create New Task

Before creating a task we must first sign in to EDT to unlock its features. 

The default Password to log in is ''lean''.

Once logged in select the ''New task'' option which has been made available.

 

 

Give the task a name then select ''Insert or update table from any source''

 

 

Now click on new to connect to your destination SQL server database:

 

STEP 2 - Connecting to SQL Server

In this example we will use one of the Native SQL Server drivers:

Enter the essential connection details only :

• Name of your SQL Server
• Connection method
• Select Database name
• Test the connection

STEP 3 - Select Destination Stored Procedure

 

The Row auto-generates SQL, the merge codes represent cell values within each row. You can use the sql radial button to insert special merge codes. 

 

NOTE : The chosen destination partly determines the layout of columns in the task EDT can thought of as 'using Excel as a Workspace', the layout of the workspace is determined by the data Destination,  the data displayed (from the data source) is overlayed on top of the destination layout and is determined on the 'Data source / SQL' configuration tab. A task does not have to have data source, data can be pasted into a task from the clipboard if you wish, but generally it is much better to use the data source tab as data can be imported from any source.  Using the 'Destination columns / SQL tab screen' as shown above you can: Define task column layout using drag and drop of the column name Hide unwanted columns Exclude unwanted columns from the SQL Insert what we call 'Work columns' - a user defined column that can be used for several purposes - a calculated value for example. Apply validation and formatting rules can be applied (data type validation is automatic) Define display formatting for example dates and money amounts Create 'SQL lookup' columns that can retrieve existing values from the database that match the imported data  And more! We will stick to the basics for the purpose of this tutorial.

 

STEP 4 Create the ODBC datasource

Using ODBC is just one way of connecting to Excel spreadsheet data.   You can use alternative connection methods, and you can paste a connection string into the connection string text box.  Please see the EDT reference section Excel Data Source Connection Strings

 

To connect using ODBC, Click the 'Data source / SQL' tab:

 

Click ODBC 32 to create a new ODBC connection to launch the windows ODBC administrator.

Click Add in the ODBC administrator:

 

Select the correct Excel ODBC Driver : In this example we are going to use the 'Microsoft Excel Diver (*.xls)

 

Provide a Data source name and select the XLS workbook using the dialogue, select OK to close the ODBC administrator and return to EDT.

STEP 5 - Select the ODBC data source

Click the New button 

 

Select provider Microsoft OLE DB Provider for ODBC Drivers, click Next..

 

Select the ODBC Excel data source that you created, click Test

Click OK to return to EDT:

 

 

As shown in the above screenshot you will now see now how easy and brilliantly flexible it is to select data from the Spreadsheet Worksheets using EDT ! You can select a specific worksheet using the Object Source dropdown SQL is generated for you and by default all columns are included If you click the 'Custom' SQL radial option - you can manually edit the SQL as you require You could modify the SQL to only select a sub set of the data for example by adding a WHERE clause The SQL can be modified to only select certain columns form the worksheet Add blank columns if you wish by using the NULL keyword eg SELECT [WebCustomerID], NULL, [Email].. Click the 'Test' button to check the SQL syntax and view the first 100 records  You can click the Map Columns button to reorder the select list using drag and drop - showing you a mapping between the destination and source columns You can insert Task Parameters using the parameter insert button  this allows you to load data matching user parameters - such as a category or date range (User parameters are created using the Parameters tab)

 

STEP 6 - Column Mapping

Click the Map Columns button

 

In the example above shown above we have initially aligned the columns that match exactly.

You can drag and drop to alter the position/order of both the source and destination Stored procedure parameters.

We now need to exclude the unwanted columns, this will complete in step 8 and 9.

Notice the source has a First name and Last name, but the destination only has a Name field, this we will deal with in Step 10.

 

STEP 7 - Excluding unwanted Source columns 

Click the custom SQL option and remove the unwanted source columns from the SQL:

Click Test SQL to verify the syntax and view the data that will be returned by the query:

 

STEP 10 - Test the Task

Save the task and return to the main EDT interface, and we will see the data loaded and ready to be imported.

STEP 11 - Remove unwanted rows

Often a spreadsheet will contain unwanted rows, these might be blank rows or row that contain subtotals for example.

As you can see in the above screenshot our data contains some blank rows at the end, these we will remove using the very useful SQL function ISNUMERIC() field to check for a value in column in the spreadsheet.

 

STEP 12 - Add data validation rules

There are several ways that EDT handles data validation within your task - ensuring the data is of good quality and good news for your business! Data type validation (automatic) Mandatory / must have values One or more specific validation rules by column using the power of Excel Data validation Validation by batch - specifying that all rows rows must be valid before the Send button operation is used Validation using fixed lists or SQL lookup Lists Server validation - automatic by use of the Validation button Additional server validation - applicable when sending data to a stored procedure

 

In this example we will add three validation rules:

• Email address must be supplied

• Name bust be more than two characters long

• Country must match one of our existing entries the County table, and the allow the user to correct the data by selecting from a drop down list

 

Marking an item as Mandatory

This is very easy - just click the Mandatory checkbox next to the data item name:

When the data is loaded into the task, any missing mandatory data is highlighted.

Notice that the Active Legend Red status shows a count of two rows .   You can use the legend up/down arrows to very quickly locate the rows in each status band.

The left hand column shows the user what is wrong, and the problem cell has a red border:-

Using the power of Excel Data Validation!

Here we will add a validation rule specifying the Name value must be more than three characters:

Additionally, on the Error Alert tab, enter an error description for user feedback:

Notice that multiple validation messages appear when on row 16 of this example, as two column values are invalid:

 

STEP 13 - Using the Validation button

In order to validate the task data with the destination SQL server database, click the EDT Validate button:

<img alt="validate data sql server data cleansing" src="http://www.leansoftware.net/portals/0/Images/V3/New%20Demo%20Images/new24.png" width="100%;max-width=300 /><br /> </a></span></p> <a onclick=" href="http://www.leansoftware.net/portals/0/Images/V3/New%20Demo%20Images/new24.png" />

Rows that are ready to be validated are automatically selected, rows with validation errors are not selected:

(note that the Batch tab allows you to configure this behaviour, you can also remove the user prompt on the More tab)

 

STEP 14 - Using the Send button

Click the send button commits the operation to the database actually inserts the new data, rows that have been sent change to status green:

Thats it!  you have added the data to the database.

 

Conclusion Questions? - please feedback below or contact us directly for more information.   Best Regards Lean Software Team