Forum Discussion

scot1967's avatar
scot1967
Frequent Contributor
5 years ago

Excel Data Loop Missing Fields?

My Excel Data Loop in TestComplete is not getting all of the data from the .xlsx spreadsheet.  Any ideas?

 

Example:

 

  • https://support.smartbear.com/testcomplete/docs/testing-with/data-driven/excel-storages.html

     

    For the data provider to treat spreadsheet data correctly, all data in each column (except the column name) needs to be the same type, for example, text only or numbers only. This is because the provider applies only one type to the whole column, so, if it contains data of several types, some values may be treated incorrectly.

     

    You might have to revert to a code based solution and connect to the excel sheet in a different way.

     

    You could try treating it like an ADO connection with:

     

    let ControlCn = ADO.CreateADOConnection();
    //create unique connection string with IMEX=1 to allow for mixed content columns
    ControlCn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='<path to excel sheet>';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
    ControlCn.LoginPrompt = false;
    ControlCn.Open();

     

  • Yeah, Excel isn't really good for the data if you're going to have blank fields.    :/

     

    My first choice would be to use csv instead of Excel.  If you can't do that, then the only way we found around it was to put a special character of some sort instead of the blank and then say (in whatever language you use):

     

    if excelfield = ".", then set datafield = ""

     

     

    • scot1967's avatar
      scot1967
      Frequent Contributor

      The problem is really that the blank fields highlighted should contain the data in the associated Excel fields.  The data it not being picked up in TestComplete.  In the image of the TestComplete viewer is overlayed on the associated Excel spreadsheet for reference.

      • Marsha_R's avatar
        Marsha_R
        Moderator

        Sorry, that wasn't clear at all.

         

        Let's see the code that's inside your data loop please.