Excel-SQL Server Import-Export using Excel Add-Ins

Introduction

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

The add-in has powerful import-export features for Microsoft Excel 2007-2016 and Microsoft SQL Server 2000-2016:

• Importing SQL Server data to Excel using the Data Connection Wizard.
• Exporting Excel data to SQL Server using the Publish Wizard.
• Using Excel as a database table editor.
• Updating SQL Server data via handling Excel events using stored procedures.
• Updating SQL Server data using custom INSERT, UPDATE and DELETE stored procedures.

The first three features do not require any configuration and are ready to use right after install.

For example, you can connect to a database table, edit the data, and save the changes to the database immediately, without any coding.

For the last two features, you should develop configuration views and stored procedures.

However, only the server-side development is used. No coding in Microsoft Excel is required.

It is important to say that SaveToDB extends the behaviour of a native Excel QueryTable object.
So, users can make what they do early and use new features to update database data.

The goal of the SaveToDB Excel Add-In is to empower database developers to deliver feature-rich database applications with Microsoft Excel as a client using only database development skills.

Table of Contents

• Introduction
• SaveToDB Import-Export Wizards
• Importing SQL Server Data to Microsoft Excel
• Exporting Excel Data to SQL Server using Publish Wizard
• Updating SQL Server Data Using Default Features
• Updating SQL Server Data Using Stored Procedures
• Updating SQL Server Data Using Event Handlers
• Conclusion
• See Also
• Download

SaveToDB Import-Export Wizards

Importing SQL Server Data to Microsoft Excel

SaveToDB Add-In contains a powerful Data Connection Wizard.

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

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

Exporting Excel Data to SQL Server using Publish Wizard

The SaveToDB add-in contains a powerful Publish Wizard.

Using the Wizard you can:

1. Easily design a new database table based on an Excel data table.
2. Create a new database table and export the source table data.
3. Create a new Excel data table connected to the created database table.

Also, the wizard inserts formula columns from the source table and applies the source table formats.

After publishing, you can edit data in Microsoft Excel and save data changes back to a database.

This is the simplest way to implement a multi-user work with data stored in SQL Server or SQL Azure in Excel.

Example of Exporting Excel Data to SQL Server using Publish Wizard

The source Excel data table:

Step 1. Connect to a server and a database

Step 2. Design the destination database table
Step 3. Define table's schema and name

Step 4. Verify and execute the script
Step 5. Verify the script results

Step 6. Finish the wizard and get a new Excel table connected to the created database table

Now you can edit data and save data changes back to a database.

Updating SQL Server Data Using Default Features

If an Excel query table is connected to a database table, you can edit the data and save changes back to a database without any coding. Just click the Save button on the ribbon.

The SaveToDB add-in tracks data changes and generates the required INSERT, UPDATE and DELETE commands to update a database table.

This feature works with database tables and editable views only.

You should implement the SQL code to save changes of data from views or stored procedures.

Updating SQL Server Data Using Stored Procedures

You can configure stored procedures to update a database.

Particular server-side views are used for configuring.

In the example view you can see that:

• xls.uspBudget_insert is used to insert new data from an Excel table connected to the xls.uspBudget_select procedure.
• xls.uspBudget_update is used to update the changed data.
• xls.uspBudget_delete is used to delete the deleted data.

SaveToDB automatically determines procedure parameters and calls the procedures for appropriate changes.

You can implement and modify any business logic using stored procedures.

Users should just click the Save button to save changes.

Updating SQL Server Data Using Event Handlers

You can use stored procedures as Excel event handlers.

Below the example of the Role Members table. The table gets data from the dbo05.uspRoleMemberTable stored procedure.

Using a special configuration view, you can assign stored procedures to the Change, Double-Click or Context Menu events of any data table.

You can see that the dbo05.uspExcelEvent_Change_dbo05_uspRoleMemberTable stored procedure is used to handle the Change event of the dbo05.uspRoleMemberTable stored procedure.

The code of the dbo05.uspExcelEvent_Change_dbo05_uspRoleMemberTable procedure:

CREATE PROCEDURE [dbo05].[uspExcelEvent_Change_dbo05_uspRoleMemberTable]
    @User nvarchar(128)
    , @ColumnName nvarchar(128)
    , @CellValue nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON

    IF @CellValue IS NULL
        EXEC sp_droprolemember @rolename =  @ColumnName, @membername = @User
    ELSE
        EXEC sp_addrolemember @rolename =  @ColumnName, @membername = @User

END

The code is very simple. The procedure has parameters:

1. The value of the User column of the current data row.
2. The name of the changed cell column.
3. The value of the modified cell.

SaveToDB automatically determines procedure parameters and calls the procedure with proper values.

You can use in procedures any data from the base data table and the predefined values:

• @SheetName
• @DatabaseName
• @TableName
• @ColumnName
• @EventName
• @CellValue

This is a very powerful feature.
The example implements the Role Members editor using Microsoft Excel with a dozen of SQL lines.

Conclusion

You can easily export Excel data to a new database table using the Publish Wizard of the SaveToDB add-in.

You can easily use Microsoft Excel as a database table editor.

You can easily transform Microsoft Excel to a feature-rich database client using server-side views and stored procedures. 

Download