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.

Alternatives to the built-in Excel provider#

Here are the available alternative drivers:

  • ODBC (free)

  • CData (commercial)

ODBC#

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

CData#

Follow these steps to install the Windows Cdata ADO.Net driver for Microsoft Excel:

  1. Go to the CData website.

  2. In the ADO.NET Provider Download section, select Download Trial or Download Full, depending on your use-case.

  3. In the list CData Software - Downloads that displays, register or sign-in.

  4. Download the ADO.Net driver and install the driver.

See also#