Unable to read numeric values from Excel file using CDaoRecordset

[AnitaEugene]

Hi
I am using CDaoRecordset and GetFieldValue to read field values from an excel file.
But, I am not able to read the numeric fields. I only get VT_NULL when I try to read the numeric field.
If I manually change the field to text, I am able to read.

Now...
How do I read the numeric field using GetFieldValue or using any other method?
Thanks
Eugene

[ovidiucucu]

Hi
I am using CDaoRecordset and GetFieldValue [...]

You can do this in many ways and you didn't tell us which one.

Just by curiousity I have made the following brief test

    // just for testing purpose
    TRY 
    {
        CDaoDatabase db;
        db.Open(_T("c:\\test.xls"), FALSE, FALSE, _T("Excel 5.0;"));
        CDaoRecordset rs(&db);
        rs.Open(-1, _T("SELECT * FROM [Sheet1$]"));
        const short nCount = rs.GetFieldCount();
        while(!rs.IsEOF())
        {
            for(short nField = 0; nField < nCount; nField++)
            {
                COleVariant varValue;
                rs.GetFieldValue(nField, varValue);
                //...
            }
            rs.MoveNext();
        }
    }
    CATCH_ALL(e)
    {
        e->ReportError();
    }
    END_CATCH_ALL

That worked well both for text and number field types.

BTW. CDaoXxx classes are deprecated and newer compilers give a warning which look like

warning C4995: 'CDaoDatabase': name was marked as #pragma deprecated
warning C4995: 'CDaoRecordset': name was marked as #pragma deprecated

.
Better is to use somthing else instead of DAO, like ODBC for example (see CDatabase and CRecordset MFC classes).

[ovidiucucu]

Here is a little bit modified test and a picture of the "table".

    // just for testing purpose
    TRY 
    {
        CDatabase dbb;
        CDaoDatabase db;
        db.Open(_T("c:\\test.xls"), FALSE, FALSE, _T("Excel 5.0;"));
        CDaoRecordset rs(&db);
        rs.Open(AFX_DAO_USE_DEFAULT_TYPE, _T("SELECT Name, Age FROM [Sheet1$]"));
        while(!rs.IsEOF())
        {
            COleVariant vtName, vtAge;
            rs.GetFieldValue(_T("Name"), vtName);
            rs.GetFieldValue(_T("Age"), vtAge);
            CString strName = vtName.bstrVal;
            UINT nAge = static_cast<UINT>(vtAge.dblVal);
            TRACE2("Name: &#37;s  Age: %u\n", strName, nAge);
            rs.MoveNext();
        }
    }
    CATCH_ALL(e)
    {
        e->ReportError();
    }
    END_CATCH_ALL

The output is

Name: John  Age: 23
Name: Mary  Age: 45
Name: Bob  Age: 34

And again: DAO is deprecated!
See Compiler warning C4995.

.

[AnitaEugene]

Hi
Thanks for your reply. When I create a new Excel file, I am able to read text and numbers. But, please download the Excel file from the link.

http://www.sharewaredreams.com/downloads/sample.xls


I am not able to read the numeric field B11,B12 and B13. Could you please check?
Eugene

[VictorN]

I am not able to read the numeric field B11,B12 and B13. Could you please check?
Eugene

What is the problem?
Do you use excepting handling?
Could you show your code?

[AnitaEugene]

Here is the code. Its all good. I am able to read all the fields with text. But, not able to read the numeric fields.

Also, the problem comes only with the sample file, which you can download
Thanks for your help.
Eugene

	try{

		pDB=new CDaoDatabase;
	
		pDB->Open(sTableName,false,false,_T("Excel 5.0;IMEX=2;HDR=No"));
		int totTables=pDB->GetTableDefCount();
		//---------------------Display the tables
		CDaoTableDefInfo tbinfo;
		for(int j=0;j<totTables;j++){
			pDB->GetTableDefInfo(j,tbinfo,AFX_DAO_PRIMARY_INFO);
			if(!(tbinfo.m_lAttributes & dbSystemObject))
				//m_ListBox1.AddString(tbinfo.m_strName);
				TRACE(tbinfo.m_strName);TRACE(_T("\r\n"));
		}
		//-----------open and display Fields
		CString sSheet;
		m_Sheets.GetWindowText(sSheet);
		CDaoTableDef td(pDB);
		td.Open(sSheet);
		CDaoFieldInfo fldinfo;
		short fieldcount=td.GetFieldCount();
		//---------------------Open recordset and get the record count
		CDaoRecordset MyRecordset(pDB);
		MyRecordset.Open(&td);
		int iPage=0;
		iGlobalBufferPointer=0;
		UINT fracPart;
		__int64 unitPart;
		int iAbsoluteCount=0;
		int iCount=0;
		int iRecordCount=0;
		COleVariant vtFld;
		while(!MyRecordset.IsEOF()){
			short shFields=MyRecordset.GetFieldCount();
			vtFld=MyRecordset.GetFieldValue(0);
			vtFld=MyRecordset.GetFieldValue(1);

			short shType=MyRecordset.GetType();
			int iBroken=0;
			
			for(int i=0;i<shFields;i++){
				CDaoFieldInfo FieldInfo;
				MyRecordset.GetFieldInfo(i,FieldInfo);
				vtFld=MyRecordset.GetFieldValue(i);
				switch(vtFld.vt){
				case VT_R4:
					sStr = DblToStr(vtFld.fltVal);
					break;
				case VT_R8:
					sStr = DblToStr(vtFld.dblVal);
					break;
				case VT_BSTR:
					sStr = vtFld.bstrVal;
					break;
				case VT_I2:
				case VT_UI1:
					sStr = IntToStr(vtFld.iVal);
					break;
				case VT_INT:
					sStr = IntToStr(vtFld.intVal);
					break;
				case VT_I4:
					sStr = LongToStr(vtFld.lVal);
					break;
				case VT_UI4:
					sStr = ULongToStr(vtFld.ulVal);
					break;
				case VT_DECIMAL:
					break;
				case VT_DATE:
					{
						COleDateTime dt(vtFld);

						sStr = dt.Format(_T("%Y-%m-%d %H:%M:%S"));
					}
					break;
				case VT_EMPTY:
				case VT_NULL:
					sStr.Empty();
					break;
				case VT_BOOL:
					sStr = vtFld.boolVal == VARIANT_TRUE? 'T':'F';
					break;
				case VT_CY:
					unitPart = vtFld.cyVal.int64/10000;
					fracPart = vtFld.cyVal.int64 % 10000;
					//CString strValue;
					sStr.Format( _T( "%I64d.%u" ), unitPart, fracPart );
					break;
				default:
					sStr.Empty();
					break;

				}

			}
			TRACE(sStr);TRACE("\r\n");
			iRecordCount++;
			if(iRecordCount>1000)break;

			MyRecordset.MoveNext();
		};

		MyRecordset.Close();
		td.Close();

		if(pDB->IsOpen())pDB->Close();
		delete pDB;
		pDB=NULL;
	}
	catch(CMemoryException *e)
	{
    	e->Delete();
		if(pDB->IsOpen())pDB->Close();
		delete pDB;
		pDB=NULL;
		AfxDaoTerm( );
		// Announce the error
		SetWindowText(_T("Unable to load database"));
		MessageBox(_T("Unable to open database\nContact us for this database support."),_T("KingMailer warning"),MB_OK);
//		SendMessage(WM_CLOSE,0,0);
		return false;
	}
	catch(CDaoException *e)
	{
		if(e->m_pErrorInfo!=NULL){
			CString sStr;sStr.Format(_T(" %s"),e->m_pErrorInfo->m_strDescription);
			//Log("InitializeDatabase",sStr);bNewLogCreated=true;
			AfxMessageBox(e->m_pErrorInfo->m_strDescription,MB_ICONEXCLAMATION );
		}
    	e->Delete();
		if(pDB->IsOpen())pDB->Close();
		delete pDB;
		pDB=NULL;
		AfxDaoTerm( );
		// Announce the error
		//SetWindowText("Unable to load database");
		MessageBox(_T("Unable to open database\nContact us for this database support."),_T("KingMailer warning"),MB_OK);
//		SendMessage(WM_CLOSE,0,0);
		return false;
	}

[VictorN]

1. Did you try to debug this code?
2. You could also try with "Excel 8.0" rather than "Excel 5.0".

[Arjay]

Isn't CDaoRecordset deprecated?

Can't you use something more modern like the ATL OLEDB consumer classes (which can be used to read Excel files among other things)?

[AnitaEugene]

Hi
Excel 8.0 also same problem.
I tried with CDatabase also. Still problem.
You may try the sample with my download sample.xls file

http://www.sharewaredreams.com/downloads/sample.xls

Any help?
Eugene

[AnitaEugene]

Hi
If I remove the first 9 remark rows, it all works fine.
How does the initial remark rows affect the numeric reading?
Eugene

[AnitaEugene]

Hi
Even if I change the field type to text manuall on the excel sheet, I am still getting the m_nType as 7 instead of 10, which is text.
This makes the read fails and returns null.

Is there a way to set the field as text through program and then read it?
Eugene

[VictorN]

Hi
If I remove the first 9 remark rows, it all works fine.
How does the initial remark rows affect the numeric reading?
Eugene

It sounds like excel driver doesn't want to get the data type for every cell - only for the whole column!
So, if the first cell in the column was formatted as text then all other cells in the column would be treated as text too.

If you want to read data only from some block of the cells you should set the name of the block and then use this <name> in SELECT ... FROM <name> query.

[ovidiucucu]

In a relational database table you cannot have different field types in different rows.

So, you can use the DB-way (DAO, ODBC and so on) to get data from Excel, only for worksheets containing simple tables, with colums consistent from data type point of view, like the one shown in my previous post.

For more complex worksheets you have to choose another way. One is OLE Automation.