Read from Excel using MS ODBC

If you just want to read the contents of an Excel spreadsheet, there is an easier way using the Microsoft Excel (ODBC) Driver.

In a previous post (Foxpro and Excel) we have shown how to control Excel using the Excel.Application object. But if you are just interested in the contents, there is an even easier way to achieve this: The Microsoft Excel (ODBC) driver. that is present on most systems with MS Office installed.

Source: Importing data from a Microsoft Excel 2007 workbook using Visual FoxPro 9.0 (Microsoft Support)

To get an SQL connection to an Excel sheet is no more difficult than this:

lcXLfile = [c:\temp\demo.xlsx]

* Warning: The driver wil never fail! 
* If the file does not exist, this driver wil create the file.
IF !FILE(lcXLfile)
 ? [Excel file not found]
 RETURN .F.
ENDIF

lcConnection = [DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=]+lcXLfile
lnHandle = SQLSTRINGCONNECT(lcConnection)
* Get all sheetnames in a cursor (every sheet is a Table)
? SQLTABLES(lnHandle,[],[crSheets])
lcQuery = [SELECT * FROM ]+TRIM(STRTRAN(crSheets.Table_Name,['],["]))
? SQLEXEC(lnHandle, lcQuery, [crResults])
SQLDISCONNECT(lnHandle)

The SQLTABLES command creates a cursor with a row for each sheet in the Excel file. You can filter the kind of tables in the second parameter. This is a typical row:

Field nameField typeLengthExample
TABLE_CATMemo4C:\TEMP\DEMO.XLSX
TABLE_SCHEMCharacter128.NULL.
TABLE_NAMECharacter128‘Sheet1$’
TABLE_TYPECharacter128TABLE
REMARKSCharacter254.NULL.
SQLTABLES structure

Remarks

Quotes in the SQLTABLES result

The TABLE_NAME field contains single quotes, These seem not to work in a SQL statement. So you have to replace them with double quotes. Check out the lcQuery line in the code above..

Field names in queries

Only when a sheet is properly filled the field names in queries are automatically named correctly: The titles in the first row are accepted as fieldnames. However, if the first row is left blank or there are multiple tables on a page, the result becomes erratic.. In this situation some or all fields get the title F1. and so on (number being the column number).

Field types

It seems the unaltered query results deliver just 2 kinds of columns: Numbers and Memo’s. Both columns may contain .NULL. values.

Quotes in query’s

To compare or use strings in SQL query’s, you need to use the single quotes again. For instance:

lcQuery = [SELECT * FROM "Sheet1$" WHERE surname='de Graaf']
SQLEXEC(lnHandle,lcQuery,[crDeGraaf])

Double complex because the sheet (table) name has to be encapsulated in double quotes, while a compare string has to be encapsulated in single quotes.. I can’t think of a reason for this odd behavior.

Read only

As to be expected, this ODBC method to an Excel file is limited to reading the file. Trying to insert or update using SQL commands renders the result that the query has to be updatable.

Leave a Reply

Your email address will not be published. Required fields are marked *