Excel Data Loop Missing Fields?

[scot1967]

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

Example:

[Marsha_R]

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]

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]

Sorry, that wasn't clear at all.

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

[scot1967]

It is only a simple data loop pulling data from an Excel file.  No code, nothing fancy.  'STL', Total Loads and the beginning date range do not show up in TestComplete when the data is viewed or looped through.

Data Driven Loop Code

Data Driven Loop Build

TestComplete Data Loop View

Excel Spreadsheet

[sonya_m]

Thank you for providing the details scot1967!

@Marsha_R , @cunderw , @Wamboo , any suggestions?

[cunderw · Accepted solution]

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();

[scot1967]

---

@cunderw wrote:

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();

---

Very interesting.  I will get with dev and see what they say about this.  Thanks!

[scot1967]

@cunderw    I just altered my spreadsheet and forced all the columns to text and TestComplete can now read all the data.    I think this may be the solution!  

Thanks!

[scot1967]

@cunderw I had to use com.  I did not know how to access the data using the method you posted.  It works now.  I had to assign the return value to a project variable because it gets lost when Excel["Quit"](); is called.  Odd.  Any ideas?

function ReadDataFromExcel(str_Sheet,int_Column,int_Row)
{
  var str_value;
  var Excel = Sys["OleObject"]("Excel.Application");
  Excel["Workbooks"]["Open"]("C:\\temp\\rLoadSummary.xlsx");
  
  var xlsSheet = Excel.ActiveWorkBook.WorkSheets.Item(str_Sheet);
  xlsSheet["Activate"]();
  
  str_value = Excel["Cells"](int_Row,int_Column);
  
  //Had to set this as a proj var because it does not return the value after Excel["Quit"]()

  Project["Variables"]["str_Excel_Value"] = str_value;

  Excel["Quit"](); 

  return str_value;
}