Click here to Skip to main content
11,415,973 members (84,712 online)
Click here to Skip to main content

Reading Excel files using ODBC

, 12 Jan 2000 CPOL
Rate this:
Please Sign up or sign in to vote.
A discussion and demonstration of reading Excel files using ODBC
  • Download demo project - 20 Kb

    The Problem

    After contributing that article about writing into an Excel file I got tons of requests about how to read from one. Well, you asked for it...

    The main problem is that you can´t read an Excel file without previously having some formatting done. Microsoft refers to this in one of their KB papers. If somewhere out there finds there´s a way to do the reading whithout the formatting, please let me know...

    Another problem is the DSN you need to have installed in your ODBC Admin. This is not very useful because you don´t always know the name of the Excel file from the start.

    The last problem I´m dealing with here is generally doing ODBC reading using CRecordset without deriving from it. That is because if I always have to create a class for every single table I want to use, I´ll end up with lots of rather unnecessary code enlarging my app´s exe.

    The 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 don´t 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 Excel´s menu. There can be more than one "table" on a worksheet. This means that a sheet isn´t 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, you´ll see what I mean...
    2. Omiting the DSN tag in the connect string of CDatabase:Open() gives the opportunity to refer the ODBC-Driver directly using its name so we don´t have to have a DSN registered. This, of course, implies that the name of the ODBC-Driver is exactly known. If it isn´t, a call to SQLGetInstalledDrivers() will show all the installed drivers. For an example see CReadExcelDlg::GetExcelDriver() below.
    3. To use CRecordset the plain way you have to use a readonly, foreward only recordset. The data to get is defined by the SQL statement you put into CRecordset::Open(). Reading out the result is done by CRecordset::GetFieldValue(). For an example see the code below.

    What is needed

    In order to get the code below going you have to

  • include <afxdb.h>
  • include <odbcinst.h>
  • install an ODBC-driver called "MICROSOFT EXCEL DRIVER (*.XLS)" (or something like that)
  • You must 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 isn´t installed. The result of an attempt to do so is some mumbling about missing registry keys.

    If using an underived CRecordset it needs to be readonly, foreward only. So any attempts to change the data or to move back will fail horribly. If you need to do something like that you´re bound to use CRecordset the "usual" way. Another drawback is that the tremendous overhead of CRecordset does in fact make it rather slow. A solution to this would be using the class CSQLDirect contributed by Dave Merner at http://www.codeguru.com/mfc_database/direct_sql_with_odbc.shtml.

    There´s 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 I´d be glad to read it under almikula@EUnet.at (please make a CC to alexander.mikula@siemens.at).

    The Source Code

    // 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 didn´t 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 don´t 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;
    }
    

    Please refer the demo project (ReadExcelDlg.cpp) for more details.

  • License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Share

    About the Author

    No Biography provided

    Comments and Discussions

     
    Questionmissing mfc100.dll Pin
    yaoohmu7-Dec-11 22:47
    memberyaoohmu7-Dec-11 22:47 
    GeneralMy vote of 5 Pin
    Mahdi Nejadsahebi22-Oct-11 3:26
    groupMahdi Nejadsahebi22-Oct-11 3:26 
    QuestionIs Excel needed? Pin
    Ranke19-Oct-10 12:37
    memberRanke19-Oct-10 12:37 
    GeneralSize of field problem Pin
    yongdiego29-Jul-08 14:52
    memberyongdiego29-Jul-08 14:52 
    GeneralFiring query against EXCEL in Java servlet Pin
    Viral4-Dec-07 3:21
    sussViral4-Dec-07 3:21 
    GeneralError in GetExcelDriver() Pin
    Jochen Arndt23-Aug-07 3:37
    memberJochen Arndt23-Aug-07 3:37 
    GeneralWriting the files to ACCESS instead of Excel Pin
    zouris2-Jul-07 14:02
    memberzouris2-Jul-07 14:02 
    GeneralProblem running the example project Pin
    tantraseeker14-May-07 5:50
    membertantraseeker14-May-07 5:50 
    Hi,

    the executable runs fine but when I compile the project and try to run it I get the following error message :

    "Database error : The Microsoft Jet database engine could not find the object "demo_table". Make sure the object exists and that you spell its name ant the path name correctly."

    What could it be? My version of Office?

    Thanks,

    Francis
    GeneralRe: Problem running the example project Pin
    nitin_ap18-May-07 2:24
    membernitin_ap18-May-07 2:24 
    GeneralReading from Excel Pin
    Biswajit Ghosh15-Mar-07 7:36
    memberBiswajit Ghosh15-Mar-07 7:36 
    GeneralI ha ve more than three rows , this program reads only 3 rows. Pin
    rajendrappa5-Dec-06 21:44
    memberrajendrappa5-Dec-06 21:44 
    GeneralRe: I ha ve more than three rows , this program reads only 3 rows. Pin
    Member 48216498-Jan-08 17:46
    memberMember 48216498-Jan-08 17:46 
    GeneralRe: I ha ve more than three rows , this program reads only 3 rows. Pin
    vakka26-Feb-09 0:49
    membervakka26-Feb-09 0:49 
    Generalexcel project deployment Pin
    fatih isikhan11-Apr-06 4:59
    memberfatih isikhan11-Apr-06 4:59 
    GeneralReading from excel without formatting Pin
    hupu15-Dec-05 2:22
    memberhupu15-Dec-05 2:22 
    QuestionReading/Writing Excel sheets in unix using C++ Pin
    Tanz21-Sep-05 6:14
    memberTanz21-Sep-05 6:14 
    AnswerRe: Reading/Writing Excel sheets in unix using C++ Pin
    logitecherrr22-Jul-06 4:06
    memberlogitecherrr22-Jul-06 4:06 
    QuestionHow to use a SQL clause? Pin
    Disen30-Mar-05 21:05
    memberDisen30-Mar-05 21:05 
    GeneralDatabase Error Pin
    ektoplasma200021-Feb-05 2:21
    memberektoplasma200021-Feb-05 2:21 
    QuestionHelp!! How to read 2 or more columns? Pin
    helen_kwan112-Jan-05 7:17
    memberhelen_kwan112-Jan-05 7:17 
    AnswerRe: Help!! How to read 2 or more columns? Pin
    helen_kwan112-Jan-05 7:32
    memberhelen_kwan112-Jan-05 7:32 
    AnswerRe: Help!! How to read 2 or more columns? Pin
    vakka26-Feb-09 0:57
    membervakka26-Feb-09 0:57 
    GeneralAn Error message Pin
    QTina2-Dec-04 11:06
    sussQTina2-Dec-04 11:06 
    GeneralReading table names from excelfile. Pin
    Ard Kunst2-Dec-04 2:28
    memberArd Kunst2-Dec-04 2:28 
    GeneralRe: Reading table names from excelfile. Pin
    JR Cooper13-Feb-06 10:25
    memberJR Cooper13-Feb-06 10:25 
    GeneralRe: Reading table names from excelfile. Pin
    markusschroth16-Jul-07 22:53
    membermarkusschroth16-Jul-07 22:53 
    AnswerRe: Reading table names from excelfile. Pin
    vakka26-Feb-09 0:55
    membervakka26-Feb-09 0:55 
    Questionchange font style and colors in spreadsheet? Pin
    xxhimanshu23-Feb-04 20:08
    sussxxhimanshu23-Feb-04 20:08 
    Generaldeleting from excel Pin
    paoloromani19-Feb-04 22:55
    memberpaoloromani19-Feb-04 22:55 
    Generaltype dependence Pin
    gok2-Feb-04 14:31
    membergok2-Feb-04 14:31 
    GeneralRe: type dependence Pin
    acerunner31613-Jun-07 16:27
    memberacerunner31613-Jun-07 16:27 
    GeneralRe: type dependence Pin
    gok13-Jun-07 17:05
    membergok13-Jun-07 17:05 
    GeneralHELP!!!!! excel convert to HTML Pin
    kee_kee14-Jun-03 3:54
    memberkee_kee14-Jun-03 3:54 
    GeneralRe: HELP!!!!! excel convert to HTML Pin
    Anonymous27-Aug-03 14:10
    sussAnonymous27-Aug-03 14:10 
    GeneralPlain C Version Available (plus major issue) Pin
    Anonymous19-Feb-03 5:26
    sussAnonymous19-Feb-03 5:26 
    GeneralRe: Plain C Version Available (plus major issue) Pin
    bats_forever16-Jul-03 4:39
    memberbats_forever16-Jul-03 4:39 
    GeneralNo need to create range. Pin
    Anonymous3-Feb-03 23:34
    sussAnonymous3-Feb-03 23:34 
    GeneralWriting Excel Files Pin
    Ludix26-Dec-02 22:46
    sussLudix26-Dec-02 22:46 
    Questionanybody konw why?? Pin
    dragonline5253-Dec-02 17:05
    memberdragonline5253-Dec-02 17:05 
    AnswerRe: anybody konw why?? Pin
    ludix26-Dec-02 22:42
    sussludix26-Dec-02 22:42 
    QuestionIs there problem with license? Pin
    bert0127-Jan-02 6:19
    memberbert0127-Jan-02 6:19 
    QuestionTable name? Pin
    Ammar17-Apr-01 2:58
    memberAmmar17-Apr-01 2:58 
    AnswerRe: Table name? Pin
    Ammar17-Apr-01 3:22
    memberAmmar17-Apr-01 3:22 
    AnswerRe: Table name? Pin
    Masudkhan16-Jan-07 2:30
    memberMasudkhan16-Jan-07 2:30 
    GeneralTable names Pin
    Laura Michaels14-Feb-01 8:49
    memberLaura Michaels14-Feb-01 8:49 
    GeneralRe: Table names Pin
    Anonymous15-Jun-01 3:47
    memberAnonymous15-Jun-01 3:47 
    GeneralRe: Table names Pin
    olafurog17-Jul-01 8:11
    memberolafurog17-Jul-01 8:11 
    GeneralRe: Table names Pin
    Brian Mason23-Jul-01 5:53
    memberBrian Mason23-Jul-01 5:53 
    GeneralRe: Table names Pin
    Brian Mason23-Jul-01 5:48
    memberBrian Mason23-Jul-01 5:48 
    GeneralRe: Table names Pin
    AR Schleicher6-Sep-01 9:22
    memberAR Schleicher6-Sep-01 9:22 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

    | Advertise | Privacy | Terms of Use | Mobile
    Web03 | 2.8.150427.4 | Last Updated 13 Jan 2000
    Article Copyright 2000 by Alexander Mikula
    Everything else Copyright © CodeProject, 1999-2015
    Layout: fixed | fluid