Reading Excel files using ODBC

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...

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

3) The last problem I4m 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, I4ll end up with lots of rather unnecessary code enlarging my app4s exe.

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...

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 don4t have to have a DSN registered. This, of course, implies that the name of the ODBC-Driver is exactly known. If it isn4t, 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.

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)

Source code

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.

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 you4re 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 codeguru4s http://www.codeguru.com/mfc_database/direct_sql_with_odbc.shtml

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


// 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;
}
Please refer the demo project (ReadExcelDlg.cpp) for more details.

Download demo project - 20 KB

Date Last Updated: May 14, 1999



Comments

  • Old president denounce is general assist absurd opinion on public affairs0

    Posted by ngsalnxgpu on 05/17/2013 06:39pm

    Old president denounce is general assist absurd [url=http://jordanscheap.page4.me/]air jordans cheap[/url] opinion on public affairs On Feburary 3 morning, a running fire of course of study of Wu Cheng of president of university of former overseas Chinese 3 small gain, to cardinal principle assist the opinion on public affairs of the near future undertakes denounce one by one, appeal concerned branch to appear personally the settlement that promotes an issue, bo Wenyuan article is as follows: General assist in stating on Feburary 1, say some popularizes an enterprise to be illicit look forward to, discover this morning oneself delete, answer truly cutout, because this formulation takes discrimination sex and violate policy of central reforming and opening. Look from contemporary company system, only the state-owend enterprise is mixed civilian look forward to, and company of last name of ultimate decision company and endowment do not decide by system of ownership only, this is the economics common sense of an at least. Party card government office and mass organizations all need to work inside constitution frame. Statement told us on Feburary 1, a few comrades do not study the policy policy of the party for a long time, thought stale, style is tough, make can do good thing to do bad thing, hurt the heart of most masses, the career that gives a party causes a loss. Communicating is the main way that settles people interior to contradict, still look at cardinal principle assist attach most importance to with people interest, whether to support the jumping-off place that serves as the job with the undergraduate, clutch remedy the job, correct error, do not tough act, painful Chou Zhe of the person that make kiss is fast. Appeal sincerely. CUBA changes a controversy the impact is very big, oneself cause bad effect to party and governmental figure, and also not be the problem that incident both sides can solve by oneself. Ask body of Ministry of Education to defend department to fulfill duty to appear personally solve, teach minister and undersecretary of be assigned personal responsibility for to appear personally please drive solve. No time to lose in doing the job, 10 thousand hope to cause take seriously. My the words of the lowly person carry little weight, fraught, 10 thousand look 10 thousand hope authorities takes seriously, 10 thousand look 10 thousand hope authorities sees this letter.

    Reply
  • Neutral summary shows you Unique all new things about nike shoes that no one is discussing.

    Posted by moisseenfogma on 05/16/2013 03:07am

    X [url=http://www.nikekutuja.biz/]nike スニーカー[/url] hnLps McpYoq OsgZyb Vju [url=http://www.nikekutuja.biz/air-jordan空気ヨルダン-c-1.html]air jordan[/url] RcgNweOsf Kl [url=http://www.nikekutuja.biz/air-maxエアマックス-c-2.html]nike air max[/url] wHxvZmzKkn MzeJfuXbv [url=http://www.nikekutuja.biz/nike-air-force-1ナイキエアフォース-c-4.html]air force[/url] Lww ZciUaw Wf [url=http://www.nikekutuja.com/]nike スニーカー[/url] nWsd NwxWjp FufJgi QfyL [url=http://www.nikekutuja.com/air-jordan空気ヨルダン-c-1.html]air jordan[/url] ifBecHgd DaoLegDkvT [url=http://www.nikekutuja.com/nike-dunkナイキダンク-c-4.html]nike dunk[/url] ab RheKadFm [url=http://www.nikekutuja.com/air-maxエアマックス-c-2.html]air max 95[/url] tKgd QflOhd Of [url=http://www.nikekutujp.com/]nike スニーカー[/url] qWfn TkhGws EtaCek RbzY [url=http://www.nikekutujp.com/nike-dunkナイキダンク-c-4.html]nike dunk[/url] lrWrbIvv Z [url=http://www.nikekutujp.com/nike-air-force-1ナイキエアフォース-c-3.html]air force[/url] kfXutYvgMbs [url=http://www.nikekutujp.com/air-jordan空気ヨルダン-c-1.html]air jordan[/url] IugTihUcjNwh PpwTrj

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • With 81% of employees using their phones at work, companies have stopped asking: "Is corporate data leaking from personal devices?" and started asking: "How do we effectively prevent corporate data from leaking from personal devices?" The answer has not been simple. ZixOne raises the bar on BYOD security by not allowing email data to reside on the device. In addition, Zix allows employees to maintain complete control of their personal device, therefore satisfying privacy demands of valued employees and the …

  • Managing your company's financials is the backbone of your business and is vital to the long-term health and viability of your company. To continue applying the necessary financial rigor to support rapid growth, the accounting department needs the right tools to most efficiently do their job. Read this white paper to understand the 10 essentials of a complete financial management system and how the right solution can help you keep up with the rapidly changing business world.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds