Excel Database Tasks - Version 2.5 Released

Stable & blazing fast!

• New features
• Merge formatted value
• Automated upgrade
• Key option when no primary key available
• MySQL & MS Access: Edit and Insert support
• Other changes
• Bug fixes
• Download
• Conclusion & Next version

Merge formatted value

A new feature has been added to enable the merging of the Excel number format applied, using the EDT option 'Merge formatted value'.

Previously EDT would merge the underlying value, rather than the displayed value. 

This feature is useful, for example, when you wish to control decimal places, display big integer values or handle different date formats.

To merge the displayed value, check the Merge formatted value option, available for both column values and parameter values:

Screenshot:

Automated upgrade

New version upgrade options  :

• upgrade check can be via LeanSoftware.net or by comparing to a network version
• multiple client workstations can be kept up to date from a central network folder
• upgrade either automatically (no prompt) or on user confirmation. 
• EDT will check for upgrade availability upon start-up

Screenshot:

Key option when no primary key available

Certain ODBC drivers do not provide any primary key information, MySQL for example.

The generate Edit SQL functionality requires at least one primary key column.

For this reason a new 'Key' checkbox is used to define the primary key when the source table primary key could not be detected:

Screenshot, showing key selection and generated WHERE clause:

MySQL & MS Access - Edit and Insert table support

MySQL and Access will only support one command at a time, and does not support procedural type script statements outside of stored procedures, for example IF <clause> THEN <do this>.

An Update or Insert template (as shown in the above example) requires two statements: firstly an UPDATE, followed by an INSERT (in MySQL the IGNORE keyword ensures the statement does nothing if the row exists).

We have added a new keyword that can be used in any merge script, this is '#GO#', and essentially splits the SQL into separate commands. The command are sent to the database one after the other.

The same principle is used for the SQL generated for Microsoft Access, although the syntax used for MS Access has to be a little more convoluted, as shown below:

Screenshot: Microsoft access update/insert syntax

Other changes

• Button layout improved to maximize available work space
• Button spacing equal when Clear/Script/Help options enabled/disabled
• Performance improvements - in tests load and validate 20,000 rows in approx 4 seconds
• 'Hide Excel menus' workstation option now functions for all versions of Excel

Bug fixes

• Could not edit or paste into connection string when initially creating a task
• If user cancelled Configuration form, then re-loaded the selected task - message 'could not connect to database' was received
• If a data source returned no data, validation would not trigger when manually pasting in new data
• Button layout problems with certain screen resolutions
• Microsoft bug 'buttons re-size on resolution change' further addressed
• Standard SQL security (user name and password) connections would caused 'connection error'
• When a calculated columns value changes depending on another column, it was not automatically re-sized when manually entering or pasting data
• 'Test SQL Merge' button within the Configuration Worksheet tab button was not visible
• If the connection string contained Task parameters -  the merged connection string was incorrect
• Pasting a value into a task parameter would lock the task parameter cell

In conclusion we believe Excel Database Tasks version 2.5 to be stable, function rich and optimized for speed.  We hope you find the new features useful.

We are currently completing the SQL Lookup column functionality - allowing easy integration with existing relational / normalized data.

Following the release of SQL Lookup functionality, we will be completing the online help and generally improving the web site with examples and case studies.

Download

Available from the downloads area of the web site.

Regards to everyone

Lean Software team

http://leansoftware.net

New release : EDT 2.4

A new software release of Excel Database Tasks is now available - addressing issues relating to version 2.3.97.

As well as problem fixes there are some very significant software enhancements.

• New features
• Bugs fixed
• Help & Advice
• Help Desk & Forum
• Instant licence activation with PayPal
• New release

New features

• Validation engine speed / program flow:

The EDT validation code has been redeveloped to provide exceptionally fast data load and validation.

We are now seeing data load and validate of 10,000 rows taking just 2 seconds!

Modifications have also been made to deliver slick an efficient program operation.

• Total record count is now displayed below the active legend
• New task option within the More tab - 'Prompt to refresh task on Send'
• Task Method displayed in task summary

We have released this new version earlier than expected - as a result of pressing bug fixes for clients.
We were expecting to be able to release 'SQL Lookup column' functionality, allowing relational data edit, this however has been delayed, but is expected to be released in the next two weeks.

Bugs fixed

The last version 2.3, contained a great deal of new functionality, including data load and task parameters.  As a result a number of issues have been reported relating to these new features :

• Selecting a task would leave the dropdown list active
• Edit insert task - error messages on creating new task from 'New' option of task drop-down
• Destination column order change when initial data load is set
• Error message relating to function 'SyncSourceDest'
• Split/resize bar did not allow resize correctly on destination tab
• When modifying the destination, and task is edit/insert data the source sql was not automatically regenerated
• Clipboard would be cleared when switching between tasks
• Buttons would resize if certain fonts missing

Help and Advice

Many uses have been found for EDT in many companies worldwide.

We can help and advice on your particular application -  we are SQL Server experts - and can advice to highly technical level, including stored procedure design.

Help Desk & forum

If you have a software problem or suspect a bug - please use the help desk to create a ticket. 

We would like to encourage use of the lean forum for discussions or ideas or suggested improvements - there are some brilliant uses of the software - it would be great if you could share your ideas with other companies

You might report an issue or simply ask for help.

Instant licence activation with PayPal

When signed into the LeanSoftware.net web site - you can see your licence details in on the My Licences page.

When purchasing via Paypal your licence will be immediately activated.  After purchase, simply restart your EDT application.

Next release

The release as mentioned will include SQL Lookup column - making it easy to create end user relational data interfaces.

The Online help in development - and will be a valuable resource with explanations, examples, roll out methods and tips and tricks using powerful excel formula and Work columns...

Have a great weekend

Service release for Excel Database Tasks

• New feature / solution - 'Process modified rows only' when sending to stored procedure
• New feature / solution - addressing problems with triggers 
• Issue with Excel 2007-2013  - resolved
• Issue with DSN name connections - resolved
• Issue with pasted data not auto resizing columns - resolved 
• Error reports - not completing web error report form correctly - resolved
• Download 2.3.97
• Next planned release - SQL Lookup columns

New feature / solution - Process modified rows only when sending to stored procedure

When creating a Data Edit task with a table as the destination, EDT will only process rows that have been changed.

The effect being that the row status colour only changes after row edit.

It was noted that this functionality would also be very useful when sending data to a stored procedure.

A number of clients prefer to send data to a stored procedure rather than directly to a table as this allows the stored procedure to perform additional validation checks. 

We have added a new option to provide this functionality on the config 'More' tab :

New feature / solution - Address problems with triggers 

The EDT application determines a 'Rows affected value' when validating SQL with the database.

By default the application will regard  'rows affected 0(zero)' to be invalid.

It was found that the 'Rows affected' value can not always be reliable when the destination table has associated SQL triggers, as the triggers themselves may update data and modify this value.

The same may be true when sending data to a stored procedure that performs multiple updates.

There are two solutions.  The first (if using SQL Server) is to place 'SET NOCOUNT ON' at the start of the trigger or procedure code.

The second solution is to make use of a new option we have added 'Pass validation when no rows affected':

The feature 'Hide excel menus' on the configuration 'More' tab caused problems with Excel 2007 - 2013.

The problem has been resolved but this feature will now only operate with Excel 2003, we will later make this available for all versions of Excel.

Issue with DSN name connections - resolved

A number of issues relating to the use of ODBC DSN names in the connection string have been resolved.

Note that are still issues when using a non trusted connection string and there is no password or username in the connection string.

Th EDT software will prompt for credentials - but this is not fully tested - so for now (if not using the recommended  trusted connection) the please ensure the user name password are contained in the connection string. For security purposes check the 'Encrypt task settings' option on the configuration 'More' tab.

Issue with pasted data not auto resizing columns - resolved 

In the last release columns did not resize correctly when pasting in data.

Hint!

Please note that if you are pasting/loading numeric values it is often preferable to set the column format to text - to prevent Excel from reformatting the number (e.g adding an exponential or removing leading zeros).    To do this click the column display format option and select Text :

Error reports - not completing web error report form correctly - resolved?

If the application encounters a problem you will see the message (test example) :

Clicking Yes should take you directly to the help desk web form and all of the error details should be completed for you as below :

If this is not working correctly with this new release - please let us know.

We are aware that this will not work with IE6 - and ask you to upgrade your browser to a later version (IE 7 and above).

Download EDT 2.3.97 - link

You can download directly from via the downloads area on the website.

Next planned release - SQL Lookup columns

We are currently working on the SQL Lookup column functionality - making it easy to display drop down lists of relational data, or to pull data from other databases into one task.

Have a great day..

Lean software team

http://leansoftware.net

New for 2013

Excel Database Tasks (EDT) 2.3 has been released.

There are some cool new features making EDT a valuable asset for anyone involved in providing database solutions..

Example use cases:

• Shared edit data tasks
• Load data tasks - from any data source
• Load - Validate - send to table or stored procedure
• Data cleansing with client and server data validation

To setup a data edit task - simply choose a destination table in the task (the table must have at least one key column)

• Update/Insert SQL code is automatically generated
  
  
• Data can be restricted by adding task parameters the user completes on task load - to search or load a range of rows
  
  
• Interface initially shows 'white' status for each row, then changes as data is edited to Amber, then Blue when validated, Green when sent
  
  

Example data edit task in action:

Data load / Data transfer

• Load and transfer Data from any datasource - query / CSV file/ stored procedure
  
  
• Data source can be anything that you can connect to via ODBC
  
  
• Transfer to table or stored procedure - handles all validation relavent to the table, or validation programmed into the stored procedure
  
  
• Load CSV data using the Microsoft Text driver

Any number of task parameters can be setup, parameters can also be hidden and be used to calculate summary values.

Lean News

Excel Database Tasks (EDT) V2.2 will be  released in the first week of January 2013

V2.2 brings new easy to use database task features including : 

• Load, validate and process data from CSV, XLS, database query or stored procedure
• Send data to table, stored procedure CSV or XLS file
• Edit data tasks
• Define user task parameters 
• SQL lookup columns
• Define user task help 
• Faster processing
• Easy online 'roaming' licence management
• Online Help Desk / Ticket system

We will be sending full details, and offering online demonstrations in January - you will receive an email as soon as V2.2 is available for download.

We wish to say special thanks to all our clients have been involved in beta testing of V2.2, all input and feedback has been valuable and greatly appreciated.

We welcome new clients who have joined us from the legal sector, motor manufacturing and construction sectors in the UK, US and Germany.

Above all we wish you a very happy Christmas & New Year.

Lean Software team 

http://leansoftware.net

Excel database tasks - Development update

We are close to releasing the V2 beta 2, as promised in November 2012.

Functionality includes the new Task parameters and data load options, as well as SQL Lookup columns and Network licencing:

Preview : Task parameter setup:

Preview : Task parameters in action:

Preview : SQL Lookup definition:

We will keep you informed and let you know as soon as the new release is available...

Best regards

Lean Software team

Important update for non US/UK versions of Excel and Excel 64 bit

A new (recommended) beta update is now available (Version 2.1.114b released at 17:12 GMT today).

Changes in this release

The following have been dealt with as a result of company feedback:

1. 64 bit version start-up issues have been resolved
2. Progress bar would not appear center screen with multiple monitors 
3. Some localised (eg Spanish/Portuguese/Swiss) versions of Excel would report error :Invalid Procedure call Line 40 of Workbook Open
4. Users were unable to manually resize columns
5. Worksheet was not correctly protected
6. Further performance improvements (process 1000 rows in approx 1.5 seconds - hardware dependent)

New Utility

A new utility has been added to the download ExcelVersionText.exe that you may find helpful in determining if your workstation is is running the 32 or 64 bit version of Microsoft Excel.

Single Download

There is now one single download containing both the 32 and 64 bit version of the EDT software.

Special thanks to particular clients in Brazil, Spain, and the Netherlands for helping resolve problems with local versions of Excel.

Best regards

Lean Software team

Many thanks for all the positive feed back for V2!

Special thanks to companies who have been involved in initial Beta testing over the past week or so.

First round of tests has uncovered some minor glitches - these we have fixed and have released a new version.

In addition we now provide a 64 bit version (only for use with Microsoft Office 64 bit).

We have also responded to initial client requests for change.

Release details 2.0.982 (update & bug fixes)

In this release

Problem fixes for first beta release:

1. When using the task ‘New’ button in the config form it was found that the task GUID (a unique number stored in the task file) would not be correct for the task, it would in fact use the GUID from the previously loaded task.
2. This had the effect that the main application task dropdown would not always reposition correctly after editing a task.
3. If a formula was removed from a column in the task configuration the formula would still persist in worksheet.
4. If a formula calculation resulted in an error, modifying the formula in the configuration form would not be reflected in the worksheet.

Additional Features:

64 Bit Microsoft Office Support !

New task options -

• Prevent send until all rows are valid
• Allow user to modify default formula

New formula editing & testing popup

Have a fabulous day!

Lean Software team

News from Lean Software

In this newsletter

• Excel database Tasks Version 2 beta release!
• Download
• Licencing and support options
• Interesting business uses

Excel database Tasks Version 2 beta release

We are delighted to inform you that the original concept product 'Excel to database' has been extensively redeveloped and is now available as 'Excel Database Tasks' V2.

There are some very cool new features - including seamless integration with Excel data validation, work columns and conditional formatting.

V2 is being delivered in two beta phases. Improved validation has been our focus for phase 1, and in phase 2 we will see the much requested initial data load options, task parameters and network licence support.

Here's a list of what's new..

Beta Download

You can download the latest build of V2 Beta 1 version now from the downloads area: http://leansoftware.net/en-us/productsdownloads/exceltodatabase/download.aspx

V2 Licencing and support options

V2 introduces network licence options, simplifying IT maintenance and now includes the cost of client support.

The new licencing details are available on the website:

http://leansoftware.net/en-us/purchase.aspx

Interesting Applications

We are seeing some very interesting business projects adopting the software:

• replacing existing troublesome data imports lacking validation
• as an application interface
• data maintenance
• mass data keying
• generating test data

The introduction of data load and task parameters in phase 2 will open the doors to many other uses too.

Beta feedback

A new area has been created on the website forum for beta feedback - it would be helpful for all firms if we could use this and to share experience / knowledge and tips.

http://leansoftware.net/forum/en-us/communityforum.aspx

Version 3.0 ?

Looking to the new year and Version 3.0 - this will include the ability to automate/schedule tasks, with email alerts containing a link to the EDT application so that a user can deal with any invalid data and complete the task.

This we believe is a superior solution to SQL Server Integration Services (for example) where failed imports are difficult to troubleshoot.

That's it for now, have a superb evening !

Lean Software Team