SaveToDB 7 Wizards

SaveToDB 7 Wizards

SaveToDB Wizards

SaveToDB Wizards
Data Connection Wizard
The Data Connection Wizard allows connecting to database objects (table, views, and stored procedures), to web data sources (web pages and web services including OData), or to text files.
After the connection, the Query List can be used to change the active database or OData object. So one sheet can be used to work with multiple database or OData objects.
Pivot Data Connection Wizard
The Pivot Data Connection Wizard allows creating pivot tables connected to database objects (table, views, and stored procedures), to web data sources (web pages and web services including OData), or to text files.
Publish Wizard
The Publish Wizard allows creating a database table, exporting the Excel table data, and inserting a new Excel table connected to the created database table. After publishing data the regular functionality reload-edit-save can be used.
Other users can connect to the published table too. This is the fastest way to get the real multi-user work with data in Microsoft Excel.
After the publishing, the Table Format Wizard can be used to save table formats and formulas in a database to use by other users.
The Publish Wizard works with Excel tables early named "Lists". To create an Excel table, select a region and click the Insert, Table menu item.
Data Merge Wizard
The Data Merge Wizard allows merging data loaded from any data source into a database table.
New rows are inserted, and existing rows are updated during the merging. Only common Excel and database table columns are used.
Excel formula columns can be used to calculate data to save to a database.
Change Connection Wizard
The Change Connection Wizard allows changing connection data (server, database, login, and password) for a group of tables.
SaveToDB Framework Installer
The SaveToDB Framework Installer allows developers to install, check, or uninstall SaveToDB Framework on all supported platforms.
SaveToDB Framework allows customizing SaveToDB features on the server-side.
Database administrator permission is required to perform wizard operations.
Configuration Workbook Generator
The Configuration Workbook Generator allows developers to generate a workbook used to customize SaveToDB features on the server-side.
The generated workbook contains SaveToDB Framework configuration tables. Just edit and save the configuration data.
In facts, Excel application customization can be made in Microsoft Excel. It is useful as the results can be viewed in Excel immediately.
Table Format Wizard
The Table Format Wizard allows saving, loading, and clearing Excel table formats of database objects in a database.
When the format is saved in a database, other users can load the same format including regular formats, conditional formatting, formulas, etc.
Window Wizard
The Window Wizard allows adding and arranging windows.
Form Wizard
The Form Wizard allows creating cursors and form fields.
You may easily create edit forms and master-detail interfaces.
Pivot Wizard
The Pivot Wizard contains useful commands for working with pivot tables.

See also complete descriptions:

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.

SaveToDB Data Connection Wizard - Selecting a provider

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.

SaveToDB Data Connection Wizard - Connecting to a database

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 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:

SaveToDB Data Connection Wizard - Selecting an object to connect

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:

SaveToDB Data Connection Wizard - Selecting an object to connect filtered by the query list

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:

SaveToDB Data Connection Wizard - Example of the Query Parameters form for stored procedures

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.
SaveToDB Data Connection Wizard - Connected budget form

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:

SaveToDB Data Connection Wizard - Example of the Configure Query wizard

The Query Parameters dialog box also shows translated parameter names:

SaveToDB Data Connection Wizard - Example of the translated Query Parameters form

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:

SaveToDB Data Connection Wizard - Example of the Query Parameters form for views

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:

SaveToDB Data Connection Wizard - Connected Payments view

Pivot Data Connection Wizard

Preface

The Pivot Data Connection Wizard allows creating Excel pivot tables connected to databases, web data sources, and text files.

The connection process is the same as for Data Connection Wizard.

If an underlying database object allows saving changes, you may insert a connected Excel table and insert a pivot table based on the connected table.

It this case you may use pivot table as an editor for the underlying table.

See details in Working with Pivot Tables.

Publish Wizard

Preface

The Publish Wizard allows:

  • Designing and creating a database table based on Microsoft Excel table.
  • Exporting the Excel table data to the created database table.
  • Inserting a new Excel table connected to the created database table.
  • Applying base table formatting and formulas to the new Excel table.

After publishing data the regular SaveToDB functionality reload-edit-save can be used.

Other users can connect to the published table too.

This is the fastest way to get the real multi-user work with data in Microsoft Excel.

After the publishing, the Table Format Wizard can be used to save the table format and formulas in a database for distributing to other users.

The created table can be republished, for example, for changing a table structure.

However, the publishing is not a substitute for the regular mode of saving data changes that requires one click only.

See also the Data Merge Wizard that allows merging Excel table data to existing database tables.

The Publish Wizard allows publishing data on all supported database platforms:

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

Excel Table Requirements

The Publish Wizard works with Excel tables early named "Lists".

To run the wizard, any cell of the table to publish must be selected.

If the wizard is disabled, create an Excel table.

To create an Excel table, select a table region and click the Insert, Table menu item.

Inserting Excel table

Wizard Steps

The wizard includes the following steps:

  1. Selecting a provider.
  2. Connecting to a database.
  3. Designing a destination database table.
  4. Defining a destination table schema and name.
  5. Verifying and executing the publish script.
  6. Verifying script results.
  7. Creating a new Excel table connected to the created database table.
Publish Excel data to database wizard - Wizard steps

Example Comments

The wizard applies base table formatting and formulas to a new table.

The following Excel table is used in this article to show desired features:

Publish Excel data to database wizard - Source Table

The table includes columns of all Excel basic types: integer, double, datetime, string, and boolean.

The table also contains the formula column that gets values of the Float column.

Microsoft SQL Server is used in the example.

Important!

The result Excel table contains the additional first column of row numbers (_RowNum in English version) used for saving data changes.

SaveToDB inserts the table at one column left, if possible, to preserve existing column positions.

Accordingly, insert at least one column left before the publishing. Otherwise, the table columns will be shifted.

Step 1. Selecting Provider

This step is used to select a provider to connect to a database.

The wizard shows all installed providers available for connection.

Publish Excel data to database wizard - Selecting provider

See specific provider descriptions on the following topics:

Step 2. Connecting to Database

This step is used to connect to a database.

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.

Publish Excel data to database wizard - Connecting to SQL Server database

See specific database server descriptions on the following topics:

Step 3. Designing Destination Table

This step is used to design the destination table.

Publish Excel data to database wizard - Designing destination SQL Server database table

Using the designer you can:

  • Add and delete destination table columns.
  • Change the column order.
  • Change the destination table column names.
  • Change the destination table column types.
  • Change primary key columns.

The Database Data Type column contains the types applicable to the source column types.
For example, if the source column contains integers only then only integer compatible SQL types are available.

The wizard selects the most proper types, but you should check and change the types if necessary.

The number in brackets after the database data type is the data length for variable length types and the data type storage size for fixed length data types.

4-byte real and float data types are not used because Microsoft Excel imports wrong values for these types.

The table must have at least one primary key (PK) column. Primary key columns must have no empty values.

If an integer column is selected as a single primary key column, then the field is created with the IDENTITY property.

If a GUID column is selected as a single primary key column, then the field is created with a GUID generation property.

The properties for IDENTITY and GUID columns are supported on all database platforms.

Step 4. Defining Table Schema and Name

This step is used to define the schema and name of the destination table.

Publish Excel data to database wizard - Defining schema and name of destination SQL Server database table

The Table Schema field contains schemas with user's ALTER permission that allows creating new tables.
A new schema can be created if a user has CREATE SCHEMA permission.

The Table Name field contains existing tables of the selected schema.

Important! If the destination table exists, then it will be dropped before the creation of a new table.

Publish Excel data to database wizard - Drop table confirmation dialog box

Step 5. Verifying and Executing Script

This step is used for verifying and executing the publish script.

Publish Excel data to database wizard - Verifying and executing script

You may edit the script before the execution.

However, know what you do because the wizard does not control your changes.

Useful tips:

  • You may change the data types if necessary.
  • You may copy the script and send it to your database administrator to create the table and import the data.

Click the Execute button to execute the script.

If the script execution has been interrupted by timeout, you may increase the command timeout in the Options dialog box.

Step 6. Verifying Script Results

The script execution results are shown in the window.

Publish Excel data to database wizard - Verifying script results

Click the Finish button to create an Excel table connected to the created database table.

Step 7. Creating New Excel Table Connected to Created Database Table

When the Finish button is clicked, the following actions are performed:

  • Creating a new worksheet.
  • Inserting a new table connected to the created database table.
  • Copying formula columns to the new table from the source table.
  • Applying formats from the source table.
Publish Excel data to database wizard - New table

Pay attention that column A is hidden. The column contains row numbers used for saving data changes. Source table columns have the same column numbers; so, you can easily transfer used source formulas.

The result message is displayed at the end:

Publish Excel data to database wizard - Final message

Useful Tips

You may edit new table data: change cells, add and delete rows. Then you may save the changes back to a database using the Save button.

Other users can connect to the table using their workbooks and can save changes also.

Use the Table Format Wizard to save the table format and formulas in a database, and other users get the same table as you including formats and formulas.

Data Merge Wizard

Preface

The Data Merge Wizard allows merging Excel table data into database tables.

You may merge Excel data loaded from web pages, text files, or other sources.

The wizard generates an SQL code to insert new data rows and to update existing data rows.

Database table primary keys are used to verify that a row exists.

In the most cases, such primary keys are natural like emails or mobile phone numbers.
For example, social network contacts can be identified by a row source like LinkedIn, Facebook or GooglePlus, and by an email.

Only common columns of an Excel table and a database table are inserted and updated.

Excel formula columns can be used to make database table columns.
For example, FirstName and LastName database columns can be calculated from the Name source column.

Database tables can contain the MergeData or merge_data datetime fields that are updated by the add-in with the script generation time.
This field allows verifying that a row exists in the latest Excel data table.

You may configure merging data configuring saving changes. In this case, users may use the Save button instead of the wizard.
Also, the custom SQL codes or stored procedures can be used to merge data.

The Data Merge Wizard allows merging Excel data into databases on all supported database platforms:

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

Excel Table Requirements

The Data Merge Wizard works with Excel tables early named "Lists".

To run the wizard, any cell of the table to merge data must be selected.

If the wizard is disabled, create an Excel table.

To create an Excel table, select a table region and click the Insert, Table menu item.

Inserting Excel table

Step 1. Selecting Provider

This step is used to select a provider to connect to a database.

The wizard shows all installed providers available for connection.

Excel Data Merge Wizard - Selecting provider

See specific provider descriptions on the following topics:

Step 2. Connecting to Database

This step is used to connect to a database.

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.

Excel Data Merge Wizard - Selecting SQL Server database

See specific database server descriptions on the following topics:

Step 3. Selecting Target Table

This step is used to select a target database table to merge Excel data.

Excel Data Merge Wizard - Selecting SQL Server database table

Step 4. Verifying and Executing Script

This step is used for verifying and executing the publish script.

Excel Data Merge Wizard - Verifying and executing script

You may edit the script before the execution.

Click the Execute button to execute the script.

If the script execution has been interrupted by timeout, you may increase the command timeout in the Options dialog box.

The following Excel table is used in the example above:

Excel Data Merge Wizard - Example Excel data to merge to a database

As mentioned above, if an Excel table does not contain primary key columns then insert commands are generated only.
However, unique records are inserted only, as the records are verified using the join on all fields. See the code above.

If the Excel table contains primary keys (natural keys in most cases), the records are verified using primary keys.

Change Connection Wizard

Preface

The Change Connection Wizard allows changing a group of connection strings at once.

This is useful when you need to move a database to a new server or to change a development database to a production one.

This wizard also changes SaveToDB stored configuration data related to changed tables.

Step 1. Selecting Provider

This step is used to select a provider to connect to a new connection database.

The wizard shows all installed providers available for connection.

SaveToDB Change Connection Wizard - Example of selecting a provider

See specific provider descriptions on the following topics:

Step 2. Connecting to Database

This step is used to connect to a new connection database.

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.

SaveToDB Change Connection Wizard - Example of connecting to a database

Step 3. Changing Connections

Select the tables to change connections to the new connection and click the Finish button.

SaveToDB Change Connection Wizard - Example of selecting connections

The result message shows a number of successfully updated tables:

SaveToDB Change Connection Wizard - Example of the final message

SaveToDB Framework Installer

Preface

SaveToDB Framework allows configuring all server-side SaveToDB features.

The SaveToDB Framework Installer allows developers to install, check, or to remove SaveToDB Framework on all supported database platforms:

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

Database administrator permission is required to install or uninstall the framework.

The wizard has the following steps:

  1. Selecting a provider.
  2. Connecting to a database.
  3. Selecting a framework language and an install operation.
  4. Verifying and executing a script.
  5. Verifying execution results.

Step 1. Selecting Provider

This step is used to select a provider to connect to a database.

The wizard shows all installed providers available for connection.

SaveToDB Framework Installer - Selecting provider

See specific provider descriptions on the following topics:

Step 2. Connecting to Database

This step is used to connect to a database.

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.

SaveToDB Framework Installer - Selecting database

See specific database server descriptions on the following topics:

Step 3. Selecting Language and Operation

This step is used to select the operation and a language of the SaveToDB Framework.

The table shows the current status of SaveToDB Framework objects.

Note that you must have enough permission to view objects in the framework schemas.

SaveToDB Framework Installer - Installing framework

Step 4. Verifying and Executing Script

The script can be modified before execution.

Click the Execute button to execute the script.

SaveToDB Framework Installer - Installation script

SaveToDB shows the confirmation dialog box before the execution:

SaveToDB Framework Installer - Confirming installation

Step 5. Verifying Results

The screen shows operation results.

SaveToDB Framework Installer - Installation results

After execution, you may go back to check the installed database objects or to install the framework in another database.

Step 3b. Selecting Language and Operation

This example shows SaveToDB Framework objects successfully installed in a database.

If the objects are found, the wizard activates the remove operation.

SaveToDB Framework Installer - Removing framework

Step 4b. Verifying and Executing Script

The script can be modified before execution.

Click the Execute button to execute the script.

SaveToDB Framework Installer - Removing script

SaveToDB shows the confirmation dialog box before the execution:

SaveToDB Framework Installer - Confirming removing

Step 5b. Verifying Results

The screen shows operation results.

SaveToDB Framework Installer - Removing results

Configuration Workbook Generator

Preface

SaveToDB features can be configured on the server-side.

SaveToDB Framework can be used to add quickly a configuration infrastructure to a database.

This wizard generates a workbook that allows editing SaveToDB Framework configuration tables in Microsoft Excel.

SaveToDB Framework must be installed before the generation. Use the SaveToDB Framework Installer to install the framework.

The wizard has the following steps:

  1. Selecting a provider.
  2. Connecting to a database.
  3. Verifying installed SaveToDB objects.
  4. Generating a configuration workbook.

Step 1. Selecting Provider

This step is used to select a provider to connect to a database.

The wizard shows all installed providers available for connection.

Configuration Workbook Generator - Selecting provider

See specific provider descriptions on the following topics:

Step 2. Connecting to Database

This step is used to connect to a database.

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.

Configuration Workbook Generator - Selecting database

See specific database server descriptions on the following topics:

Step 3. Verifying Installed Framework Objects

To generate a configuration workbook, SaveToDB Framework must be installed and available with the current connection credentials.

Use the SaveToDB Framework Installer to install the framework.

If the framework is installed but the framework objects are not found, ask your database administrator to add your login to the SaveToDB developer role, or connect to a database using other credentials.

Configuration Workbook Generator - Verifying objects and selecting a language

Click the Finish button to generate the configuration workbook.

Step 4. Generating Configuration Workbook

The wizard generates a configuration workbook.

Configuration tables are placed on separate worksheets.

To configure SaveToDB features, edit the desired table and save the data.

The workbook contains an application development concept that is a good start point for application development.

Configuration Workbook Generator - Generated configuration workbook

Table Format Wizard

Preface

Users can change database objects of Excel data tables using the Query List and the Data Connection Wizard.

The SaveToDB add-in saves database object formatting before changing and applies the saved or default formatting to a new object.
So users do not lose their formats and work.

See topics about table format elements and default table formatting below.

Table formats can be saved in a database and applied back from a database if this feature is configured.
See Configuring table formats.

The Table Format Wizard allows users to perform different tasks with the table formats.

Table Format Wizard

SaveToDB Table Format Wizard
Save in Workbook
The button saves formats of the selected tables in the workbook.
Restore from Workbook
The button restores formats of the selected tables from the workbook.
Clear in Workbook
The button deletes formats of the selected tables in the workbook.
Save in Database
The button saves formats of the selected tables in databases.
The button is disabled if the user has no permission on the save format procedure.
Restore from Database
The button restores formats of the selected tables from databases.
The button is disabled if databases have no saved table formats.
Clear in Database
The button clears formats of the selected tables in databases.
The button is disabled if the user has no permission on the save format procedure.
Clear Table Format
The button clears cell formats and data validation in the selected tables.
The conditional formatting is not changed.
Apply Default Format
The button applies the default table formatting. See below.
Use the Clear Table Format button to clear existing formatting before the new one.
OK
The button closes the dialog box.
Cancel
The button closes the dialog box.

Table Format Elements

The SaveToDB add-in saves and restores the following formatting:

  • Cell formats.
  • Conditional formatting.
  • Applied filters.
  • Sort order rules.
  • Table totals.
  • Data validation.
  • Window properties.
  • Formula columns.

Default Table Formatting

The SaveToDB add-in applies the following rules for default formatting:

  • Auto-sizing columns (maximum width 500px).
  • Hiding the row number column.
  • Setting the width 18px for the A column if the table does not use this column and has the row number column.
  • Setting date and time formats for date and time columns.
  • Setting data validation for database tables and views if the data changes can be saved to a database.
  • Setting special conditional formatting for non-NULL columns.

Window Wizard

Preface

The Window Wizard allows adding related windows.

You may easily implement master-detail interfaces in Excel 2007 and Excel 2010.

This feature is not applicable for Excel 2013. 

See Working with Windows.

Window Wizard Menu Items

Arrange Windows
The button arranges active workbook windows.
A smart, non-Excel, algorithm is used.
The auto-arrange workbook windows option can be turned on in the Options dialog box.
Add Window
The menu allows adding new windows.
The active and new windows have the equal sizes of a half size of the active window.
You may add as many windows as you need.

Form Wizard

Preface

The SaveToDB add-in allows:

  • Highlighting active table rows.
  • Updating named cells with active table row values.
  • Updating active table row values with named cell values.
  • Implementing master-detail relations.

This is an example of the described features:

Example of using cursors and fields

The Form Wizard helps to create such forms.

See complete description in Working with Cursor and Fields.

Form Wizard Menu Items

Add Cursor
Adds a cursor to the active table.
Remove Cursor
Removes the cursor of the active table.
Add Form Fields
Add named cells to show and edit active table row values.
Remove Form Fields
Removes named cell names added to show and edit active table row values.
Clean Named Cells
Cleans named cells that have no correct ranges.

Connecting to Microsoft SQL Server

Preface

SaveToDB Add-In for Microsoft Excel allows connecting to tables, views, and stored procedures of the all SQL Server versions:

  • Microsoft Azure SQL Database
  • Microsoft SQL Server
  • Microsoft SQL Server Express LocalDB

This topic describes Microsoft SQL Server specific features.

Selecting Provider

Connecting Excel to Microsoft SQL Server database - Selecting provider

Microsoft OLE DB Provider for SQL Server is pre-installed with Microsoft Windows and available by default.

This is the best provider to connect to Microsoft SQL Server and Microsoft Azure SQL Database.

To connect to file databases, for example using Microsoft SQL Server Express LocalDB, SQL Server Native Client 11.0 providers installed are required.

Connecting to Database

The server name format for Microsoft SQL Server:

<Server name or IP-address>[,<Port>][\<Instance name>]

The database field is available when the server and the logon credentials are specified.

The database list is populated with the databases available for a connection.

This is an example of a connection to the AzureDemo database in Microsoft Azure SQL Database:

Connecting Excel to Microsoft SQL Server database

This is an example of the connection to the local server on port 1433:

Connecting Excel to Microsoft SQL Server database

The 'localhost' should be used for the local server with the port specification as Microsoft Excel does not support the '.' notation with the port specified.

This is an example of the connection to the SQLEXPRESS named instance:

Connecting Excel to Microsoft SQL Server database

This is an example of the connection to a file database using Microsoft SQL Server Express LocalDB:

Connecting Excel to SQL Server Express LocalDB file database

You may use the Browse button to select a file.

The SQL Server Native Client 11.0 provider is required to connect to a file database.

If the file database is already attached, it is available in the database list.

Connecting Excel to SQL Server Express LocalDB attached file database

However, the better way is to use the file connection for a file database as the database can be detached anytime.
In this case, the file connection works while the database connection does not.

Connecting to Microsoft SQL Server Compact

Preface

SaveToDB Add-In for Microsoft Excel allows connecting to Microsoft SQL Server Compact databases.

The SaveToDB add-in supports working with Microsoft SQL Server Compact itself as there are no OLEDB providers or ODBC drivers supported by Microsoft Excel.

Selecting Provider

Connecting Excel to SQL Server Compact database - Selecting provider

Microsoft SQL Compact Data Provider supports working with Microsoft SQL Server Compact 3.5,
and Microsoft SQL Compact Data Provider 4.0 supports working with Microsoft SQL Server Compact 4.0.

Microsoft SQL Compact Data Provider 4.0 is embedded into SaveToDB and works by default.

You may download Microsoft SQL Server Compact 4.0 at http://www.microsoft.com/en-us/download/details.aspx?id=30709

and Microsoft SQL Server Compact 3.5 SP2 at http://www.microsoft.com/en-us/download/details.aspx?id=5783.

Connecting to Microsoft SQL Server Compact Database File

An existing database file must be specified to connect to Microsoft SQL Server Compact.

A password must be specified if the database file is encrypted.

If the database file located in the active workbook folder, then SaveToDB uses a file name only.
This feature allows moving a workbook and its database files to another folder or computer.

The Microsoft SQL Server Compact database can be created during connection.

This feature is useful for the Publish Wizard and the SaveToDB Framework Installer.

Connecting Excel to SQL Server Compact database

Creating Microsoft SQL Server Compact Database

SaveToDB allows creating Microsoft SQL Server Compact databases including versions 3.5 and 4.0.

The database version is defined by the Microsoft SQL Server Compact provider selected at the first step.

Creating SQL Server Compact database

Check Overwrite existing database file to recreate a database file.

You may specify a password to encrypt the database file.

Encryption modes:

  1. Platform Default: The algorithms used in this mode are AES128_SHA256, where AES128 is the encryption algorithm with 128-bit key and SHA256 is the hash algorithm with 256-bit key. This is the default encryption mode option on all SQL Server Compact 4.0 supported platforms.

  2. Engine Default: In this mode, the database is encrypted using AES256_SHA512, where AES256 is the encryption algorithm and SHA512 is the secure hash algorithm. The default key length is used to maintain backward compatibility with SQL Server Compact 3.5.

Connecting to SQLite

Preface

Microsoft Excel supports working with SQLite via ODBC drivers.

The SaveToDB add-in also supports working with SQLite via built-in .NET SQLite Data Provider.
So you may work with SQLite without installing additional software.

Selecting Provider

Connecting Excel to SQLite database - Selecting provider

SQLite Data Provider is embedded into SaveToDB and works by default.

You may download and install SQLite ODBC Driver at http://www.ch-werner.de/sqliteodbc/.

Connecting to SQLite Database File

An existing database file must be specified to connect to SQLite.

A free version of SQLite with a public domain license does not support encrypting.

If the database file located in the active workbook folder, then SaveToDB uses a file name only.
This feature allows moving a workbook and its database files to another folder or computer.

The SQLite database can be created during connection.

This feature is useful for the Publish Wizard and the SaveToDB Framework Installer.

Creating SQLite database

Creating SQLite Database

SaveToDB allows creating SQLite databases.

Creating SQLite database

A free version of SQLite with a public domain license does not support encrypting.

Connecting to Oracle Database

Preface

SaveToDB Add-In for Microsoft Excel allows connecting to Oracle Database tables, views, and stored procedures.

This topic describes Oracle Database specific features.

Selecting Provider

Connecting Excel to Oracle Database - Selecting provider

The Oracle Database Client components are required to connect Oracle Database.
See http://www.oracle.com/technetwork/database/enterprise-edition/downloads/

Oracle OLE DB Provider is the best choice as it supports all Oracle Database features.

Connecting to Database

The Oracle Database server name format:

[<Server name or IP-address>[:<Port>]/]<Service name>

The service name only can be used if the service is configured in the tnsnames.ora file on the local machine.

Microsoft Excel does not support connections as SYSDBA or SYSOPER, but you may logon as SYSTEM.

This is an example of the connection to the service named Orcl:

Connecting Excel to Oracle Database

This is an example of the connection to the service named Orcl at the server named Oracle on port 1521:

Connecting Excel to Oracle Database

Multiple versions of 64-bit Oracle Database ODBC drivers have an error.

You may get the "Arithmetic operation resulted in an overflow" message during connection:

Oracle ODBC connection error - Arithmetic operation resulted in an overflow

The solution is to update the 64-bit Oracle Database ODBC driver to the latest version.
Alternatively, you may use Oracle OLE DB Provider that has no such error.

The Oracle Database 10g providers have an error. They do not work with the program started from the path that contains the brackets.

So 32-bit Microsoft Office on 64-bit Windows installed in Program Files (x86) cannot connect to Oracle Database.
The following ORA-12154 error is appearing:

Oracle connection error ORA-12154

See Fixing Oracle Database 10g Connection Error ORA-12154 below.

Fixing Oracle Database 10g Connection Error ORA-12154

The Oracle Database 10g providers have an error. They do not work with the program started from the path that contains the brackets.

So, 32-bit Microsoft Office on 64-bit Windows installed in Program Files (x86) cannot connect to Oracle Database.

The best way is to update the Oracle client components as Oracle Database 11g providers do not have this issue.

Otherwise, the best way to fix the error is to create a symbolic link to the Microsoft Office installation directory and to create a new shortcut for Microsoft Excel.

To create the symbolic link, execute the following command from the Windows command line as Administrator:

mklink /D "C:\Program Files x86" "C:\Program Files (x86)"

Then create the shortcut for Microsoft Excel. For example, the path for Microsoft Excel 2010 is:

"C:\Program Files x86\Microsoft Office\Office14\EXCEL.EXE"

and the path for Microsoft Excel 2007 is:

"C:\Program Files x86\Microsoft Office\Office12\EXCEL.EXE"

The disadvantage of this solution is that you should open Microsoft Excel with the new shortcut to work with Oracle Database, but not with the click on an Excel workbook.

The alternative way is to reinstall Microsoft Office to a new path without brackets.

To delete the symbolic link, execute the following command from the Windows command line as Administrator:

rd "C:\Program Files x86"

Connecting to IBM DB2

Preface

SaveToDB Add-In for Microsoft Excel allows connecting to IBM DB2 tables, views, and stored procedures.

This topic describes IBM DB2 specific features.

Selecting Provider

Connecting Excel to IBM DB2 - Selecting provider

IBM OLE DB Provider for DB2 or Microsoft OLE DB Provider for DB2 installed is required to connect IBM DB2.

See IBM Data Server Client Packages at http://www.ibm.com/software/data/db2/linux-unix-windows/download.html
and Microsoft OLEDB Provider for DB2 at http://www.microsoft.com/en-us/download/details.aspx?id=16978.

The IBM provider is the best choice as the Microsoft provider requires the Microsoft SQL Server Enterprise Edition license.

Connecting to Database

The IBM DB2 server name format:

<Server name or IP-address>[:<Port>]

This is an example of the connection to the SAMPLE database at the server named DB2:

Connecting Excel to IBM DB2 database

This is an example of the connection to the SAMPLE database at the server named DB2 on port 50000:

Connecting Excel to IBM DB2 database

Connecting to MySQL and MariaDB

Preface

SaveToDB Add-In for Microsoft Excel allows connecting to MySQL and MariaDB tables, views, and stored procedures.

MariaDB is completely compatible with MySQL. So you may use MySQL providers and drivers to connect both servers.

Selecting Provider

Connecting Excel to MySQL database - Selecting provider

Two types of providers can be used to connect MySQL:

  • MySQL ODBC Driver.
  • MySQL Data Provider for .NET.

You may download MySQL ODBC drivers at http://dev.mysql.com/downloads/connector/odbc/.

Microsoft Excel supports MySQL ODBC drivers, and use of ODBC drivers is preferable.

A Unicode version of MySQL ODBC drivers completely supports national characters; ANSI drivers can have issues with national characters.

.NET MySQL Data Provider is integrated into the SaveToDB add-in and does not require additional installation.
However, working with MySQL is possible only with the SaveToDB add-in installed as Microsoft Excel does not support .NET providers.

Connecting to Database

The MySQL server name format:

<Server name or IP-address>[;port=<Port>]

This is an example of the connection to the test database at the localhost:

Connecting Excel to MySQL database

This is an example of the connection to the test database at the localhost on port 3306:

Connecting Excel to MySQL database

Connecting to NuoDB

Preface

SaveToDB Add-In for Microsoft Excel allows connecting to NuoDB tables, views, and stored procedures.

Selecting Provider

Connecting Excel to NuoDB database - Selecting provider

NuoDB ADO.NET Driver is integrated into the SaveToDB add-in and does not require additional installation.
Working with NuoDB is possible only with the SaveToDB add-in installed as Microsoft Excel does not support .NET providers.

You may download the latest NuoDB ADO.NET Driver at http://www.nuodb.com.

ODBC connections are not supported.

Connecting to Database

The NuoDB server name format:

<Server name or IP-address>[:<Port>]

This is an example of the connection to the test database at the localhost:

Connecting Excel to NuoDB database

This is an example of the connection to the test database at the localhost on port 48004:

Connecting Excel to NuoDB database

Connecting to PostgreSQL

Preface

SaveToDB Add-In for Microsoft Excel allows connecting to PostgreSQL tables, views, and functions.

Selecting Provider

Connecting Excel to PostgreSQL - Selecting provider

Two types of providers can be used to connect PostgreSQL:

  • PostgreSQL ODBC Driver.
  • Npgsql .NET PostgreSQL Data Provider.

You may download PostgreSQL ODBC drivers at http://www.postgresql.org/ftp/odbc/versions/msi/.

Microsoft Excel supports PostgreSQL ODBC drivers but does not support connecting to functions that return ref cursors.

Npgsql .NET PostgreSQL Data Provider is integrated into the SaveToDB add-in and does not require additional installation.
However, working with PostgreSQL is possible only with the SaveToDB add-in installed as Microsoft Excel does not support .NET providers.

Connecting to Database

The PostgreSQL server name format:

<Server name or IP-address>[;port=<Port>]

This is an example of the connection to the test database at the localhost:

Connecting Excel to PostgreSQL database

This is an example of the connection to the test database at the localhost on port 5432:

Connecting Excel to PostgreSQL database

Connecting to Web Data

Preface

SaveToDB Add-In for Microsoft Excel allows connecting to web data sources.

The following web data sources are supported:

  • Web services including OData services
  • Web pages

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

SaveToDB supports the following authorization methods: Windows, Basic, Forms, OAuth 1.0, and OAuth 2.0.

SaveToDB supports the following authorization providers: Google, Facebook, LinkedIn, Twitter, Microsoft Azure Marketplace, Windows Live, Yahoo, and Yahoo API Key.

SaveToDB Enterprise allows saving data changes to OData web services.

Selecting Provider

Use Gartle Web Data Provider to connect web data.

Connecting Excel to Web - Selecting provider

Connecting to Web Data Source

This step differs from the same step for database connections.

Connecting Excel to Web - Specifying URL

The following data are used:

  • URL
  • Service URL
  • OAuth Provider
  • Scope

When the URL has been changed, SaveToDB tries to discover the required parameters in the background.
If the parameters cannot be discovered by SaveToDB or other values should be used, you may specify them manually.

Any time you may test the connection with the current connection data.

The Next button is available for successfully connected OData web services as a web service object is selected in the next step.
For other web data sources, the Finish button is used as the specified URL data is inserted in Microsoft Excel.

URL

In general, you may insert the URL from a browser.

SaveToDB allows redefining HTTP query parameters. See below.

You may also add the pages and rootPath parameters to the end of the URL. See below.

Service URL

The service URLs are defined unambiguously for OData web services only. The service URL is the service root URL that returns the service document.

The service URL for other web services and websites is a URL of any protected area.

A website can contain multiple protected areas. The root URLs of these areas can be used as service URLs.

Such areas cannot be found in all cases. So, sometimes you may need to specify the service URL manually.

Don't worry if the service URL is not defined correctly. In this case, you can be asked for authorization for multiple URLs from the website.

OAuth Provider

SaveToDB supports the following authorization methods:

  1. Windows
  2. Basic
  3. Forms
  4. OAuth 1.0
  5. OAuth 2.0

The first three methods are discovered automatically. The OAuth methods are discovered by website URLs.

You can manually set or clear the OAuth provider.

Scope

The scope is used for OAuth 2.0 providers only. SaveToDB discovers the known scopes for known OAuth providers.
However, the scope requirements are very different for various providers and websites. So, you may specify the required scope manually.

Redefining HTTP Query Parameters

Parameters of HTTP queries are detected from a URL automatically.

The parameters can be redefined in the form {<Parameter name>=<Default value>}.

For example, the URL http://finance.yahoo.com/q/hp?s=GOOG+Historical+Prices

has the "s" parameter with the "+Historical+Prices" suffix to the stock symbol.

You can configure the URL like http://finance.yahoo.com/q/hp?s={Symbol=GOOG}+Historical+Prices

In this case, the Symbol parameter is used to specify the stock symbol only.

The parameters are placed on the ribbon and allow changing the query further.

Pages and RootPath Parameters

;Pages=<number of pages>

Specifies the number of loaded pages for the specified URL.

SaveToDB tries to find the next page URL and to load the next pages.

For example, you may load option data for multiple expiration dates from Yahoo! Finance:

http://finance.yahoo.com/q/op?s={Symbol=AAPL};pages=20

;RootPath=<root path>

Specifies the root element of columns to output.

For XML and JSON, SaveToDB parsers look for the first element with the full column name ended with the specified value.

The full column name consists of all column names in the path separated by dots.

For JSON documents, you may specify multiple roots separated by comma or semicolon.

For HTML, you may specify a table number to output, not column name.
To find the right value, just try different values: ;rootPath=1, ;rootPath=2, and so on.

For example, you may load option data for calls and puts from Google Finance:

http://www.google.com/finance/option_chain?q={Symbol=AAPL}&authuser=0&output=json;RootPath=calls,puts;pages=2

Also, you may load Income Statements, Balances, CashFlows from Google Finance using the following URLs:

https://www.google.com/finance?q={Symbol=AAPL}&fstype=ii;RootPath=2
https://www.google.com/finance?q={Symbol=AAPL}&fstype=ii;RootPath=4
https://www.google.com/finance?q={Symbol=AAPL}&fstype=ii;RootPath=6
https://www.google.com/finance?q={Symbol=AAPL}&fstype=ii;RootPath=3
https://www.google.com/finance?q={Symbol=AAPL}&fstype=ii;RootPath=5
https://www.google.com/finance?q={Symbol=AAPL}&fstype=ii;RootPath=7

;CollapsedNodes=<node>[,...]

The option defines the XML nodes that include values of children nodes.

Example:

;CollapsedNodes=passages

;SkippedNodes=<node>[,...]

The option defines suffixes of XML nodes to exclude from the output.

To exclude specific columns, use complete paths with column names separated by dots like parent1.parent2.column.

Example:

;SkippedNodes=.type

;IgnoredTags=<tag>[,...]

The option defines tags to skip as separate columns. The values are included into parent columns.

The typical scenario is ignoring text highlight tags. The option is applicable only with XML.

Example:

;IgnoredTags=hlword

;NoSourceHeaders=True

The option suppresses using HTML table headers as column names.

Use this option to import financial statements from the web with static column names.

;AsIs=True

The option suppresses any special processing of the output data.

For example, the add-in converts Yahoo timestamps to datetime values. You may disable this using the AsIs option.

;RowValues=True

This option applies XML or JSON parsers that print all values in rows.

This option is useful for learning document structures.

Selecting OData Object

This step allows selecting an OData web service object to connect.

OData containers are similar to database query list views.

Using the Query List, you may select a container and then an object of the container.

Connecting Excel to OData - Selecting EntitySet or FunctionImport

Specifying Parameters

This step is used to specify parameters of OData FunctionImport objects and HTTP queries.

Connecting Excel to OData - Specifying OData query parameters

Further, you may work with web data like with other database data.

Connecting to Text Files

Preface

SaveToDB Add-In for Microsoft Excel allows loading and refreshing data from text files.

For example, a web page can be saved to a local disk and loaded into Microsoft Excel.
It is also used if a web server does not support direct connection but supports data export.

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

Connecting to text files using SaveToDB has additional benefits over Microsoft Excel connections:

  • You may connect to different data the same way.
  • You may connect to JSON and HTML files.
  • You may refresh data of any type.
  • You may add formula columns to loaded data.
  • You may specify a code page for loaded data.

You may save the loaded data into a database using the Data Merge Wizard.

Selecting Provider

Use Gartle Text Data Provider to connect to text files.

Connecting Excel to text file - Selecting provider

Connecting to Text Files

This step differs from the same step for database connections.

You may select a file and specify a code page.

Connecting Excel to text file

You may specify a relative path to a file, related to the active workbook directory.

In this case, you may freely move the workbook and the file to another place or computer.