Data Connection Wizard

Data Connection Wizard

Preface

The Data Connection Wizard allows connecting to databases, web data sources, and text files.

The following database platforms are supported:

  • Microsoft Azure SQL Database
  • Microsoft SQL Server including Microsoft SQL Server Express LocalDB
  • Microsoft SQL Server Compact
  • Oracle Database
  • IBM DB2
  • MySQL
  • MariaDB
  • NuoDB
  • PostgreSQL
  • SQLite

You may connect to tables, views, and stored procedures.

The following web data sources are supported:

  • Web services including OData web services.
  • Web pages.

SaveToDB supports the following web and text data formats: HTML, XML, JSON, and CSV.

In general, the connecting includes the following steps:

  1. Selecting a provider.
  2. Connecting to a database, web data source, or a text file.
  3. Selecting an object to connect.
  4. Specifying parameters and customizing a query.
  5. Inserting a data table into Microsoft Excel.

The Data Connection Wizard can be used for changing or customizing an active query.

In this case, the wizard starts on the most reasonable step.

Developers can customize the wizard.

Step 1. Selecting Provider

This step is used to choose a provider to connect to a data source.

The wizard shows all installed providers available for connection.

Different providers can have specific features. The most important notes are shown in the comment field.

Microsoft Excel x64 requires 64-bit providers and Microsoft Excel x86 requires 32-bit providers.

The wizard shows only applicable providers.

OLEDB providers are preferable as Microsoft Excel allows customizing such queries much easy than others.

ODBC drivers allow working with data using Microsoft Excel built-in features too but have a little bit difficult customization.

.NET providers allow working with data using the SaveToDB add-in only, but they do not require additional providers to be installed.

For example, Microsoft Excel has no support for Microsoft SQL Server Compact as there are no suitable OLEDB providers or ODBC drivers.

SaveToDB allows working with Microsoft SQL Server Compact as with other databases.

See specific feature descriptions on the following topics:

Step 2. Connecting to Database, Web Data Source, or Text File

This step is used to connect to a database, web data source, or a text file.

The different database servers have specific features described in separate topics.

Click the Help button or the Examples link to get the context help during the connecting.

The Next button is enabled when the connection is successful. The wizard tests the connection in the background and activates the button.

If the Next button is disabled, click the Test Connection button to test connection.

See specific feature descriptions on the following topics:

Step 3. Select Database Object

Select an object to connect and its Query List on this step.

If you enable the Query List, you can change the query object further using the ribbon Query List, without using the Data Connection Wizard.
If you do not want to change the object later, uncheck Enable Query List on the ribbon.

The SaveToDB add-in saves the necessary metadata on hidden workbook worksheets.

If add-in's worksheets are not added yet, you may add them by checking Enable SaveToDB in this workbook.
If the sheets are already added, the field is disabled.

If the Enable SaveToDB in this workbook is unchecked, the wizard works like the native Microsoft Excel wizard.

SaveToDB contains the default Query List query that selects all tables, views, and stored procedures:

Developers can create different Query Lists to select objects by business areas.

See Configuring Query List.

For example, the following list is defined through the xls40.viewQueryList view and contains budget related objects:

Also, developers can add SQL codes, HTTP and text file queries to the Query List.

See Configuring Query List.

Step 4. Specifying parameters and customizing a query

The Query Parameters dialog box starts if the query (stored procedure, SQL code, HTTP, or text) has parameters:

This is a very useful feature absent in Microsoft Excel.

Parameter values can be configured.

See about customizing table and view queries below.

Step 5. Inserting Query

If the wizard has been started from an existing table, the wizard inserts the new query instead of the existing table.
It is useful to reconnect the existing query completely including restoring original column order.

Otherwise, the wizard asks an address to insert a table.

Insert a table at least at the second row to use the auto-filter control row.
Also, the first empty column makes the table tidier.

Take a look at the screenshot:

  • The Query List allows changing the query objects.
  • The ribbon parameters allow changing query parameters.
  • The table is completely formatted as the example database contains saved format of the xls31.uspBudgetForm stored procedure.

Translating Database Objects

Database developers can configure data translation in databases to translate database objects, object fields and parameters within Microsoft Excel.

Here is the wizard page as shown above but with the translation:

The Query Parameters dialog box also shows translated parameter names:

Customizing Tables and Views

The Query Parameters dialog box used for stored procedures is shown above.

The Query Parameters dialog box used for tables and views is shown below:

Check the fields to select in the S (Select) field.

Check the fields to use in the WHERE clause in the W (Where) field.

Take a look at the screenshot. The selected WHERE fields are placed on the ribbon and can be changed easily: