Forum Discussion

chicks's avatar
chicks
Regular Contributor
14 years ago

Excel column returns null for non-numeric characters

Anybody seen anything like this or have any comments?



short version

=========

When I tried to use aqConvertVarToStr on values in an excel column that had non-numeric characters, I got either a null value or an empty string

back  (not sure which).



I created a new excel column, copied the data cells from the old column and the values were read in properly.

When I copied the failing column to a new column, the error continued.



long version

=========

I was trying to read a zipCode.  zipCodes with the 5 digit format without a dash e.g.  75081 were being read successfully.

Zip codes with the extended format e.g. 75081-0012 were coming in as null or the empty string.

The cells are of 'general' format.   I discovered that it was any non-numeric character that caused a null value.

I wasn't sure whether it was the aqConvert or the dataDriver.Value that was coming in wrong.

Apparently it is the dataDriver.value that shows up as empty when the problem is happening. 

I eventually realized that I had  other columns with non-numeric values that were working properly.



Here's some code:



function debugZipCode () {



 // zip code values with a - included are being read as empty strings

 // appears to be column specific.  If I create a new column values are

 // read properly.  If I copy the bad column, values are no longer read properly



Log.Message("debugZipCode");

    Driver = DDT.ExcelDriver(excelData, signupSheet, true);

    

    var omit;

    var aURL;

   

 while (! Driver.EOF() ) {

  omit = aqConvert.VarToStr(Driver.Value(omitHeader));

  // skip row if there's anything other than whitespace SignupWithConfirmationin the omit column

  if ( /\S+/.test(omit) == false) {

    // which environment

Log.Message("raw is "+Driver.Value(websiteHeader) ) ;

    aURL = aqConvert.VarToStr(Driver.Value(websiteHeader));

Log.Message("aURL is "+aURL);

 

Log.Message("raw is "+Driver.Value(zipcodeHeader) ) ;

    aZipCode = aqConvert.VarToStr(Driver.Value(zipcodeHeader));

    Log.Message("zip is "+aZipCode);

  

   }

   Driver.Next();

 }

 DDT.CloseDriver(Driver.Name);

}





environment

========

Windows 7,  Excel 2010,  TestComplete 8.50.626.7
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    The problem you're dealing with is that the driver being used for reading in values tries to "best guess" what the formatting of the ENTIRE column is to determine how to read it.



    so, if the first few values in the column are numeric only, it assumes the whole column is numeric and, therefore, when it finds a non-numeric value, returns NULL.  Same with the other way around.



    What you need to do is make sure you force the proper formatting for all values in that column.  So, since you are doing ZIPCodes which may have the +4 value attached, you're column should be formatted as text.  Using Excel to format won't work.  What you need to do is prefix the values in your cells with the apostrophe character (') to force Excel to treat it as text, even for your purely numeric values.



    This, to be honest, is a good rule of thumb when reading values from either Excel or from CSV when you're using the DDT drivers.  Force all your values in your table to text only and then use conversion routines within your code to convert to integer or what not.  For Excel, that means using the apostrophe when entering numeric or boolean values.  For CSV, that means wrapping all data in double quotes (").
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    no... you need to add the preceding apostrophe to all numeric fields.  There is no succeeding apostrophe.



    In Excel, preceding a numeric value with the apostrophe character tells excel to treat that field as text instead of numeric.  You need to do this for all values in your zip-code column that are pure numeric values.



    so, if a cell has the value of 12345, put an apostrophe before it (literally, '12345)

    If a cell has the value of 12345-1234, no need to do anything because Excel is "smart" and knows that's not a number.



    For relevant reading, here's a quote from the TC help documentation (http://smartbear.com/support/viewarticle/11916/):

    In order 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 data may be treated incorrectly.

    • vx's avatar
      vx
      Occasional Contributor
      8 years ago

      Thank you, it worked.

  • Martin_Blass's avatar
    Martin_Blass
    New Contributor
    8 years ago

    Thanks!

    I was struggling a bit with it...

     

    Just a hint for others - If you have no column names you still have to fill in something for the name in the Schema.ini file. In particular you have to use letter "F" and the column number...

     

    e.g.

    [parameter_sets.csv]
    ColNameHeader=True
    Format=Delimited(;)
    Col1=F1 Text
    Col2=F2 Text
    Col3=F3 Text
    Col4=F4 Text
    Col5=F5 Text
    Col6=F6 Text
    Col7=F7 Text
    Col8=F8 Text

  • chicks's avatar
    chicks
    Regular Contributor
    14 years ago
    Hi Robert,



    Thanks for your response.   The behavior I'm seeing does not match my understanding of your reply.



    If the driver is assuming that the value is numeric based on the first few characters and then returning a null when it hits

    a non-numeric character, I would assume that this happens consistently.  In other words, a cell value starting with numeric

    characters and then including a non-numeric character would NEVER be read as other than null.

    However, I am successfully reading the mixed character case after creating a brand new column and using the same sequence

    of characters that failed previously.



    Also, I tried  preceding the value with an apostrophe and still got a NULL response.  As well as changing the formatting from 'general'

    to 'text'.



    What am I missing? Thanks.  Curt  




  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    You need to format ALL values in the column that way.  It doesn't do anything to set one a certain way and expect them all to be treated as such.  Either the column contains all numbers or the column contains all strings.  As mentioned, the best default I've found is to force everything in the column to strings by prefacing everywhere where necessary with the (') character.



    And, while it's not random, it is rather dependant upon what the first cell value is that you read in the driver or that the driver encounters.  So, sometimes it will work well, sometimes it won't, all depending upon how you're operating with the excel file and such.



     BTW, this is one reason why I avoid Excel for use in DDT drivers because it's rather unpredictable.  CSV's while they can have the same problem are much easier to correct by just putting a schema.ini file in the directory with the CSV file where you indicate the column format.  Then you don't have to worry about formatting the data itself, the schema.ini takes care of all of that for you.
  • chicks's avatar
    chicks
    Regular Contributor
    14 years ago


    I'll need to strip off the preceding apostrophe correct?  Do I need a terminating apostrophe?
  • chicks's avatar
    chicks
    Regular Contributor
    14 years ago
    When I'm getting the error, putting the apostrophe before the values does not fix the problem.

    I'm only checking two rows at a time. 



    Generally, I'm treating everything as text and using vartostr, though it sounds like either the driver or excel is trying to be 'smart'.



    Can you tell me more or direct me to something regarding your ini files, please?  I've  worked with CSV files before and ended up

    editing them in excel and then saving as CSV because editing the raw CSV was kind of tedious. 



    Thanks very much for your advice.



    Regards.  Curt









































    omit website participant loyaltyCard signupType nameOrPin zipCode
    https://qafuelrewards.efuelrewards.com/cppublic/fuelperks/ QA Corner Stores Issue Redeem 1509 3 Test 75001//:867
    https://qafuelrewards.efuelrewards.com/cppublic/fuelperks/ QA Corner Stores Issue Redeem 1510 3 Test 75001






  • Martin_Blass's avatar
    Martin_Blass
    New Contributor
    8 years ago

    Good morning everybody,

     

    I suffer from a simillar problem. I have columns of HEX values which are separated by coma and a space (e.g. "53, 0F"). The columns themselves are separated by semicolon (CSV file). 

    It all works fine until I reach cells with values such as "00, 01" in which case TestComplete interprets this as "0". I have tried using different text "separators" and trying to "force" text format by using apostrophe at the begining of the cell...

     

    At best I can get the values out in this format: '00, 01' (including the apostrophes)

     

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      8 years ago

      AS noted earlier on in the thread, the best solution is to use some sort of schema.ini for a CSV file to indicate the column format so that the database driver does not auto-convert it.