3

I want to use ".xlsx" format Excel file as Data Source in my hand coding Coded UI test. The best information I get is found in MSDN: Creating a Data-Driven Coded UI Test, which has useful information regarding of various data format as below, but it doesn't mention about the ".xlsx" format.

After some more googling and experiment, I finally managed to get it to work. In order to save time for me and someone who might face the same challenge again, I think it's beneficial to document the connection strings and some key points to pay attention to here as later reference.

The following are the data source strings found in the MSDN page:

Data Source Types and Attributes

  • CSV

    [DataSource("Microsoft.VisualStudio.TestTools.DataSource.CSV", "|DataDirectory|\\data.csv", "data#csv", DataAccessMethod.Sequential), DeploymentItem("data.csv"), TestMethod]

  • Excel

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

  • Test case in Team Foundation Server

    [DataSource("Microsoft.VisualStudio.TestTools.DataSource.TestCase", "http://vlm13261329:8080/tfs/DefaultCollection;Agile", "30", DataAccessMethod.Sequential), TestMethod]

  • XML

    [DataSource("Microsoft.VisualStudio.TestTools.DataSource.XML", "|DataDirectory|\\data.xml", "Iterations", DataAccessMethod.Sequential), DeploymentItem("data.xml"), TestMethod]

  • SQL Express

    [DataSource("System.Data.SqlClient", "Data Source=.\\sqlexpress;Initial Catalog=tempdb;Integrated Security=True", "Data", DataAccessMethod.Sequential), TestMethod]

I know I can convert the ".xlsx" format into the old format ".xls", but I rather have more options than less.

Improve this question
13
  • This site should answer real questions. No offense, but if you want to post solutions of your research, get a blog. I understand that it took you some time to figure it out, and many other posters spend even less time researching the problem than you did, and all this deserves praise. But what we want here is quality answers to quality relevant questions. All your post is just competent reading the docs and programming craft.
    – Peter M. - stands for Monica
    May 1, 2017 at 18:37
  • I know that "competent programming craft" is sorely missing in many questions asked here, but we should strive for higher standard, not lower.
    – Peter M. - stands for Monica
    May 1, 2017 at 18:37
  • @PeterMasiar Well, this may not be a real question/solution for people like you who I believe are more experienced and better skilled. However, this is a real question and a real solution for people like me. There are lots of posts out there which failed to work for me.
    – hxin
    May 1, 2017 at 19:44
  • 1
    @PeterMasiar, fair enough, but what if he asked the question and then found the answer two days later? That looks like a legit scenario for a Q&A and is essentially no different.
    – FDM
    May 1, 2017 at 19:47
  • @FDM Thank you. The working .xlsx Attributes I posted in my answer can be found nowhere. Despite I did search a solution for more than 2 days. MSDN doesn't mention anything about .xlsx format at all.
    – hxin
    May 1, 2017 at 20:01

1 Answer 1

Reset to default
2

Here is the Attributes:

Data Source Types and Attributes

  • Excel, ".xlsx" format

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

where "data.xlsx" is the Excel file you want to use as Data Source, "Sheet1$" is the worksheet you are using in the Excel file. Using this to replace the normal [TestMethod] attribute before the test method.

If this is not working, check the following:

  1. Add "data.xlsx" Excel file to your CodedUI Project:

    Right Click Project -> Add -> Existing Item... -> Add

  2. Check "data.xlsx" Properties in VS, make sure to set "Copy to Output Directory" Property to "Copy always" or "Copy if newer"

  3. If you get a connection error,

    ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    it's most likely due to the fact that you don't have the 2007 Office System Driver installed for the OLEDB provider. (Thanks to blog.danbrust.net and incyclesoftware), you can download it from the following Microsoft link:

    Link 2007 Office System Driver: Data Connectivity Components

    This will install the Office 2007 drivers. Since the Access and Excel file formats haven’t changed since 2007, these drivers work just fine with Office 2010, 2016, etc.

Another comment for CSV file: if CSV file is not working, check the encoding. It should be ANSI or UTF-8 without BOM.

Improve this answer
4
  • 1
    Mark this as the answer to your question. It can indeed be handy for future reference..
    – FDM
    May 1, 2017 at 18:36
  • Added a CSV problem in my answer which also bit me.
    – hxin
    May 1, 2017 at 19:08
  • See stackoverflow.com/a/25742114/546871 which is all about data driving with a CSV and it includes a link to an answer about BOMs and CSVs.
    – AdrianHHH
    May 2, 2017 at 20:37
  • @AdrianHHH Thanks for the info. We will most likely to stick with Excel with both .xls and .xlsx, because that's our business users have always been providing us with before we switched to CodedUI. I will be looking for ways to not only read source files but append/write/update them in run time as we did with UFT before. If you have information about that, it will be very helpful as well.
    – hxin
    May 2, 2017 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.