DDT.ExcelDriver "Unexpected error"

[Warren_Sealock]

I'm having some difficulty with the DDT.ExcelDriver, and I'm wondering if anyone has run across this before.  Essentially my task is to read in some data from app generated Excel files (XLS and XLSX), I can then use that data for some further testing.  The problem is that on certain XLS files the DDT.ExcelDriver throws an odd error: "Unexpected error from external database driver (22)".



Here is the (JScript) code that does it, all it's doing is reading data from the left-most column of the Excel sheet:



function GetLookupExcelData(varFilePath, varSheetName)

{

    var outarray = [];

    DDT.ExcelDriver(varFilePath, varSheetName, true);

    while (!DDT.CurrentDriver.EOF()) 

    {

      var value = DDT.CurrentDriver.Value(0)

      if (value) outarray.push(value);

      DDT.CurrentDriver.Next();

    }

    DDT.CloseDriver(DDT.CurrentDriver.Name);

    return outarray;   

}



The error happens on the DDT.ExcelDriver(varFilePaqth, varSheetName, true) line.  Note: this ONLY happens with XLS files (so far, but not every XLS file), if I have the same data in a XLSX file I don't get an error at all.  Also note on the DDT.ExcelDriver calls, I've tried without the "true" parameter when using XLS files, and I still get the same result (i.e., with and without ACE driver support).  I'm using TestComplete 9.10.1894.7.



I will try to attach two files (XLS and XLSX), both containing the same data, that demonstrates the issue.

[mugheessiddiqui]

Hi Warren



It's late reply but finding the cause and documenting the solution for the others.



In XLS file if worksheet name has spaces in it, you'll get the exception "Unexpected error from external database driver (22)".

Rename worksheet in xls document by replacing spaces with "_" and run the following code, it will work fine.

function test2()

{

GetLookupExcelData("script\\tctemp.xls", "Rotating_Equipment_Capacity_Uni"); // working absolutely fine

 

GetLookupExcelData("script\\tctemp.xls", "Rotating Equipment Capacity Uni"); //Sheet name with space will throw exception "Unexpected error from external database driver (22)".

 

}

function GetLookupExcelData(varFilePath, varSheetName)

{

var outarray = [];

DDT.ExcelDriver(varFilePath, varSheetName, false);

while (!DDT.CurrentDriver.EOF())

{

var value = DDT.CurrentDriver.Value(0)

if (value) outarray.push(value);

DDT.CurrentDriver.Next();

}

DDT.CloseDriver(DDT.CurrentDriver.Name);

return outarray;

}

[bruno_gianello]

I´m having the same problema with a Data Driven Loop, but I already cleaned up my xls file of all special characters and spaces everywhere. I´m still having the message:

---------------------------

TestComplete

---------------------------

Erro inesperado causado pelo drivers de banco de dados externo (22)

---------------------------

OK   

---------------------------

(It's in portuguese, but the same thing)

Do I run the script anyway? If so, could some1 please explain to me how?

Thank you guys very much 😄

[Marsha_R]

I don't think the spaces are causing the issue because all our DDT .xls files have sheets with spaces in the name and we don't see that error message.



We have occasionally had issues with .xls and .xlsx where the sheet becomes corrupt and you can't see why.  Try creating an new sheet with some of the data from one of the sheets that doesn't work and see if that will run.  Don't copy/paste the data, but type it in manually.  

[sauerkraut]

Bruno,



Eu já tive esse problema.

Se não me engano, o seu arquivo deve estar com atributo somente leitura.



Deixe ele editável. Acredito que vai resolver.



Abraço.

[felipefigueired]

Tive o mesmo problema recentemente.

Resolvi desinstalando as ultimas atualizações do Windows.

Windows 7 (KB4041681 and KB4040685)

Windows 8 (KB4041693 and KB4041685).

[PAULO]

Hello, You have to save the files as excel workbook. When you work with two different files excel sends this error. I had the same problem and changed the type and solved my problem.

---

@Warren_Sealock wrote:
I'm having some difficulty with the DDT.ExcelDriver, and I'm wondering if anyone has run across this before.  Essentially my task is to read in some data from app generated Excel files (XLS and XLSX), I can then use that data for some further testing.  The problem is that on certain XLS files the DDT.ExcelDriver throws an odd error: "Unexpected error from external database driver (22)".



Here is the (JScript) code that does it, all it's doing is reading data from the left-most column of the Excel sheet:



function GetLookupExcelData(varFilePath, varSheetName)

{

    var outarray = [];

    DDT.ExcelDriver(varFilePath, varSheetName, true);

    while (!DDT.CurrentDriver.EOF()) 

    {

      var value = DDT.CurrentDriver.Value(0)

      if (value) outarray.push(value);

      DDT.CurrentDriver.Next();

    }

    DDT.CloseDriver(DDT.CurrentDriver.Name);

    return outarray;   

}



The error happens on the DDT.ExcelDriver(varFilePaqth, varSheetName, true) line.  Note: this ONLY happens with XLS files (so far, but not every XLS file), if I have the same data in a XLSX file I don't get an error at all.  Also note on the DDT.ExcelDriver calls, I've tried without the "true" parameter when using XLS files, and I still get the same result (i.e., with and without ACE driver support).  I'm using TestComplete 9.10.1894.7.



I will try to attach two files (XLS and XLSX), both containing the same data, that demonstrates the issue.

---