locked
Does anyone know the DataSource string for new excel (for test data) RRS feed

  • Question

  • I have looked the internet up and down for the answer and all I get are older samples for previous versions of excel. Even the MS site still behind.

    I'm using excel 2016. Using VS 2015 for development.

    Have use the following for test data from excel on my test methods, but keeps getting errors such as:

    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 [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

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

    I have also used this one:

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

    I have the using System.Data.Odbc on my class.

    My test data file is at: c:\testdatda\findandselect.xlsl

    Any feedback is appreciated.


    NR


    • Edited by Highlander4 Sunday, January 15, 2017 1:38 AM
    Saturday, January 14, 2017 2:08 AM

Answers

  • I found the solution to my problem, not exactly what I wanted, but here it is. The explanation is out there sort of in a convoluted way.

    First, I needed the latest data access dll for latest MS office (in my case office 2016). You need the oledb version 12  for x64).

    After installing oledb 12, I kept getting: OLEDB Provider is Not Registered on the Local Machine .

    What this problem turnout to be is that the version of MS Office I had was a 32 bit Office install. Bummer, I though all along that it was 64 bit. Microsoft doesn't make it easy for you to verify the Office version, there is no About information.  I only realized this the third time I tried to install the oledb driver when it told me that I couldn't install because I had a 32 bit version of office. Don't know why it didn't said so the first time I installed it.

    I then proceeded to get the same driver for x86 and installed it.

    Then I had to go to my solution project/ properties in VS 2015 and in Build tab, change the targeted platform to x86. After doing so and then using the string below(found in InCycle) on my CUIT methods, everything worked:

    [TestMethod, DataSource("System.Data.OleDb", "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=|DataDirectory|\\c:\\TestData\\FindAndSelect.xlsx;" + "Persist Security Info=False;" + "Extended Properties='Excel 12.0 Xml;HDR=YES'", "Sheet1$", DataAccessMethod.Sequential)]

    Wished Microsoft did a better job at explaining these strings rather than relying in the community to do their documentation.  I would like to use the same string above without the + signs, but don't know how to go about it.  I expect to change back to x64 once my company starts using Office x64.


    NR




    • Marked as answer by Highlander4 Sunday, January 15, 2017 9:05 PM
    • Edited by Highlander4 Sunday, January 15, 2017 9:07 PM
    Sunday, January 15, 2017 9:02 PM