Creating an Excel OleDB DataSource

Use the Projects Tree or the Project Configuration dialogue to Create or Edit a new Excel OleDB DataSource.

Excel OleDB DataSource can also be configured by dragging and dropping the target file directly onto the Projects Tree. The Project, DataSource and DataView will be automatically configured to point at the first sheet of the workbook, with all columns read only.

Configuring the Excel OleDB DataSource

An Excel OleDB DataSource only requires the workbook full path, with extension.

You can then configure multiple DataViews to look at different sheets of the workbook

Configuring the Excel OleDB DataView(s)

To create a new DataView, from the DataSource Configuration dialogue click the New DataView button. Refer to the section Configuring a DataView for more ways to create and edit DataViews.

An Excel OleDB DataView can be configured to look at an entire sheet or at a custom range that can be joined from multiple sheets.

When giving a sheet name, the OleDB DataView will try to determine the relevant area from the entire sheet, but you can narrow the scope by specifying your own range in the Custom Query area. Custom query to perform operations on the data from several sheets, see the OleDB Walk through for some query examples.

Note about Mixed data types

Excel workbook often contain mixed data types in a single column, like numbers, text annotations and dates all together. This is considered an evil practice but the OleDB engine can accommodate and read the values as string (but it is very bad and you should rather ask yourself how to clean these columns).

If you do not activate this option but do have mixed column, the column type will be determined by the first value(s) and all following non-compliant values will be considered NULL.