Error using Data Source relative path in Excel Data Driven Unit Test

Question

I am using a System.Data.Odbc connection string to connect to an Excel data source. The following error occurs when using a relative dbq path:

ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

App.config:

<configuration>
    <configSections>
        <section name="microsoft.visualstudio.testtools" type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
    </configSections>
    <connectionStrings>
        <add name="excelConnection" connectionString="Dsn=Excel Files;Dbq=ExcelDataSource.xlsx;Defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5" providerName="System.Data.Odbc"/>
    </connectionStrings>
    <microsoft.visualstudio.testtools>
        <dataSources>
            <add name="excelDataSource" connectionString="excelConnection" dataTableName="Sheet1$" dataAccessMethod="Sequential"/>
        </dataSources>
    </microsoft.visualstudio.testtools>
</configuration>

Test Method Attributes:

[DeploymentItem("ExcelDataSource.xlsx")]
[DataSource("excelDataSource")]
[TestMethod]

Troubleshooting notes:

Works with an absolute dbq path
Excel file is set to 'Copy if Newer' and [DeploymentItem] is specified
Fails before reaching the [TestInitialize] method; before the Test Deployment Dir is created.
It worked in the past, perhaps in an older Visual Studio version

Question: Where does defaultDir=.; resolve to?

Actual sheets names: "Full", "Lite". I'm trying to access "Full". — user3167162, Apr 22 '15 at 23:17
Yes, I substituted 'Full' with 'Sheet1' to be more generic. As I said it works with an absolute path so the names appear correct. — user3167162, Apr 22 '15 at 23:28

score 2 · Accepted Answer · edited May 23 '17 at 11:43

2

It resolved correctly using |DataDirectory| variable: defaultDir=|DataDirectory|.

connectionString="Dsn=Excel Files;Dbq=ExcelDataSource.xlsx;Defaultdir=|datadirectory|\;driverid=790;maxbuffersize=2048;pagetimeout=5"

More on the |DataDirectory| variable here

edited May 23 '17 at 11:43

Community

1
1

answered Apr 23 '15 at 00:20

user3167162

445
1
6
23

Error using Data Source relative path in Excel Data Driven Unit Test

1 Answers1