Forum Discussion

salley's avatar
salley
Frequent Contributor
6 years ago

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

 

  • tristaanogre's avatar
    tristaanogre
    6 years ago

    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.

  • RUDOLF_BOTHMA's avatar
    RUDOLF_BOTHMA
    6 years ago

    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

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    6 years ago

    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.

  • RUDOLF_BOTHMA's avatar
    RUDOLF_BOTHMA
    Community Hero
    6 years ago

    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