×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Trying to access Excel 2007 via ODBC

Trying to access Excel 2007 via ODBC

Trying to access Excel 2007 via ODBC

(OP)
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:

CODE

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:

CODE

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
 

RE: Trying to access Excel 2007 via ODBC




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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Trying to access Excel 2007 via ODBC



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

CODE

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Trying to access Excel 2007 via ODBC

(OP)
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
 

RE: Trying to access Excel 2007 via ODBC




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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Trying to access Excel 2007 via ODBC

(OP)
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
 

RE: Trying to access Excel 2007 via ODBC

(OP)
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):

CODE

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
 

RE: Trying to access Excel 2007 via ODBC



Mike,

Glad you found a solution.  

Happy to lend a hand.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close