Data driven coded UI test with excel file as a data source

Question

I built a coded UI test and I want to make it a data driven test , I want to get the data from an excel file ( Data.xls )located in the same directory with the project files, I used this line as shown in the MSDN site , but it didnt work , what changes should I do to the line ?

    [DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\Data.xls;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true", "Sheet1$", DataAccessMethod.Sequential), TestMethod]

thank you

Providing the error message you get will help us answer your question. — Eric Scherrer, Apr 24 '14 at 12:38
@EricScherrer The unit test adapter failed to connect to the data source or to read the data. — Adi, Apr 24 '14 at 12:51
Put the input file in the bin\Debug folder where the dll is. It should work. — kida, Apr 24 '14 at 14:16
See also this answer. It covers data driving in general, but with a focus on CSV files. See http://stackoverflow.com/a/25742114/546871 — AdrianHHH, Oct 11 '16 at 12:17

score 0 · Answer 1 · answered May 05 '14 at 17:28

Here's the line of code that I use to connect the DataSource to my tests:

[DataSource("System.Data.Odbc", "Dsn=Excel Files;dbq=|DataDirectory|\\Logins.xlsx;defaultdir=C:\\Data\\Logins.csv;driverid=1046;maxbuffersize=2048;pagetimeout=5", "Sheet1$", DataAccessMethod.Sequential), DeploymentItem("Logins.xlsx"), TestMethod]

Then in my code I used:

LoginParams.TextBoxInfo = TestContext.DataRow["ColumnHeader"].ToString();

However, I did set my excel spreadsheet as a Deployment item. I'm not sure if you've already done that.

score 0 · Answer 2 · answered Sep 15 '14 at 12:23

As a workaround create a new class, and add this data source. You can use the same UIMap, and same code, just add a new Coded UI Test. "Project"->"New CodedUI Test".

I have used same excel workbook but different sheets for my tests as below:

namespace Data_driven_testing
{
/// <summary>
/// Summary description for CodedUITest2
/// </summary>

[CodedUITest]

public class CodedUITest2
{
    public CodedUITest2()
    {
    }

    [DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=D:\\CodedUI\\Data_driven_testing\\Data_driven_testing\\Data.xls;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true", "Sheet2$", DataAccessMethod.Sequential), TestMethod]

    public void CodedUITestMethod1()
    {


        this.UIMap.RecordedMethod3Params.UITextBox1EditText = TestContext.DataRow["Input1"].ToString();
        this.UIMap.RecordedMethod3Params.UITextBox2EditText = TestContext.DataRow["Input2"].ToString();
        this.UIMap.RecordedMethod3();


        // To generate code for this test, select "Generate Code for Coded UI Test" from the shortcut menu and select one of the menu items.
        // For more information on generated code, see http://go.microsoft.com/fwlink/?LinkId=179463
    }



 namespace Data_driven_testing
 {
   /// <summary>
   /// Summary description for CodedUITest1
   /// </summary>
[CodedUITest]
public class CodedUITest1
{
    public CodedUITest1()
    {
    }

    //[TestMethod]
    [DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=D:\\CodedUI\\Data_driven_testing\\Data_driven_testing\\Data.xls;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true", "Sheet1$", DataAccessMethod.Sequential), TestMethod]


    public void CodedUITestMethod1()
    {
        this.UIMap.RecordedMethod1Params.UITextBox3Text = TestContext.DataRow["Input1"].ToString();
        this.UIMap.RecordedMethod1Params.UITextBox4EditText = TestContext.DataRow["Input1"].ToString();

        this.UIMap.RecordedMethod1();
        this.UIMap.RecordedMethod2Params.UITextBox5EditText = TestContext.DataRow["Input3"].ToString();
        this.UIMap.RecordedMethod2Params.UITextBox6EditText = TestContext.DataRow["Input4"].ToString();

        // this.UIMap.RecordedMethod2();


    }

score 0 · Answer 3 · answered Nov 18 '16 at 14:30

If you could have provided us with a stack trace, may be we could have given you the exact solution.

Still, seeing your comment I think, If you get a connection error, it's most likely due to the fact that you don't have the 2007 Office System Driver installed for the OLEDB provider.

You can download it from the following Microsoft link: http://www.microsoft.com/en-us/download/details.aspx?id=23734

Data driven coded UI test with excel file as a data source

3 Answers3