Connecting to Data Source

Connecting to Data Source

The SaveToDB add-in for Microsoft Excel allows connecting to database table, views, and stored procedures.
Also, the add-in supports connecting to web data sources and text files including HTML, XML, JSON, CSV, and plain texts.

You may use the unified Data Connection Wizard that works with ODBC drivers, OLEDB and .NET providers.

Moreover, the add-in includes a built-in set of providers that allows connecting to databases by default and does not require additional driver installations.

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

Microsoft OLE DB Provider for SQL Server is preinstalled 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:

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

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:

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

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.

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

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.

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.

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

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

SaveToDB allows creating SQLite databases.

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

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:

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

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:

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:

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

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:

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

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

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:

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

Connecting to NuoDB

Preface

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

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:

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

Connecting to PostgreSQL

Preface

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

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:

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

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 to Web Data Source

This step differs from the same step for database connections.

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.

Specifying Parameters

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

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 to Text Files

This step differs from the same step for database connections.

You may select a file and specify a code page.

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.