none
How to access Excel files using ODBC without skipping first row with Access 2013 Runtime? RRS feed

  • Question

  • First, I am interested in finding documentation on how to use the ODBC driver provided by Access 2013 Runtime. I cannot seem to find with any of my googling a list of the supported parameters which may be included in my ConnectionString. Where is ACE ODBC’s Excel support and parameters documented? DAO for 2007 seems to provide an example of a connection string using the ACE ODBC driver, but for the Access version and for Office 2007 rather than 2013 and it doesn’t itemize the ODBC ConnectionString properties used or available.

    Regarding my issue, I found an old KB that states that FirstRowHasNames/Header is ignored by ACE. It states that MS acknowledged it as a bug. I figured that if MS acknowledges it as a bug and the KB is about an unsupported version of Office, the supported modern versions of Office would have this bug fixed and I should be able to specify FirstRowHasNames in my ConnectionString, right? https://support.microsoft.com/en-us/kb/288343

    That same article suggests using JET drivers. But I was interested in getting this working with 64-bit because that’s the future. And it seems that JET doesn’t exist outside of 32-bit.

    I am probably going to give up and use OLEDB instead of ODBC in the end. But it seems ridiculous that I have to use a less generic data access interface. Is there a way to access Excel files via ODBC with 64-bit without skipping the first row? Is there a bug for this I can subscribe to/vote for?

    My current connection string looks like: “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=C:\Path\to\dot.xslx”. I installed the 64-bit version of Access 2013 Runtime and it works fine if the first row in the Excel worksheet is a header row. When I add “HDR=NO” or “Header=False” or “FirstRowHasNames=0”, I get a warning like “ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute FirstRowHasNames” (with the respective name). I’ve even tried passing it like “Extended Properties="HDR=NO"” which gives the same error about how “Extended Properties” is unsupported.

    Will this be fixed in Access 2016 Runtime? ;-)

    Monday, December 14, 2015 9:39 PM

Answers

  • Hi ohnobinki,

    >>My current connection string looks like: “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=C:\Path\to\dot.xslx”. I installed the 64-bit version of Access 2013 Runtime and it works fine if the first row in the Excel worksheet is a header row. When I add “HDR=NO” or “Header=False” or “FirstRowHasNames=0”, I get a warning like “ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute FirstRowHasNames” (with the respective name). I’ve even tried passing it like “Extended Properties="HDR=NO"” which gives the same error about how “Extended Properties” is unsupported.

    ODBC hasn’t a parameter about controlling the first row, but Olbde has a parameter controlling the first row. You could try to use oledb connect excel. Please try the following steps if you use odbc connect to excel.

    Before ODBC connect excel.  First open the Excel (oExcel = Createobject ("Excel.application")), and then in front of the first line insert a row fake title.

    >>Will this be fixed in Access 2016 Runtime? ;-)

    Access 2016 Runtime will encounter the same problem.

    Best Regards,

    Tuesday, December 15, 2015 9:22 AM
    Moderator

All replies

  • Hi,

    Since this issue is related to ado.net DEV, I'll move your thread to its' forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Tuesday, December 15, 2015 5:13 AM
  • Hi ohnobinki,

    >>My current connection string looks like: “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=C:\Path\to\dot.xslx”. I installed the 64-bit version of Access 2013 Runtime and it works fine if the first row in the Excel worksheet is a header row. When I add “HDR=NO” or “Header=False” or “FirstRowHasNames=0”, I get a warning like “ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute FirstRowHasNames” (with the respective name). I’ve even tried passing it like “Extended Properties="HDR=NO"” which gives the same error about how “Extended Properties” is unsupported.

    ODBC hasn’t a parameter about controlling the first row, but Olbde has a parameter controlling the first row. You could try to use oledb connect excel. Please try the following steps if you use odbc connect to excel.

    Before ODBC connect excel.  First open the Excel (oExcel = Createobject ("Excel.application")), and then in front of the first line insert a row fake title.

    >>Will this be fixed in Access 2016 Runtime? ;-)

    Access 2016 Runtime will encounter the same problem.

    Best Regards,

    Tuesday, December 15, 2015 9:22 AM
    Moderator
  • Some of the Excel driver settings have never worked from the connection string and probably never will. Instead, you will need to change them in the Registry (either programmatically or through the Registy Editor). Just remember that there may be entries for both the 32-bit and 64-bit drivers.

    https://msdn.microsoft.com/en-us/library/office/ff844939.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, December 15, 2015 1:23 PM