Forum Discussion

atn3dxd's avatar
atn3dxd
Occasional Contributor
2 years ago

Using Excel Sheet for Data Driven Loop with Empty Cells

I am really new to using Test Complete and I have created a data loop using Excel to iterate through a bunch of rows of data.  It works fine except for when the field in the excel sheet is empty which in some cases it will be empty for the street prefix of an address.  The problem is the test fails when it hits the field in the Excel sheet that is empty for the street prefix.  I have looked around in the help and played with the data set but have not been able to find a way for the loop to just enter nothing if the field in Excel is empty and then move on to the next field.  Any help would be greatly appreciated.

Thanks

  • Marsha_R's avatar
    Marsha_R
    2 years ago

    The browser sections under the If and under the Then should be able to be indented. That's how TC looks for the code. Try just indenting "browser" in each case and the rest should move with it.

     

     

  • sgoulet's avatar
    sgoulet
    Contributor
    2 years ago

    WHat kind of test is this? Keyword? Script? What error are you receiving? 

  • atn3dxd's avatar
    atn3dxd
    Occasional Contributor
    2 years ago

    It is a keyword test, and the data is withing a data driven loop.  The error is "Type Mismatch".  Anytime there is a value in the field in Excel it works fine but if the field is empty the test fails at that point and then shows the error "Type Mismatch".  The whole row is formatted as "text" in Excel. I did test just putting a space in the empty field and then it enters a space in the field on the webpage, but I really do not want that to be the way it works.

    • Marsha_R's avatar
      Marsha_R
      Moderator
      2 years ago

      I encountered that issue a long time ago. The fastest workaround in Excel that I found was similar to yours. We actually put a character in the blank fields and used a code snippet to say if the field was not = "!" then use the field contents. 

       

      We got tired of messing with that after awhile and switched to using .csv files instead of Excel files. Excel can be unpredictable with the data and you can spend a lot of time doing workarounds.

      • atn3dxd's avatar
        atn3dxd
        Occasional Contributor
        2 years ago

        So, are the CSV files still edited using Excel but saved as CSV file?  I was messing with the IF-THEN and Else but cannot seem to get them to work for the ELSE part.  It says that is it in the wrong location but I have tried to move it and I think it really needs to be indented more for it to work in the location I need it but the application does not let me indent it anymore, so it is under the correct grouping.  

         

        I am new to this and not very familiar with scripting as I am just learning so how would I do a script and then get it into the test complete code?  Sorry again I am just starting with this process.  LOL

  • atn3dxd's avatar
    atn3dxd
    Occasional Contributor
    2 years ago

    Not sure where the code snippet is as I do not see it in the menu in Test Complete. I was trying an If-Then with this, but it does not like it and shows an error for an unexpected token error.  I am sure my format is way off.  The idea was if the value is not a "!" then the test should use the value in that field.  

     

    if (ODPU_data.value("StreetPfx") <> "!")
    then (ODPU.Variables.ODPU_data("StreetPfx"))

     

    Thanks

    • Marsha_R's avatar
      Marsha_R
      Moderator
      2 years ago

      Here's where to find Code Snippet

       

      for your code, I think it would be something closer to

      if (ODPU_data.value("StreetPfx") <> "!"

      then (ODPU.Variables.ODPU_data = ODPU_data.value("StreetPfx")

       

      You could do this with a standard If Then, you wouldn't necessarily need a script.

       

  • atn3dxd's avatar
    atn3dxd
    Occasional Contributor
    2 years ago

    First thanks for your help with this.😀

    I tried the code snippet and could not seem to get the syntax correct for the code.  I tried a bunch of stuff but it never liked the syntax even when it looked something like what you sent me.  

     

    So I now have this If-Then and Else setup but I do not think they are in the correct place and it will not let me indent any further into the lines.  Basically the idea is if the column from the spreadsheet <> "!" then use the value in the column of the spreadsheet else move to the next field and move in from there.

     

    Here is a screenshot:

     

    Thanks again

     

    • Marsha_R's avatar
      Marsha_R
      Moderator
      2 years ago

      The browser sections under the If and under the Then should be able to be indented. That's how TC looks for the code. Try just indenting "browser" in each case and the rest should move with it.

       

       

      • atn3dxd's avatar
        atn3dxd
        Occasional Contributor
        2 years ago

        We finally got it to work.  The IF-Then was a little weird to setup when using the little wizard, they have.  Initially I could not get it to go to the ELSE part.  Somehow, I was able to modify the IF-Then, so it looks like this.  The column name was not in the Value part for the If-Then check so it was never getting to the ELSE part.

         

         

        Thanks again for all your help with this.