Ask a Question

Reading values from excel file

newbiexiii
New Contributor

Reading values from excel file

My AUT exports excel files which contains dates and strings. I am trying to read the exported excel file but dates are read as encoded.
For instance, the excel has a column named Date and date format is 8/25/2016 11:13:52 AM but when I try to read this value with Excel object, I get

_x0038__x002F_25_x002F_2016_x0020_11:13:52_x0020_AM

How can I decode this to get the correct date format?

 

Code used to open and read excel file

 

var sheet = Excel.Open(filename).SheetByIndex(0);

sheet.Cell(7,2).Value;

4 REPLIES 4
Marsha_R
Community Hero

This may be helpful:

https://support.smartbear.com/testcomplete/docs/testing-with/working-with-external-data-sources/exce...


Marsha_R
[Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame

This would not work for system which don't have Excel installed.

Hi @newbiexiii - 

 

Have you tried exporting the file to a .csv instead? It may have something to do with the formatting of the excel file.

 

Emma

chriscc
Contributor

Hi @newbiexiii.

 

Not sure what language you are coding, but the following JavaScript functions will show you how you can convert your date string.

 

The first is a generic function to convert hex codes to ascii characters. The second is specifically parsing your date string and then calling the hexToAscii function to get the ascii characters.  The third is just a test function.  You could combine them into one function if you wanted.

 

Have a look and let me know if this helps.

 

  function hexToAscii(hexString)
  {
    // create an empty string
    var ascii = "";
    for (var i = 2; i < hexString.length; i += 2)
    {
     // get the substring, convert to int using base 16, then get the ascii character
     ascii += String.fromCharCode(parseInt(hexString.substr(i, 2), 16));
    }
    return ascii;
  }

 

 

  function dateDecoder(excelValue)
  {
    // create a empty string to append and return
    var retAscii = "";
    // split the string on the underscore
    var arr = excelValue.split('_');
    // loop the array
    for (var i = 0; i < arr.length; i++)
    {
      // if 5 characters and starts with 'x' then convert the hexcode to ascii otherwise just append the item to asciiString
      if (arr[i].length == 5 && aqString.Find(arr[i], "x", 0, false) == 0)
        retAscii += hexToAscii(aqString.Replace(arr[i], "x", "", false));
     else
       retAscii += arr[i];
    }

    return retAscii;
  }

 

 

  function decoderTest()
  {

    BuiltIn.ShowMessage(dateDecoder("_x0038__x002F_25_x002F_2016_x0020_11:13:52_x0020_AM"));
  }

Chris
cancel
Showing results for 
Search instead for 
Did you mean: