This discussion is archived
14 Replies Latest reply: May 9, 2012 6:18 AM by 783124 RSS

Import script error using DW.Utilities.fParseString: Type mismatch: 'CLng'

783124 Newbie
Currently Being Moderated
Is it possible to use CLng in FDM Import Scripts to convert a String datatype to a Long datatype, so that it can be evaluated numerically? I am currently receiving the following error:

Error:
"Error: An error occurred importing the file.
Detail: Type mismatch: 'CLng'
At line: 13"


Script:
Function TBNetwork(strField, strRecord)

Dim lngA
lngA = CLng(DW.Utilities.fParseString (strRecord, 8, 3, ","))

If lngA > 62100 Then
TBNetwork = "Greater"
Else
TBNetwork = "Less"
End If

End Function


CSV data file:
Field1,Field2,Field3,Field4,Field5,Field6,Field7,Field8
101,B1000,62100,L0000,C00001,N401,IY002,"-10,000,000.00"
101,B1000,62100,L0000,C00002,N401,IY003,"-6,646,939.00"
101,B1000,62100,L0000,C00003,N401,IY004,"-55,100,000.00"

Edited by: JamesD on May 4, 2012 3:02 PM
  • 1. Re: Import Script Error: Type mismatch: 'CLng'
    SH Guru
    Currently Being Moderated
    Yes that is possible. Try casting the numeric value to long as well i.e. lngA > CLng(62100)

    Edited by: SH on May 3, 2012 1:19 PM
  • 2. Re: Import Script Error: Type mismatch: 'CLng'
    783124 Newbie
    Currently Being Moderated
    The same error occurs.

    Interestingly, the following works fine:


    Dim lngA
    lngA = CLng("62101")

    If lngA > 62100 Then
    TBNetwork = "Greater"
    Else
    TBNetwork = "Less"
    End If


    So there appears to be an issue with converting DW.Utilities.fParseString (strRecord, 8, 3, ",") to a Long. I have tried many different permutations to try and work around this. Any ideas?
  • 3. Re: Import Script Error: Type mismatch: 'CLng'
    SH Guru
    Currently Being Moderated
    Are you sure that there are valid numeric values in field 3 for all lines in your data file? If not this will cause the CLng error.
  • 4. Re: Import Script Error: Type mismatch: 'CLng'
    783124 Newbie
    Currently Being Moderated
    I am using the exact same data as given above (only three rows!)...
  • 5. Re: Import Script Error: Type mismatch: 'CLng'
    SH Guru
    Currently Being Moderated
    What about the header line?

    Try

    Function TBNetwork(strField, strRecord)

    Dim lngA
    lngA = DW.Utilities.fParseString (strRecord, 8, 3, ",")

    If IsNumeric(lngA) Then
    If CLng(lngA) > 62100 Then
    TBNetwork = "Greater"
    Else
    TBNetwork = "Less"
    End If
    Else
    RES.PblnSkip = True
    End If
    End Function
  • 6. Re: Import Script Error: Type mismatch: 'CLng'
    beyerch2 Expert
    Currently Being Moderated
    Valid values for the Long data type are integers from -2,147,483,648 to 2,147,483,647.

    I do believe the values you are passing in are technically not integers because of the .00 at the end of them.

    I'd be willing to bet if you drop the last 3 characters (assuming you always have a decimal point and two digits after), this would work better.
  • 7. Re: Import Script Error: Type mismatch: 'CLng'
    783124 Newbie
    Currently Being Moderated
    I am trying to convert the values from the third column (62100), so these values should be fine:

    lngA = DW.Utilities.fParseString (strRecord, 8, _3_, ",")
  • 8. Re: Import Script Error: Type mismatch: 'CLng'
    783124 Newbie
    Currently Being Moderated
    I have also run the following script, to see what value is being returned by strA:

    Script:
    Function TBNetwork(strField, strRecord)

    Dim fso, tf
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set tf = fso.CreateTextFile("D:\Testfile.txt", True)

    Dim strA
    strA = DW.Utilities.fParseString (strRecord, 8, 3, ",")

    tf.WriteLine(strA)
    tf.Close

    TBNetwork = strField

    End Function


    The value returned in the generated file "Testfile.txt" was 62100 (no quotation marks).
  • 9. Re: Import Script Error: Type mismatch: 'CLng'
    beyerch2 Expert
    Currently Being Moderated
    Oops, I missed that part, sorry!

    Other thought would be are there any blank rows at the end of the file? It hard to tell what exacdtly is in your data file as in the post you are showing a header row and it looks like there might be a blank row beneath. If it is trying to process on either the header row or the blank row, that wouldn't go so well.

    With that said, let's take a step back.

    Why are you even using CLng again?

    Since everything in VBScript is technically a variant, you should be able to do a direct comparison such as :
    If DW.Utilities.fParseString (strRecord, 8, 3, ",") > 62000 then  (ie. "62000")
       do A
    else
      do B
    end if
    Was that not working and if no, what was happening?

    (I thought you had posted that earlier and stated that it was not returning true but if you are looking at the 62000, "62000" should not be greater than 62000, but equal to. If you wanted to equate to true the condition would need to be >=, not >)
  • 10. Re: Import Script Error: Type mismatch: 'CLng'
    783124 Newbie
    Currently Being Moderated
    Hi beyerch2,

    I can confirm that there are no spaces in the CSV file, and that it loads to Essbase fine as long as I am not trying to convert a value from DW.Utilities.fParseString, which is unfortuntely exactly what I need to do!

    I have updated the script as follows:


    Script:
    Function TBNetwork(strField, strRecord)
    If DW.Utilities.fParseString (strRecord, 8, 3, ",") > 62100 Then
    TBNetwork = "Greater"
    Else
    TBNetwork = "Less"
    End If
    End Function


    This does not even run within the Script Builder window, and gives the following error:


    Error:
    Error: An error occurred running the script:
    13 - Type mismatch: '[string: ""]'
    At Line: 3


    However, if I modify the script as follows, it "runs" within the Script Builder window ok:


    Script:
    Function TBNetwork(strField, strRecord)
    If DW.Utilities.fParseString (strRecord, 8, 3, ",") > CLng(62100) Then
    TBNetwork = "Greater"
    Else
    TBNetwork = "Less"
    End If
    End Function


    And now the problem is that the result is always "Greater", even when I change the string to CLng(62111) (i.e. 62100 > 62111)... This isn't too surprising, since the function DW.Utilities.fParseString is outputting a string. But changing the script to the following doesn't help either!


    Script:
    Function TBNetwork(strField, strRecord)
    Dim strA
    strA = DW.Utilities.fParseString (strRecord, 8, 3, ",")
    If CLng(strA) > CLng(62100) Then
    TBNetwork = "Greater"
    Else
    TBNetwork = "Less"
    End If
    End Function


    Error:
    Error: An error occurred importing the file.
    Detail: Type mismatch: 'CLng'
    At line: 6
  • 11. Re: Import Script Error: Type mismatch: 'CLng'
    SH Guru
    Currently Being Moderated
    This error ...

    Error: An error occurred running the script:
    13 - Type mismatch: '[string: ""]'
    At Line: 3

    ... suggests that the value being returned by the parseStirng function is an empty string, did you try my suggested script from a few posts previous doing an IsNumeric check on the returned value before trying any casting??
  • 12. Re: Import Script Error: Type mismatch: 'CLng'
    783124 Newbie
    Currently Being Moderated
    Hi SH, apologies I completely missed this post. Great news - this has fixed the problem! Many thanks! :-)
  • 13. Re: Import Script Error: Type mismatch: 'CLng'
    beyerch2 Expert
    Currently Being Moderated
    Glad it's working now, but ..... if that fixed your problem, you are really processing more rows than you've stated repeatedly in the thread? I thought you printed out all of the read data and confirmed they were all numeric? Slightly confused here.
  • 14. Re: Import Script Error: Type mismatch: 'CLng'
    783124 Newbie
    Currently Being Moderated
    I have re-tested to double-check, and can confirm that the solution is correct. You should be able to reproduce the error as follows:


    CSV File:
    Field1,Field2,Field3,Field4,Field5,Field6,Field7,Field8
    101,B1000,62100,L0000,C00001,N401,IY002,"-10,000,000.00"
    101,B1000,62100,L0000,C00002,N401,IY003,"-6,646,939.00"
    101,B1000,62100,L0000,C00003,N401,IY004,"-55,100,000.00"


    Erroneous Script:
    Function TBNetwork(strField, strRecord)
    Dim strA
    strA = DW.Utilities.fParseString (strRecord, 8, 3, ",")
    'If IsNumeric(strA) Then
    If CLng(strA) > 62100 Then
    TBNetwork = "Greater"
    Else
    TBNetwork = "Less"
    End If
    'Else
    ' RES.PblnSkip = True
    'End If
    End Function


    Error:
    Error: An error occurred importing the file.
    Detail: Type mismatch: 'CLng'
    At Line: 16


    Working script:
    Function TBNetwork(strField, strRecord)
    Dim strA
    strA = DW.Utilities.fParseString (strRecord, 8, 3, ",")
    If IsNumeric(strA) Then
    If CLng(strA) > 62100 Then
    TBNetwork = "Greater"
    Else
    TBNetwork = "Less"
    End If
    Else
    RES.PblnSkip = True
    End If
    End Function

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points