SQL Server Import to Excel using Excel Add-Ins

Introduction

In this article, we will discuss SaveToDB Add-In for Microsoft Excel.

The SaveToDB add-in allows connecting to stored procedures and changing a group of connections strings of existing Excel data tables.

The add-in adds the following features to Microsoft Excel right after install:

  • You can change the query of a data table using the ribbon Query List.
  • You can change the values of stored procedure query using the ribbon.

Using server-side configuration views and stored procedures you can:

  • Use stored procedures as Excel event handlers to implement the drill-down feature.
  • Use translation views to translate Excel table column names to a user language dynamically.
  • Use translation views to translate database object names to a user language.
  • Use configuration views to filter database objects available to users.
  • Use views as data sources for stored procedure parameter controls.

The add-in allows creating custom views for database queries that significantly reduces user requests to database developers.

The SaveToDB add-in places the SaveToDB tab to the Excel ribbon and looks like this:

SaveToDB Interface Look

The Table Views group also are duplicated at the Data tab. See details below.

Table of Contents

Connect to DB Wizard

Tables, Views, and Stored Procedures as Data Source

The SaveToDB add-in contains a powerful and configurable Connection Wizard.

You can easily connect and reconnect to any database object including the stored procedures.

Using the built-in Excel connection wizard, you cannot select a stored procedure as a data source.

The result of the SaveToDB connection wizard is a native Excel ListObject with QueryTable connected to a database table, view, or a stored procedure.

Connect to Database Wizard - Connect to database

Connect to Database Wizard - Select default Query List and database object

Database Objects Filtering using Query List View

By default, a user can see and select any database object with SELECT or EXECUTE permission.

You can write certain views that filters database objects available to select.

For example, the xls40.viewQueryList view filters the objects meaningful for business users.

Connect to Database Wizard - Select Query List and database object

You can configure query lists using views with the following fields:

  1. TABLE_CATALOG
  2. TABLE_SCHEMA
  3. TABLE_NAME
  4. TABLE_TYPE
  5. INSERT_PROCEDURE
  6. UPDATE_PROCEDURE
  7. DELETE_PROCEDURE

The last three fields are used as database data update procedures.
See Excel-SQL Server Import-Export Using Excel Add-Ins for details.

Pay attention to the Enable ribbon Query List for this sheet checkbox.

If a user enables this option, then he can change the query using ribbon Query List instead of the Connection Wizard later.

Database Object Names and Descriptions

Database object names are good for database developers but not suitable for business users often.

Sometimes, the developers do not remember the meaning of the objects.

So, recognizable business names and descriptions of the database objects is a good practice.

Database Connection Wizard - Connecting SQL Server from Excel

SaveToDB reads translations from views with the following fields:

  1. TABLE_CATALOG
  2. TABLE_SCHEMA
  3. TABLE_NAME
  4. LANGUAGE_NAME
  5. TRANLSLATED_NAME
  6. TRANSLATED_DESC
  7. TRANSLATED_COMMENT

The translation feature supports not only "business" names but translations to any language.
Just specify a preferred data translation language in the Options dialog.

You can use your implementation of translation infrastructure. Just prepare a view for the add-in.

You can find a complete example of a translation service implementation in the SaveToDB SDK.

Change Connection Strings Wizard

You can change a group of connections strings using the SaveToDB Change Connection Strings Wizard.

Step 1. Connect to a new server and a database.
Step 2. Select the required data tables.
Step 3. Click Finish.

Change Connection Strings Wizard - Connect to database

Change Connection Strings Wizard - Select tables for change

The wizard makes the changes and reports about results.

Change Connection Strings Wizard - Result message

Query Change using Ribbon

If you enable the ribbon Query List for the sheet in the Connection Wizard, you can change a query right in the ribbon Query List.

Change Query using Ribbon Query List

By default, the native database object names are used.

However, if a translation service is implemented and activated, you can use business names instead of database ones.

Change Query using Ribbon Query List Translated

When you change the query, the add-in saves and restores the following attributes:

  • Formula columns.
  • Columns width and visibility.
  • Regular and conditional formatting.
  • Applied auto-filters.
  • Applied sortings.
  • Cell validations.
  • Table totals.

In facts, you have tables after query change as they looked before.

This feature allows using a few worksheets only for any number of database queries.

SaveToDB does not restore external formulas and links. So, if you use table data in other Excel objects, just disable the ribbon Query List for a sheet.

Stored Procedure Parameters Change using Ribbon

Stored procedures are the most powerful database objects for reporting.

Using stored procedures you can make dynamic pivot tables and dynamic columns reports.
Moreover, you can use parameters.

Excel itself has no UI controls to connect to stored procedures and to change their parameters.

SaveToDB automatically defines procedure parameters and places them to the ribbon.

On the screenshoot below, the xls50.uspStockComparison procedure has 5 parameters: @Symb1, @Symb2, @Symb3, @StartDate and @EndDate.

Change Stored Procedure Parameters using Excel Ribbon

SaveToDB stores parameter values history, and you can select a value from the list.

Also, you can specify a data source for any parameter using the configuration view with the following fields:

  1. SPECIFIC_CATALOG
  2. SPECIFIC_SCHEMA
  3. SPECIFIC_NAME
  4. PARAMETER_NAME
  5. SELECT_SCHEMA
  6. SELECT_NAME
  7. SELECT_TYPE

For example:

Stored procedure parameters data source configuration view

The T-SQL code of this view:

SELECT
    p.SPECIFIC_CATALOG
    , p.SPECIFIC_SCHEMA
    , p.SPECIFIC_NAME
    , p.PARAMETER_NAME
    , t.TABLE_SCHEMA AS SELECT_SCHEMA
    , t.TABLE_NAME AS SELECT_NAME
    , t.TABLE_TYPE AS SELECT_TYPE
FROM
    INFORMATION_SCHEMA.PARAMETERS p
    INNER JOIN INFORMATION_SCHEMA.TABLES t
        ON REPLACE(t.TABLE_NAME, 'viewParameterValues_', '@') = p.PARAMETER_NAME
WHERE
    p.PARAMETER_MODE LIKE 'IN%'

You can find a complete example in the SaveToDB SDK.

Database Objects Translation

User Names and Descriptions of Database Objects

We talked about translation service briefly in the Connection Wizard topic above.

There is an example of a translation view:

Database objects translation view example

You can define the following named cells on a worksheet, and SaveToDB updates these cells after query change:

  • SaveToDB_Object - a query object name
  • SaveToDB_Name - a translated object name
  • SaveToDB_Desc - a translated description
  • SaveToDB_Comment - a translated comment

So, you can provide business names, short descriptions, and full comments for database objects in different languages using translation database views.

The example view uses values from a regular database table that can be edited using Excel with SaveToDB. You can find this example in the SaveToDB SDK.

User Names of Database Object Columns

SaveToDB can dynamically translate Excel table column names if a translation view is available.

There is an example of the translation view:

Database object columns translation view example

This feature solves the eternal problem of column name changes wanted by users. Business users can modify the columns names themselves as often as they want; as database developers, we do not care about this.

Drill-Down

You can use stored procedures as Excel event handlers.

You have two options to implement drill-down:

  • Using Double-Click event.
  • Using Context Menu event.

There is an example of the Context Menu drill-down menu:

Excel Datatable Drill-Down via Context Menu

A server configuration view is used to assign stored procedures to events of specific queries.

See the example of the configuration view:

Excel event SQL Server handlers configuration view

As you can see, the xls50.uspStockHistory stored procedure is used to handle the ContextMenu event on any column of the xls50.uspStockPerformance object. The TradeHistory worksheet is used to show the results.

You can check the loaded configuration using SaveToDB Options dialog.

Excel event SQL Server handlers configuration view loaded by SaveToDB

As you can see, the xls50.uspStockPerformance object is opened on the Reports and Performance sheets.
The handlers are used on the both sheets.

Pay attention that a user see "Stock Trade History" menu item instead of "xls50.uspStockHistory" as a name of the handler. SaveToDB uses translated names of database objects if possible.
See discussed above User Names and Descriptions of Database Objects topic.

Data Filtering and Custom Table Views

When we develop a database application, we have two ways:

  • To develop a few server-side mega-views and to customize the results at the client-side.
  • To develop a lot of required views at the server-side.

"I need only this, this and this columns, and only rows matched these conditions." - said the user.
"You can hide the other columns and apply auto-filters." - said the developer.
"It's a waste time for me!" - said the user.
"It's outside of the project scope, and it increases the development and maintenance costs." - answer the developer.

Unfortunately, the users win often, and application lifecycle costs increase, increase, increase...

Fortunately, you can save sets of applied auto-filters and visible columns.

Further, a user can quickly apply the saved set to a data table right in Excel using the ribbon menu:

Table Views - Views List

Alternatively, using the Context menu:

Table Views - Context Menu

Just one click, and a new table is ready!

Table Views - Applied View

This feature eliminates user's waste time and saves the developer's time.

Of course, a user can add and control the views itself.

Table Views Views - Options Dialog

Conclusion

Using the SaveToDB add-in, you can transform the Microsoft Excel to a feature-rich database application easily.

Start with default options, then add required features using only server-side configuration views and stored procedures.

Download

SaveToDB Add-In for Microsoft Excel
SaveToDB Add-In for Microsoft Excel
The add-in allows using Microsoft Excel as a client application platform
Version: 10.14 | 04/30/2024 | 18.1MB | Getting Started | Editions | Article 1 | Article 2
Download
E-books
E-book. Excel Applications. 10 Steps for Database Developers
The e-book shows how to create database client applications using Excel
Version: 1.0 | 03/20/2017 | 2.3MB
Download
E-book. Excel Applications. 10 Steps for VBA Developers
The e-book shows how to create database client applications with VBA
Version: 1.0 | 03/20/2017 | 2.4MB
Download
E-book. Excel Applications. 11 Steps for Advanced Users
The e-book shows how to create multi-user Excel applications with no SQL or VBA use
Version: 1.0 | 03/20/2017 | 2.7MB
Download