Forum Discussion

sankar_k's avatar
sankar_k
Occasional Contributor
10 years ago

Reading data from Excel using DDT

Hi There



I read data from Excel using DDT and it is working fine. I pass one SQL statement from Excel which is more than 350 character length. The statement is not fully read and the string available in TestComplete after reading is only 250 characters and rest is truncated. Could you please let me know is there any restriction in reading number of characters / length of a string from Excel File



Thanks



Sankar K
  • Colin_McCrae's avatar
    Colin_McCrae
    10 years ago
    What version of Excel?



    Really old versions (2003 and earlier I think) had a 255 character limit within a cell. Newer ones don't. (Well, they have a limit, but it's thousands of characters.)



    It could also be caused by the way the connection is made to Excel by TestComplete. First answer here explains it. Not sure how the TC DDT connection is established:



    http://superuser.com/questions/646825/255-max-character-size-in-excel-cell-when-cell-data-is-linked-fetch-from-externa



    Personally, I don't like huge strings of text in excel cells. I commonly pass in JSON blobs to my tests. I store them as text files and then store a link to the text file in the Excel data. Much easier to read ....



    (Obviously, my framework also has to be smart enough to know it's a file like so I use a prefix to flag it and the framework parses it ...)
  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)
    10 years ago
    Hi Sankar,



    Yes, it's an Excel limitation:

    http://support.microsoft.com/kb/189897



    To work around this, try setting the TypeGuessRows registry value to 0 in the following key:



    For .xls files:

    HKEY_LOCAL_MACHINE\SOFTWARE\\Microsoft\Jet\4.0\Engines\Excel



    For .xlsx files:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\<version>\Access Connectivity Engine\Engines\Excel



    where <version> is the Excel version number:

    12.0 = 2007

    14.0 = 2010

    15.0 = 2013



    On 64-bit Windows versions, it'll be under ...\SOFTWARE\Wow6432Node\...



    Let us know how it works out!
  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    10 years ago
    What version of Excel?



    Really old versions (2003 and earlier I think) had a 255 character limit within a cell. Newer ones don't. (Well, they have a limit, but it's thousands of characters.)



    It could also be caused by the way the connection is made to Excel by TestComplete. First answer here explains it. Not sure how the TC DDT connection is established:



    http://superuser.com/questions/646825/255-max-character-size-in-excel-cell-when-cell-data-is-linked-fetch-from-externa



    Personally, I don't like huge strings of text in excel cells. I commonly pass in JSON blobs to my tests. I store them as text files and then store a link to the text file in the Excel data. Much easier to read ....



    (Obviously, my framework also has to be smart enough to know it's a file like so I use a prefix to flag it and the framework parses it ...)
  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    10 years ago
    Well, it's the data connection driver that applies the limitation rather than Excel.



    Possibly easier to put the dummy data in the excel sheet (and make sure you test ignores the dummy row). Reason being, if you run it on a different PC you need to remember to apply the registry hack/fix. If you use dummy data, you can run it wherever you like and it doesn't matter if you forget to hack the registry.



    Both methods will work though.



    :)