Reading Excel files using ODBC
Rating: none
Rating: none
| Alexander Mikula (view profile) May 15, 1999 |
Problem
After contribution that article about writing into an Excel file I got tons of requests about how to read from one. Well, you asked for it... 1) In fact the main problem is that you can4t read an Excel file without previously having some formatting done. Microsoft refers to this in one of their KB papers. If somewhere out there there4s a way to do the reading whithout the formatting, please let me know...
(continued)
Solution
1) According to Microsoft, an Excel sheet of version 4.x and later can only be read by ODBC if a database range is defined. Unfortunately they don4t state how to do this exactly. One way to let ODBC know what data is in there is to name a range of data on a worksheet using "Insert->Names" from Excel4s menu. There can be more than one "table" on a worksheet. This means that a sheet isn4t necessarily the same as a table in a "real" database. If you open "ReadExcel.xls" from the attached demo project and look up the names, you4ll see what I mean...Needed
In order to get the code below going you have toinstall an ODBC-driver called "MICROSOFT EXCEL DRIVER (*.XLS)" (or something like that)
use an ODBC Admin version 3.5 or higher
Drawbacks
Using a pseudo DSN only works with ODBC Admin V3.51 and higher. Earlier versions will not be able to use a DSN that actually isn4t installed. The result of an attempt to do so is some mumbling about missing registry keys.There4s still work to do
One unsolved mystery in reading those files is how to get the data WITHOUT having a name defined for it. That means how can the structure of the data be retrieved, how many "tables" are in there, and so on. If you have any idea about that I4d be glad to read it under almikula@EUnet.at (please make a CC to alexander.mikula@siemens.at)Source Code
Please refer the demo project (ReadExcelDlg.cpp) for more details.
// Query an Excel file
void CReadExcelDlg::OnButton1()
{
CDatabase database;
CString sSql;
CString sItem1, sItem2;
CString sDriver;
CString sDsn;
CString sFile = "ReadExcel.xls"; // the file name. Could also be something like C://Sheets//WhatDoIKnow.xls
// Clear the contents of the listbox
m_ctrlList.ResetContent();
// Retrieve the name of the Excel driver. This is
// necessary because Microsoft tends to use language
// specific names like "Microsoft Excel Driver (*.xls)" versus
// "Microsoft Excel Treiber (*.xls)"
sDriver = GetExcelDriver();
if( sDriver.IsEmpty() )
{
// Blast! We didn4t find that driver!
AfxMessageBox("No Excel ODBC driver found");
return;
}
// Create a pseudo DSN including the name of the Driver and the Excel file
// so we don4t have to have an explicit DSN installed in our ODBC admin
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,sFile);
TRY
{
// Open the database using the former created pseudo DSN
database.Open(NULL,false,false,sDsn);
// Allocate the recordset
CRecordset recset( &database );
// Build the SQL string
// Remember to name a section of data in the Excel sheet using "Insert->Names" to be
// able to work with the data like you would with a table in a "real" database. There
// may be more than one table contained in a worksheet.
sSql = "SELECT field_1, field_2 "
"FROM demo_table "
"ORDER BY field_1";
// Execute that query (implicitly by opening the recordset)
recset.Open(CRecordset::forwardOnly,sSql,CRecordset::readOnly);
// Browse the result
while( !recset.IsEOF() )
{
// Read the result line
recset.GetFieldValue("field_1",sItem1);
recset.GetFieldValue("field_2",sItem2);
// Insert result into the list
m_ctrlList.AddString( sItem1 + " --> "+sItem2 );
// Skip to the next resultline
recset.MoveNext();
}
// Close the database
database.Close();
}
CATCH(CDBException, e)
{
// A database exception occured. Pop out the details...
AfxMessageBox("Database error: "+e->m_strError);
}
END_CATCH;
}
// Get the name of the Excel-ODBC driver
// Contibuted by Christopher W. Backen - Thanx Christoper
CString CReadExcelDlg::GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;
// Get the names of the installed drivers ("odbcinst.h" has to be included )
if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
return "";
// Search for the driver...
do
{
if( strstr( pszBuf, "Excel" ) != 0 )
{
// Found !
sDriver = CString( pszBuf );
break;
}
pszBuf = strchr( pszBuf, '/0' ) + 1;
}
while( pszBuf[1] != '/0' );
return sDriver;
}
Date Last Updated: May 14, 1999