DataDriven Unit Test using Excel in VS2013

Question

I am creating Datadriven Unit(MS Unit Test) Test using Excel(*.xlsx) file as data source. it is erroring out with the following error

Result Message: The unit test adapter failed to connect to the data source or to read the data. For more information on troubleshooting this error, see "Troubleshooting Data-Driven Unit Tests" (http://go.microsoft.com/fwlink/?LinkId=62412) in the MSDN Library. Error details: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

I have set the Copy to Output directory : always for the DateRangeTest.xlsx I also tried defaultdir=.\;, defaultdir=.;, and defaultdir=c:\projectName\bin\debug\;

Here is the section from app.config

<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="DateRangeConstr" 
      connectionString="Dsn=Excel Files;dbq=DateRangeTest.xlsx;defaultdir=.\; driverid=790;maxbuffersize=2048;pagetimeout=5" 
      providerName="System.Data.Odbc" />
  </connectionStrings>
  <microsoft.visualstudio.testtools>
    <dataSources>
      <add name="DateRangeTestDataSource" 
        connectionString="DateRangeConstr" 
        dataTableName="Sheet1$" 
        dataAccessMethod="Sequential"/>
    </dataSources>
  </microsoft.visualstudio.testtools>

I was able to find out the problem which was related to 32 bit & 64 bit version for the driver. I changed the connection string to be DSN less and it is working now. — NSS, Mar 07 '14 at 06:04
Always be sure to read the descriptions that appear when selecting tags! — Charles, Mar 07 '14 at 07:26
@NaunihalSidhu when you find the solution to your own question, you can post it as an answer and accept it :) — Dzyann, Aug 15 '14 at 20:16

score 3 · Answer 1 · edited Nov 18 '14 at 19:56

There are (2) options here - you can either remove the DSN requirement or install the opposite Excel/Access runtime bitness of the Office product you have installed.

Option 1 - remove DSN from Connection String

Change from this:

<add name="DateRangeConstr" 
    connectionString="Dsn=Excel Files;dbq=DateRangeTest.xlsx;defaultdir=.\; driverid=790;maxbuffersize=2048;pagetimeout=5" 
    providerName="System.Data.Odbc" />

To this:

<add name="DateRangeConstr" 
    connectionString="dbq=DateRangeTest.xlsx;defaultdir=.\; driverid=790;maxbuffersize=2048;pagetimeout=5" 
    providerName="System.Data.Odbc" />

Option 2 - Install ODBC Drivers for Opposite Bitness

For Office x64, install Microsoft Access Database Engine 2010 Redistributable for x86 (AccessDatabaseEngine.exe)
For Office x64, install Microsoft Access Database Engine 2010 Redistributable for x64 (AccessDatabaseEngine_x64.exe).

This fix should be the same whether you have Office 2010 or Office 2013.

To test this change - you should be able to configure the Excel Files DSN for both x86 and x64 once installed.

32-bit ODBC: %WINDIR%\SysWOW64\odbcad32.exe
64-bit ODBC: %WINDIR%\System32\odbcad32.exe

Click on "Excel Files" and then click "Configure" and you should see a dialog. If an error occurs when you click "Configure" - you don't have the proper office driver installed for that bitness.

score 0 · Answer 2 · answered May 21 '17 at 05:23

We need not to install anything to our system to run the external excel data driven unit tests.

We need to simply run the Data Source Wizard present under Visual Studio Data Source Explorer.

Watch this video. This Tutorial tells how to run Data Driven (Parameters) unit tests using Visual Studio Testing Tools.

DataDriven Unit Test using Excel in VS2013

2 Answers2

Option 1 - remove DSN from Connection String

Option 2 - Install ODBC Drivers for Opposite Bitness