Updating SQL Tables from Excel

Most of our customers use XLCubed for reporting, analysis and data visualisation, but an increasing number use it for data updates, either within budgeting and forecasting solutions or in niche applications.

XL3RunSQL is a neat method for running SQL updates from Excel, and allows users to build input forms (which can ultimately be web-enabled) quickly and easily.

In this blog, we will walk through the steps to set this up.

Here’s an XLCubed Table based on a SQL query, showing employee details including their salary and commission:

Table showing Employee Details

We will use an XL3RunSQL formula to change and update each employee’s salary and commission.

Columns P and Q will hold the new values for commission and salary that will be used to update the table:

Table to input new values

The XL3RunSQL Formula has the following syntax:

=XL3RunSQL(ExecuteSQL, Connection, SQLStatement, [TableSheet], [Table])

To set up the parameters for each row to be used in the XL3RunSQL statement, add four calculations to the tables in columns K-N. We will then add some formulas to manage the update. Adding these to the table means that data growth is handled automatically.

The parameters are set up as follows:

1. ExecuteSQL

A cell reference that contains TRUE when the SQL Statement should be run. After execution, the cell reference will be set to FALSE or an error message if an error occurred.

We will create an XL3Link in column N so that when clicked, it will update a certain cell reference to TRUE, allowing the XL3RunSQL to run the SQL statement. Use the following formula:

=XL3Link(“”,”Update”,,XL3Address($L4), if(and($P4<>””,$Q4<>””),”TRUE”,”FALSE”)

This will set the cell L4 to TRUE only when there are new values in columns P and Q – this ensures the SQL query is only run when new values have been added.

2. Connection

The connection to use to connect to the database. This is normally the Connection ID of a workbook relational connection or a connection string.

From the XLCubed Ribbon > Connections:

3. SQLStatement

The SQL Statement to run.

We have set up the SQL statement in column K using a CONCATENATE formula:

=CONCATENATE(“update employees set salary = “, TEXT(IF($P4=””,$I4, $P4),0),”,commission_pct=”, TEXT(IF($Q4=””,$J4,$Q4),”0.00″), “where employee_id = “, TEXT($B4,0),””)

This formula will set the SQL Statement to update the salary and the commission to the new values.  If there are no new values, the SQL statement will not run and the values will remain unchanged.

4. TableSheet (Optional)

Worksheet name containing query table or XLCubed Table to refresh after the SQL has run.

5. Table (Optional)

The name of the query table or XLCubed Table to refresh after the SQL has run.

We can name the table by right-clicking on the table > XLCubed Properties > Behaviour

XL3RunSQL

We now have the parameters we need to set up the XL3RunSQL forumla.

We will set up our formula in column M:

=XL3RunSQL($L4,1,$K4,,”Employee_Details”)

Now we can see that every time we add values for the New Salary or New Commission, the table will update every time we click ‘Update’. We can also hide the calculation columns K-M to make the table look neater and not show any unnecessary information.

After we have input all of our data and updated the table, we can then clear the data in columns P and Q . This can be done using an XL3link, for example:

=XL3Link(“”,”Clear Inputs”,,XL3Address($P4:$P100),””,XL3Address($Q4:$Q100),””)

To make the range dynamic, you could also use a named range for both columns P and Q.

In the example below I have used a slicer to filter the information based on a specific Job Type. As you can see, since the formulae are included as Table calculations, the whole input table handles varying number of rows without the need for any manual copying of formuale.

Video showing values being updated

To utilise the same approach with stored procedures, the XL3RunSQLProc formula has a similar logic.

One Reply to “Updating SQL Tables from Excel”

  1. Hi, very interesting article. there is nevertheless an even more powerful XL3RunArea function which is very poorly documented but which allows you to go much further in data management (update,insert records) with Sql Server. this function doesn’t validate line by line like XL3RunSQL but only one trigger allows you to validate all the records. it is therefore possible to update very large tables, which is not really appropriate with XL3RunSQL. in addition this function XL3RunArea works with the pager which allows to display a precise number of records on the screen while having the possibility to go forward or backward in the records of the table.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.