Microsoft Excel

Microsoft Excel#

The built-in Excel provider handles the following file types:

  1. Excel Workbook (.xlsx)

  2. Excel Workbook 97-2003 (.xls)

  3. Excel Workbook 5.0/95 (.xls)

  4. Excel Binary Workbook (.xlsb)

The following file formats and versions are supported:

File Type

Container Format

File Format

Excel Versions

.xlsx

ZIP, CFB+ZIP

OpenXml

2007 and newer

.xlsb

ZIP, CFB

OpenXml

2007 and newer

.xls

CFB

BIFF8

97, 2000, XP, 2003 98, 2001, v.X, 2004 (Mac)

.xls

CFB

BIFF5

5.0, 95

.xls

BIFF4

4.0

.xls

BIFF3

3.0

.xls

BIFF2

2.0, 2.2

Excel provider configuration#

Settings divide into two groups: connection and object-level settings. All database objects inherit settings from the connection. Relevant settings can be adjusted on the object level. If a setting is not overriden on the object level, changing it on the connection level propagates to every object that does not override the setting. Changing the setting on the object level stops the inheritance.

Basic reader configuration#

Setting name

Allowed values

Description

First row contains column names

True*, False

Indicates that the first row contains column names. The default is True. The “Skip first # rows” setting value (see Advanced Settings below) is applied first, so the First Row may not be the actual first row in a file.

Culture

Any culture from the list

Indicates the culture to be used when converting string values to desired data types.

Empty strings should be returned as NULL

True*, False

Indicates that the empty string is converted to a Null value.

Advanced reader configuration#

Setting name

Allowed values

Description

Scan rows limit

1 - 1000

On larger workbooks, the number of rows sampled to infer each column’s data type. A smaller value imports faster; a larger value inspects more rows and produces more accurate types. Smaller workbooks are read in full regardless of this value.

Treat all columns as text

True, False*

Skips data type inference and reads every column as text (nvarchar). Useful when columns mix different kinds of values and you would rather handle the conversion in SQL.

Detect data region

True, False*

Confines a sheet to its contiguous data region instead of its whole used range. When on, rows stop at the first fully blank row and — when the first row is used as column names — the columns are bounded to the leading run of non-empty header cells, so a trailing legend or footer below a blank separator is neither read nor inferred. Off by default (the whole used range is read). It applies to the imported virtual table; an OPENROWSET (BULK ...) read inherits the row stop (its columns come from the WITH clause).

Password-protected workbooks#

Workbooks protected with an open (read) password are supported. The password is part of the connection’s provider configuration and is stored encrypted. It is set in SQL when creating the connection, by including a password entry in the @provstr provider-configuration JSON of sp_addlinkedserver:

EXEC sp_addlinkedserver
     @server     = N'SalesWorkbooks',
     @srvproduct = N'Excel',
     @datasrc    = N'C:\data\sales',
     @provstr    = N'{"password": "<workbook password>", "useFirstRowHeadersAsColumnNames": "true"}';

The key must be the camelCase password - the lookup is case-sensitive. The same password is used for every read of that connection, including OPENROWSET (BULK ...).

Note

sp_updatelinkedserver replaces the entire provider configuration of an existing connection, so when you use it to add or change the password, include every provider-configuration entry you want to keep, not just password.

Data type inference#

When a sheet is imported, Querona infers a SQL data type for each column from its cell values and Excel cell number formats. A column that mixes incompatible kinds of values - for example numbers and text - is read as text so that no value is silently lost.

Cell content

Inferred type

Text, or a mix of incompatible value kinds

nvarchar(n)

Whole numbers

bigint

Numbers with a fractional part

float

Numbers with a fixed-decimal or currency cell format

decimal(p, s) (scale taken from the cell’s number format)

TRUE / FALSE

bit

Dates and date-times

datetime2

Elapsed-time (duration) cells

time

Text columns are sized to fit the data. Smaller workbooks are scanned in full, so the nvarchar length matches the longest value exactly; larger workbooks are sampled (see Scan rows limit), so the length is given some headroom and very wide columns are read as nvarchar(max). If a value turns out to be longer than the inferred length, the query fails with a clear error instead of silently truncating the value - increase the scan rows limit, or widen the column type.

You can override an inferred type when importing a sheet, or set Treat all columns as text to read every column as text.

Note

Hidden and very hidden worksheets are skipped - they are not listed as tables.

Reading with OPENROWSET (BULK)#

Besides importing a worksheet as a virtual table, you can read a workbook ad hoc with OPENROWSET (BULK ...): declare the columns inline with WITH (...), select a worksheet and range, bind columns by header name with HEADER_ROW, and read several workbooks with a glob. See OPENROWSET for the full Excel walkthrough.

Alternatives to the built-in Excel provider#

Here are the available alternative drivers:

  • a standard ODBC driver (free)

  • a commercial third-party driver (for example CData), as a last resort

ODBC#

To install the ODBC driver, please follow the instructions described in ODBC.

Commercial driver#

Microsoft Excel can also be read through a commercial third-party ADO.Net driver (for example CData). These drivers are not bundled with Querona and are licensed separately — see ADO.Net and third-party drivers.

See also#