See also::

How to Import Excel spreadsheet to SQL Server database

How to Validate and Import from Multiple Excel Worksheets to SQL Server database

How to Validate and Import from Multiple Excel Workbooks with 'Select File' user prompt

How to Import Excel spreadsheet using Stored Procedure

Serious Real World example - Import and process BANK Statements!

Welcome In this tutorial we will help you complete the task of importing or processing data from a Comma Separated Value (CSV) file. 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, and we may well be able to help you. In this first example we show you how to import to a table, and how to update a table from a CSV file, in a later example we explain how to send CSV data to a stored procedure.  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

If you wish the task to allow user selection of the source file from a directory please see this example

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

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

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 CSV data is the fact that fields may missing or contain invalid data that renders the whole file invalid. EDT efficiently overcomes all these import problems improving business efficiency and data accuracy.

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

• validate values against existing list or sql data

Here is the example source data we will use for tutorial:

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.

Select ''Insert or update table from any source''

Now give the task a name and then click on new to connect to your destination SQL server database:

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 table

Using ODBC is just one way of connecting to a CSV / text file.   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  CSV / Text File 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:

• Provide a Data Source Name.
• Un-check the 'Use current directory' option and select the directory where your CSV file resides 
• Select OK to close the ODBC administrator and return to EDT.

Select New..

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

\

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

Click OK to return to EDT:

Click the Map Columns button

In the example 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 columns.

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 8 - 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 9 - Excluding unwanted Destination columns 

On the Destination tab for unwanted columns, 

• uncheck the Mandatory option, 
• uncheck the Include in SQL option
• uncheck the Visible option

Notice that the SQL on the right hand panel is automatically updated to include only the columns required.

STEP 10 - Custom SQL to concatenate First and Last name fields

To concatenate the first and last name into one column we simply modify the Source SQL to append two fields using the ampersand operator , separating with a space:

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

STEP 12 - Remove unwanted rows

Often a CSV file will contain unwanted rows, these might be blank rows or rows 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 CSV filet.

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:

Validation with Lists

In our example we are importing a Country value, but it must match with a valid country name in an existing Country table on SQL server.

We can easily use EDT sql lookup column capability to validate these values.

Click the SQL list button on the Country row item :

Choose options as shown below to create a Lookup column:

The option shown at the bottom of the dialogue Show validation error of column value not in list must be ticked for lookup validation to take place.

Returning to EDT as you can see below, it's now obvious there are some spelling errors in the country name data:

The user can simply click on the country cell and select the correct country name from the drop down list!

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

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 15 - Using the Send button