Considerations when using Excel as a data source

Introduction

PIESCentral supports Microsoft Excel as a data source. There are certain restrictions, however, inherent in the data provider that are outside our control and must be understood.

Defining Data Tables in a Spreadsheet

Excel “workbooks” (that is to say, files) can contain one or more “worksheets.” The tabs at the bottom of the screen are used to name these worksheets. The default names are “Sheet1”, “Sheet2”, etc., but the user can rename them to something more relevant like “Pricing” or “Interchanges”.

It is helpful to think of the workbook as the “database” and the “worksheet” as the tables in that database. The first row of each worksheet should contain a representative label for each column, for example, “Part Number”.

When writing a SQL statement against an Excel worksheet, you must append a dollar sign ($) to the name, for example [Pricing$]. (It is also a good idea to always include square brackets around any column or worksheet name.)

As an alternative to the worksheet name, you can also define a “Named Range” in Excel and use that Named Range as a table reference. In this case, do not include a dollar sign after the name. One problem with Named Ranges, however, is making sure they are kept up to date to include all of the data you want to include.

Guessing Data Types

As a rule, each column in a relational database has a single data type. With Excel, however, each cell can have its own data type. This presents a problem for the Excel data provider. The Excel driver reads a certain number of rows (by default, 8) to guess the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver normally decides in favor of the majority data type, and returns null values for cells that contain data of the other type.

Changing Registry Settings

In order to force the Excel data provider to treat mixed data types as text, set the following two keys in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

It is stored in a different location when running on a 64bit machine:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

ImportMixedTypes should be set to “Text” (without the quotes)

TypeGuessRows should be set to 0 (zero). This tells the driver to read the first 16384 rows to determine the data type.

Note that your current registry settings for these two values is shown in the Help > About box.

Truncated Text

A side effect of setting the ImportMixedTypes to “Text” as recommended above is that all text fields are truncated to 255 characters. Also, you are limited to 255 columns of data.

Special Cases Regarding Numbers

In certain cases, a number that is formatted as “Text” will be changed to scientific notation (for example 7.84877e+008). The solution is to format the column as “General.” Also, note that numbers in Excel are stored in floating point and so decimal fractions must be rounded to be non-repeating (2.4 is really stored as 2.3999999…). The RoundDecimal function is provided for this purpose.

Excel Password Protected Files

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: “Could not decrypt file.”