cancel
Showing results for 
Search instead for 
Did you mean: 

Returning Null when trying to read data from Excel using DDT driver

SOLVED
Frequent Contributor

Returning Null when trying to read data from Excel using DDT driver

Hi,

I'm using DDT driver to read data from the excel. i've two column in Excel , sayg COL1=100,200 and COL2=100000,200000. For somereason COL2 is returning me Null Values, but when i changed the DataType in EXCEL as Text, it's working Fine. or is there something i'm doing is wrong, please see my below script

********MainScript

Function Main()
Set Driver = DDT.ExcelDriver(ReadInputFile,"TestData",True)
While Not DDT.CurrentDriver.EOF
strRunFlag=Trim(DDT.CurrentDriver.Value("Run_Flag"))
sItem=Trim(DDT.CurrentDriver.Value("Col_A"))
sAmount=Trim(DDT.CurrentDriver.Value("Col_B"))
If strRunFlag<>"N" Then

'Calling the PriceCart Function
PriceCart sItem,sAmount
End If
DDT.CurrentDriver.Next()
Wend
End Function

************PriceCartFunction

Function PriceCart(strItem,strAmt)
arrItem=Split(strItem,":")
arrAmt=Split(strAmt,":")
For i = 0 To UBound(arrItem)
itemVal=arrItem(i)
ItemAmount=arrAmt(i)
log.Message "Item: "&itemVal& " :Amount: "&ItemAmount
Next
End Function

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Hero

Re: Returning Null when trying to read data from Excel using DDT driver

100,200 is a valid integer (one hundred thousand two hundred)

100000,200000 is not.  The DDT driver tries to match the data in the column to the next best data type.... if you're not designating it as text, I'm guessing it's TRYING to read it as integer... and since it's an invalid INT value due to the comma placement, it's failing.

 

My thought is that you SHOULD be making these columns "Text" value anyways.  You're treating them as strings to split around a separator character so you should attempt to force the data to be read as a string.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Community Leader

Re: Returning Null when trying to read data from Excel using DDT driver

If, as @tristaanogre suggests, you want to force excel to be text, you could add a single quote to the value in the cell formula. e.g. '10000,20000 rather than 10000,20000.  This forces excel to report the value as a string.  If you just change the cell format to be a "general" or "text", this translation sometimes turns out wrong.  eg. saving "0001" changes the value to "1".  If you do indeed want to work with integers, you should probably still consider storing them as string and in TC use

aqConvert.VarToInt(Trim(DDT.CurrentDriver.Value("Col_A")))

or something similar


-------------------------------------------------
Standard syntax disclaimers apply
Regards,
2 REPLIES 2
Community Hero

Re: Returning Null when trying to read data from Excel using DDT driver

100,200 is a valid integer (one hundred thousand two hundred)

100000,200000 is not.  The DDT driver tries to match the data in the column to the next best data type.... if you're not designating it as text, I'm guessing it's TRYING to read it as integer... and since it's an invalid INT value due to the comma placement, it's failing.

 

My thought is that you SHOULD be making these columns "Text" value anyways.  You're treating them as strings to split around a separator character so you should attempt to force the data to be read as a string.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Community Leader

Re: Returning Null when trying to read data from Excel using DDT driver

If, as @tristaanogre suggests, you want to force excel to be text, you could add a single quote to the value in the cell formula. e.g. '10000,20000 rather than 10000,20000.  This forces excel to report the value as a string.  If you just change the cell format to be a "general" or "text", this translation sometimes turns out wrong.  eg. saving "0001" changes the value to "1".  If you do indeed want to work with integers, you should probably still consider storing them as string and in TC use

aqConvert.VarToInt(Trim(DDT.CurrentDriver.Value("Col_A")))

or something similar


-------------------------------------------------
Standard syntax disclaimers apply
Regards,
New Here?
Join us and watch the welcome video:
API Testing Mistake #2
APITestingMistake#2
Top Kudoed Authors