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:

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

Table of Contents

• Introduction
• Connect to DB Wizard
• Change Connection Strings Wizard
• Query Change using Ribbon
• Stored Procedure Parameters Change using Ribbon
• Database Objects Translation
• Drill-Down
• Data Filtering and Custom Table Views
• Conclusion
• See Also
• Download

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.

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.

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.

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.

The wizard makes the changes and reports about results.

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.

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.

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.

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:

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:

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:

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:

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

See the example of the 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.

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:

Alternatively, using the Context menu:

Just one click, and a new table is ready!

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

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

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