Forum Discussion

sankar_k's avatar
sankar_k
Occasional Contributor
11 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 ava...
  • Colin_McCrae's avatar
    Colin_McCrae
    11 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)
11 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!