ODBC and MS Excel

Microsoft Excel is a spreadsheet application. Since ODBC was primarily designed for databases special rules have to be followed to read and write Excel data using ODBC:

• A table of data is referred to as either a named range (e.g. MyRange), a worksheet name (e.g. [Sheet1$]) or an explicit range (e.g. [Sheet1$B2:C12]).
• By default, the first row of a range is used for naming the columns (to be used in SQL statements). The option FIRSTROWHASNAMES=0 disables this feature and columns are implicitly named F1, F2... However, even with this option, the first row is ignored and cannot contain data.
• The data type of columns is deduced by the Excel driver by scanning the first 8 rows. The number of rows analyzed can be changed using the option MAXSCANROWS=n (n between 1 and 8).

It is important to be aware that when writing to database tables specified by a named range in Excel, they will increase in size if new data is added using an INSERT statement. To overwrite existing data in the worksheet, the SQL statement UPDATE can be used in most cases (although this command is not fully supported). Now suppose that we wish to write further data over the top of data that has already been written to a range using an INSERT statement. Within Excel it is not sufficient to delete the previous data by selecting it and hitting the Delete key. If this is done, further data will be added after a blank rectangle where the deleted data used to reside. Instead, it is important to use Edit/Delete/Shift cells up within Excel, which will eliminate all traces of the previous data, and the enlarged range.

Microsoft Excel tables can be created and opened by only one user at a time. However, the "Read Only" option available in the Excel driver options allows multiple users to read from the same .xls files.

When first experimenting with acquiring or writing data via ODBC it is tempting to use short names for column headings. This can lead to horrible-to-diagnose errors if you inadvertently use an SQL keyword. We strongly recommend that you use names like ``myParameters'', or ``myParams'', or ``myTime'', which will not clash with SQL reserved keywords.