Powerful validate and transfer of data to database table or stored procedure

Data edit, data validation, data upload, data processing, shared tasks

Introduction

Excel Database Tasks - (EDT) enables you to load - edit - validate and save or transfer data using the power of Microsoft Excel supporting SQL Server, Access, MySQL, FoxPro, Oracle and a growing number of other database systems.

Any data can be loaded (or even pasted) into the application, this may be from a database query or ANY data that can be accessed vis ODBC including text files such as CSV format.

The application can also be used in a data processing environment with multiple users.

Application features

Easy to use color coded/traffic light data validation
Active Legend to keep track of uploaded data
Tasks saved for easy repeat use
Data is validated as soon it is typed or pasted into Excel
Upload Excel data to a table or stored procedure process
Allow default values
Mandatory/must have fields can be specified
Allow user friendly column names
Allow excel formula / calculated fields
Multiple database type support: Microsoft SQL Server, Access, MySQL, FoxPro and others (to be tested)
Supports powerful custom SQL scripts, with SQL/Excel merge fields
Database (server side) validation checks ensure you comply with any rules defined within the database
For co-operative use, Tasks can be shared across a network
Task configuration is password protected
Edit data tasks
Edit / Insert data tasks
Network licencing
64 Bit Office support

Allow data initial load from ANY data source including data query, stored procedure, CSV and XLS files
Edit data tasks - edit any table or range of data with automatic primary key detection
Task parameters - parameters can be used as merge codes in load and send SQL and many other areas
'Work columns' for interim calculation or to hold imported values not directly mapped to a destination item
Formula validation
Error checking using Excel data validation
Cell format using Excel format options
Conditional format support
Fixed width and column wrap
Hide/display column
Easy to use connection wizard
Connection string and taks settings encryption
Resizeble config form layout
Multiple configuration folder support - folders can be on a network share
Progress indicator with estimated time to complete
Integrated help
White label - application icon can be replaced with your own logo

Version 1 requirements

PLEASE NOTE : the following information will shortly be updated to reflect EDT Version 2.5

Please see blog for new feature details

Microsoft Office : Microsoft Excel 2003 or any later version to Microsoft Office (32 or 64 bit).
Operating System : Windows XP or any later version.

Version 1 User Guidance

The application download zip contains two files – the application exe and one support file ‘SQL-Flavours.XL-DB.xml’.
The supporting file must be located in the same directory as the application.

Extract the files to a new folder on your computer.

We will explain the features of the Premier version by way of a walk through ..

Firstly we will look at a simple example of inserting new records directly into a table, then later we will show an example where we will pass data to a stored procedure.

Defining a Task

To start – click on the Icon “Configure”

You will be prompted for a password : enter “lean”.
(you can later change the default password)

The New task Settings dialogue will appear as below…

Enter a Task name, then Click “Select data source”.

You will find the tool will connect to a range of data sources – (the configuration settings for each type are held in the file mentioned earlier “SQL-Flavours.XL-DB.xml”).

Only select a User or Machine datasource - not a File datatasource:

After selecting your data source the tables or stored procedures will be loaded into the “Destination” dropdown.

Change the “Method” to see the Destination dropdown list alternate between stored procedures and tables.
[Note the options 'Edit existing data' and 'Extract data to excel' are not available in this version]

Once you have decided upon a destination, click next, or click the “Columns” tab.

Columns

As you can see from the above screenshot you can specify which columns or parameters you wish to include in the Task.

You can optionally enter default values, and specify a user friendly column name if you wish.

We will explain each feature by referring to the numbered items in the above screenshot:

Column order can be modified using the move up/move down buttons.
Data Items can be included or excluded from the spreadsheet.
Mandatory items can be specified.
Send to database - specifies that the item will be automatically included in the SQL merge statement.
Default values can now be an Excel formula - such as today's date, or refer to other columns using the excel R1C1 formula.
An example excel formula for today's date would be =text(today(),"dd/mm/yyyy").
Excel column name can be specified
The SQL Definition button will retrieve the destination object native script from the database.
This will be the table or stored procedure definition SQL script.
Note :The database command that retrieves the information is held in the SQL-Flavours XML file.
Not all databases have a suitable command, MS Access for example.
The SQL Merge Script button will test the task by merging the sql with the current contents of the spreadsheet.
Maximize/minimize buttons for viewing a large number of columns and long SQL scripts.
The Automatically Generate SQL option can be changed to Custom SQL, allowing SQL proficient users to build their own merge script.
The merge codes (eg:'{XL-merge:CompanyShortName}') can be placed anywhere within the script. Be careful to place quotation marks around string data types.

System

The system tab contains system wide/global settings.

Config Folder
Each task you define creates an XML file with the same name as the task, by default they are saved to the same location as the application.
The folder location for the task definition files can be modified to a shared network location if you wish.

Password
Allows you to change the default Admin password.
If you create a task for shared use, you can be safe knowing users cannot modify the task without the admin password.

SQL Flavours
Browse the SQL flavors XML file.
This file can be modified to support many different databases types.
Currently we have tested with SQL Server, Microsoft Access, MySQL and Fox - we would love to hear from you as we need help testing with other database systems and expanding/defining the SQL Flavours support file.

Traffic light / Color coded row status

The color codes have the following meanings:

Red - data type or mandatory item problem
Amber - data has passed preliminary validation, but requires database validation
Blue - data has passed database validation and can now be sent to the database
Green - data has been sent to the database

By design the application :

Allows database 'Validate' of rows that are status Amber.
Allows 'Send' of rows that are status Blue.
Prevents 'Send' of rows that are status Red, Amber or Green (already sent)

Active Legend

The Active Legend feature (introduced in Version 1.1.16 and above) provides useful features when processing and navigating a large number of rows:

displays the number of rows in each status band
click on the main legend colour button to select all rows in that band
click on the chevron to move to the next row within the colour band

Validation detail / status summary

Details of the row status, including any database validation messages are displayed in the first column.

Selection sensitive commands

All the command buttons ('Validate' , 'Send', 'Autofit', 'Clear' and 'Script') are selection sensitive.

For example, if we multi select just two rows as shown in this example:

If we now click the 'Validate' button, the action prompts for selection confirmation :

Automatic selection

If we do not select any range of rows, the application automatically selects all the rows that are valid for the chosen action.
For example, with no rows highlighted the Validate button selects all rows that require database validation:

Handling of SQL success/failure

The 'Validation' button submits the row to the database within a transaction, records the result, then rolls back the transaction.

Following on from the previous example, the database has a check constraint applied to the Days Credit field that will not accept a value less than 30.

Here we can see the row has been rejected by the database, and the corresponding error has been displayed in the first column:

Send to Database

The Send button will by default select all rows that are ready to send (coded Blue).

After confirming the send, rows are sent to the database and coded Green if successful:

Once coded green the rows cannot be re-sent or re-validated:

Clear Rows

The Clear button will remove all or just the rows you have selected.

Merge Script

The Script button will generate an SQL merge script for all or just the rows you have highlighted:

You can copy the script and run from a database scripting tool if you require, the same option is available within the Task configuration screen for testing the merge script.