14 Replies Latest reply: May 9, 2012 8:18 AM by 783124 RSS

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

    783124
      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
          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
            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
              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
                I am using the exact same data as given above (only three rows!)...
                • 5. Re: Import Script Error: Type mismatch: 'CLng'
                  SH
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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