How to use .xlsx Excel files as Data Source for Coded UI test?

Question

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.

• Peter M. - stands for Monica: 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: I know that "competent programming craft" is sorely missing in many questions asked here, but we should strive for higher standard, not lower.
• hxin: @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.
• FDM: @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.
• hxin: @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.

Answer 1

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.

• FDM: Mark this as the answer to your question. It can indeed be handy for future reference..
• hxin: Added a CSV problem in my answer which also bit me.
• AdrianHHH: 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.
• hxin: @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.