Trying to access Excel 2007 via ODBC

[Mike Lewis]

I am trying to retrieve data form an Excel 2007 workbook.

I downloaded AccessDatabaseEngine.EXE from Microsoft and installed it. I understand this installs the drivers for the new Office 2007 data format, including Excel's XLSX format.

I then tried this connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyFile.XLSX;
Extended Properties="Excel 12.0 Xml;HDR=NO"

On trying to connect, the ODBC Select Data Source window appeared. This is the window which prompts you to choose a DSN. I clicked Cancel, and the window went away. The connection was then successful.

However, I don't want the end-user to see this window. So I tried a different connection string:

Provider=MSDASQL.1;Persist Security Info=False;DSN=Excel Files;
DBQ=MyFile.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5

In this case, the connection went smoothly, without anything popping up. But this isn't ideal, because it requires a DSN to be present, which is an extra dependency.

In both cases, when I did a SELECT on the workbook, all the data was correctly returned except the first row. In fact, first row of data appeared as the column headings in the result set, which is not what I want.

I know that the parameter HDR=NO is supposed to treat the first row of the sheet as data, not as a header, but this seems to have no effect. I tried setting it to NO, YES and leaving it out completely. I did this with both the above connection strings. In all cases, the result was the same: the result set was always missing the first row.

So, I've got two questions:

1. If I use the first of the above connection strings, how can I avoid the Select Data Source window from appearing?

2. How can I tell the system to return the first row of the worksheet?

I hope I have posted this in the right forum. I wondered about posting in the VBA forum, but this isn't really a VB issue. I'm doing all this from a different development platform (Visual FoxPro).

Thanks in advance.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[SkipVought]

did you try putting the entire path & file name in the Data Source"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mike Lewis]

Yes, I did use the full path and filename, Skip. I just omitted it here for brevity.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[SkipVought]

i've got both my PC's tied up.

Are you using MS Query, ADO, DAO?

With MS Query, in Excel Data > Import External Data > Data Parameters is a selection to include row headings or not.

If you're using ADO or DAO, you must write the headings to the sheet in a manner similar to...

dim fld as ADODB.Field, rst as ADODB.Recordset,iCol as integer
...
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

    with YourSheetObject
      iCol = 1
'write the headings in row 1
      for each fld in rst.fields
        .cells(1, iCol).value = fld.name
        iCol = iCol + 1
      next
'return the data to row 2
      .Cells(2,1).copyfromrecordset rst
    end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mike Lewis]

Thanks for your help with this, Skip.

I'm not using MSQUERY, ADO or DAO. Just plain old-fashioned ODBC -- mainly because that's the best option for Visual FoxPro.

That said, I've just tried creating an ADO recordset. It seems to solve the first problem, that is, the unwanted Select Datasource window. But the problem re the missing first row still exists. The behaviour is identical to what I was seeing before.

However, you've given me the germ of an idea. Using ODBC, I can get the data into a VFP cursor. Once I've done that, it should be possible to write FoxPro code that creates a new cursor, with the "correct" field names, and the first row generated from the the field names of the original cursor -- as per your suggestion.

I'll play around with this and report back.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[SkipVought]

All the above use ODBC. You might check the VFP Properties for the QueryTable Object, for a heading switch.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mike Lewis]

Skip,

Not sure I understand. What exactly is the QueryTable object?

Thanks.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[SkipVought]

In VFP you ought to have an object browser. Or maybe it's just VB Help.

This might help you...

http://fox.wikis.com/wc.dll?Wiki~VFPODBCDriver~SoftwareEng

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mike Lewis]

Yes, we certainly have an object browser. And I can see a QueryTable object in Excel. I can't at first glance see any PEMs related to header rows, but I'll look more closely.

But I don't see how this helps. I'm not using Automation for this exercise. I'm simply using ODBC to get the worksheet data into a cursor. I haven't got access to any Excel classes. In fact, I can't even assume that Excel will be installed on the user's system.

Sorry if I'm missing something obvious. I'm not an expert in this area.

Re the link you sent me to the Wiki article. This in fact is about VFP's own ODBC driver, that is, the driver you would use to access VFP data from another platform. It doesn't help with my present problem (although it's an interesting article anyway).

Thanks for your on-going help with this.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

OK, I think I've got it sorted. For the benefit of others who might have this problem, this is what I did (the code is FoxPro, but should be easy to translate to other languages):

lcStr = ;
  [Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\myfile.xlsx]

lcCmd = [SELECT * FROM "Sheet1$"]

lnHandle = SQLSTRINGCONNECT(lcStr)
IF lnHandle > 0
  SQLEXEC(lnHandle, lcCmd, "csrExcel")
ELSE 
  * Could not get a connection 
ENDIF

If the workbook is an XLSX, this works perfectly. You end up with a cursor (roughly equivalent to a recordset) with all the rows intact, and with sensible column names like Column1, Column2, .....

If the workbook is an XLS file (whether created in Excel 2007 or an earlier version), the first row of the data is used as column headings. Because the data is not necessarily character data, you might end up with column names that are really numbers or dates.

To get round that, I wrote some code that copies the cursor to an array, then loops through the column names, inserting these into the first row of the array, and finally writing the array back to a new cursor.

I won't post that code here, because it's not really related to the ODBC problem. If anyone wants to see, they should let me know and I'll be happy to share it.

Skip, thanks again for your help with this. You put me in the right direction.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[SkipVought]

Mike,

Glad you found a solution.

Happy to lend a hand.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]