Forum Discussion

missydr's avatar
missydr
Occasional Contributor
6 years ago
Solved

Running a query on Excel sheet coming back with no values

I'm trying to pull a specific value from an Excel sheet by using a query but I get back nothing in my log.  I've spent too much time already trying to get it to work. See the script below that I crea...
  • tristaanogre's avatar
    tristaanogre
    6 years ago

    Yes, basicly the problem is in your query.  

    myDriver.Value(0) should work so long as you actually have data to retrieve.  So, it's possible that your query is not returning anything due to the WHERE clauses.

     

    My GUESS is that the "Date" field, you're doing a where clause assuming it's a string... I'm betting it's NOT a string and that it's actually a date/time value in your execl spreadsheet so the string comparison won't work.  You should check the actual spreadsheet and format that column to string if you want to use that Where clause or, somehow, replace the date string you have with the appropriate date/time value.

missydr's avatar
missydr
Occasional Contributor

Marsha_R I had been breaking it down and was still getting errors. 

 

tristaanogre I had tried it as Log.Message(myDriver.Value(0)) and received an "Data type mismatch in criteria expression" error. So the way I had it was the only way I wasn't getting an error. It makes sense to me to put Value(0) but with the error I was getting....I was getting desperate! :)

 

I got it to finally work after commenting out "where [Date] = "6/30/2000" AND [Description] = "FLAT FEE"' and adding the loop. I don't need every amount just the amount on the specific date. Will TestComplete let me do that? Do I just need to break the query down more in my code to get the value I want?

tristaanogre's avatar
tristaanogre
Esteemed Contributor
6 years ago

Yes, basicly the problem is in your query.  

myDriver.Value(0) should work so long as you actually have data to retrieve.  So, it's possible that your query is not returning anything due to the WHERE clauses.

 

My GUESS is that the "Date" field, you're doing a where clause assuming it's a string... I'm betting it's NOT a string and that it's actually a date/time value in your execl spreadsheet so the string comparison won't work.  You should check the actual spreadsheet and format that column to string if you want to use that Where clause or, somehow, replace the date string you have with the appropriate date/time value.

  • missydr's avatar
    missydr
    Occasional Contributor
    6 years ago

    I was making it harder than it needed to be.....of course! Here's the working script.

     

    function ReadDataFromExcel()
    {
    var myDate = "5/31/2000";
    var myDesc = "FLAT FEE";
    var myDriver = DDT.ExcelDriver("C:\\IUL_Calc.xlsm", "Activity");

    do
    {
    myDriver.ADOCommandObject.CommandText = 'SELECT * FROM [Activity$]';

    if (aqConvert.DateTimeToStr(myDriver.Value("Date"))== myDate)
    {
    Log.Message(myDriver.Value("Date"));
    if((myDriver.Value("Description"))== myDesc)
    {
    Log.Message(myDriver.Value("Description"));
    Log.Message(aqConvert.CurrencyToStr(myDriver.Value("Amount")));
    break;
    }
    myDriver.Next();
    }
    }while (! myDriver.EOF())

    DDT.CloseDriver(myDriver.Name);
    }

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      6 years ago

      Technically speaking, since you're not filtering with a "WHERE", clause, you don't need the line setting the command text.  The command text you have is the default for the DDT.ExcelDriver.  But yes, what you've done basically does the filter after the fact.