.xlsx Excel files as Data Source in Coded UI test

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
[TestMethod]
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.CSV", "|DataDirectory|\\data.csv", "data#csv", DataAccessMethod.Sequential), DeploymentItem("data.csv")]
  • Excel
[TestMethod]
[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")]
  • Test case in Team Foundation Server
[TestMethod]
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.TestCase", "http://vlm13261329:8080/tfs/DefaultCollection;Agile", "30", DataAccessMethod.Sequential)]
  • XML
[TestMethod]
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.XML", "|DataDirectory|\\data.xml", "Iterations", DataAccessMethod.Sequential), DeploymentItem("data.xml")]
  • SQL Express
[TestMethod]
[DataSource("System.Data.SqlClient", "Data Source=.\\sqlexpress;Initial Catalog=tempdb;Integrated Security=True", "Data", DataAccessMethod.Sequential)]

I know I can convert the “.xlsx” format into the old format “.xls”, but I rather have more options than less.Here is the Attributes:

Data Source Types and Attributes

  • Excel, “.xlsx” format
[TestMethod]
[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)]

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, 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s