OleDB Connection
Use the Sources Manager Tree or the DataNode 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 Sources Manager Tree. The DataNode, DataSource and DataView will be automatically configured to point at the first sheet of the workbook, with all columns read only.
|
|
OleDB connection can present compatibility issues. Check the documentation on Microsoft Excel connections to ensure they do not apply to you or any of your users before setting up such a connection. |
|
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
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.
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.
