Forum Discussion

scot1967's avatar
scot1967
Frequent Contributor
5 years ago
Solved

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:  
  • cunderw's avatar
    cunderw
    5 years ago

    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's avatar
scot1967
Frequent Contributor
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's avatar
scot1967
Frequent Contributor
5 years ago

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

 

Thanks!