Forum Discussion

missydr's avatar
missydr
Occasional Contributor
7 years ago

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 created. Any help will be much appreciated.

 

function ReadDataFromExcel()
{
var myDriver = DDT.ExcelDriver("C:\\IUL_Calc.xlsm", "Activity");
myDriver.ADOCommandObject.CommandText = 'SELECT [Amount] FROM [Activity$] where [Date] = "6/30/2000" AND [Description] = "FLAT FEE"';

Log.Message(myDriver);

DDT.CloseDriver(myDriver.Name);
}

 

Missy

  • tristaanogre's avatar
    tristaanogre
    7 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.

  • Marsha_R's avatar
    Marsha_R
    Moderator
    7 years ago

    Break it down in pieces to troubleshoot

     

    Try just using "FLAT FEE" and leave off the date.  If you get data then, you'll know it's the date.

    If you don't get any data even then, try taking the where clause off and just get Amount somehow.  

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    7 years ago

    Well, for one thing, Log.Message(myDriver) won't return your amount value.  Logging the object won't give you the values in the column.

     

    Try, instead, Log.Message(myDriver.Value(0))  or Log.Message(myDriver.Value('Amount')).  you need to actually specify what you want.

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      7 years ago

      Note, also, the code as you have it will only return the first value.  To get the AMOUNT value for every row in your result, you need to build a "while" loop to loop until mydriver.EOF() is no longer false.  

      • missydr's avatar
        missydr
        Occasional Contributor
        7 years ago

        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?