5 Replies Latest reply on Aug 7, 2014 10:47 PM by Francisco Amores

    Import script FDM - remove currency symbol on amount field

    2727852

      I have a few tab delimited source files with those €, £, US$ in front of the value in the amount field.  I've been doing Edit/Replace for a while but there is surely a way to discard or remove those at the import.  I've tried a few thing in terms of import script.  I only want to know what would be the easiest way to get only the numeric value in the amount field?

       

      Many thanks!  Patrick

        • 1. Re: Import script FDM - remove currency symbol on amount field
          Francisco Amores

          Hi,

           

          FDM or FDMEE?

          • FDM

               You can use Mid or Right+Len functions

               amt = Right(amt,Len(amt)-1)

               or

               sign = "$"

               amt = Mid(amt,len(sign)+1)

           

               You may need to convert amt to number

          • FDMEE: there are different ways of doing that in jython. You can use lstrip function

               "$1200".lstrip("$")

               >>> 1200

           

          I hope that helps.

           

          Regards

          • 2. Re: Import script FDM - remove currency symbol on amount field
            2727852

            Hi Francisco,

             

            Many thanks for taking the time to answer.

             

            [TC] - [Amount=NN]  92-050-050-100010 HSBC France-05110023975 €452,340.54 €105,826.58 €71,172.89 €34,653.69 486,994.23

             

            From the text file, field 8 has to be numeric for the import to occur.

             

            Upon your suggestion, I scripted it this way without success.

             

            Function PEER1Amount(strField, strRecord)
            '------------------------------------------------------------------
            'Oracle Hyperion FDM DataPump Import Script:
            '
            'Created By: pfrancoeur
            'Date Created: 8/6/2014 2:00:12 PM
            '
            'Purpose:      
            '              
            '------------------------------------------------------------------
            Dim StrPEER1Amount
            StrPEER1Amount=DW.Utilities.fParseString(strField,8,8," ")

            If Left(StrPEER1Amount,1) = "€" Then
                 StrPEER1Amount = Right(StrPEER1Amount,Len(StrPEER1Amount)-1)
            Else
                 StrPEER1Amount = strField
            End If

            End Function

             

            I'm surely close to getting it solved.  Any suggestions would be welcomed!

             

            Thanks again!

             

            Patrick

            • 3. Re: Import script FDM - remove currency symbol on amount field
              Francisco Amores

              Hi,

               

              some points:

               

              - This line is not correct StrPEER1Amount=DW.Utilities.fParseString(strField,8,8," "), you should be using strRecord

              - In any case, if you assign that import script to FIELD 8, you don't need to use parse function as your value of column 8 is already in strField

              - In VBS, when you return a value from a function you need to use the same function name. In that function you are not returning anything

              - As you are returning an amount you should apply a cast when returning the value

               

              I hope you got concepts clear

               

              Regards

               

              Function PEER1Amount(strField, strRecord)

              '------------------------------------------------------------------

              'Oracle Hyperion FDM DataPump Import Script:

              '

              'Created By: pfrancoeur

              'Date Created: 8/6/2014 2:00:12 PM

              '

              'Purpose:      

              '              

              '------------------------------------------------------------------

                Dim StrPEER1Amount

               

                'Not necessary if script assigned to FIELD 8

                'StrPEER1Amount=DW.Utilities.fParseString(strRecord,8,8," ")

               

                If Left(strField,1) = "€" Then

                    PEER1Amount = Right(strField,Len(strField)-1)

                Else

                    PEER1Amount = strField

                End If

               

                ' Return the value

                PEER1Amount = CDbl(PEER1Amount)

               

               

              End Function

              • 4. Re: Import script FDM - remove currency symbol on amount field
                2727852

                Hi,

                 

                Thanks for defining some concepts, it is very informative.

                 

                I applied the script and I getting this in the error log, which I never got:

                 

                ** Begin FDM Runtime Error Log Entry [2014-08-07-11:59:55] **

                -------------------------------------------------------------

                ERROR:

                Code............................................. 13

                Description...................................... Type mismatch: 'CDbl'

                At line: 18

                Procedure........................................ clsImpDataPump.fImportTextFile

                Component........................................ upsWObjectsDM

                Version.......................................... 1111

                Thread........................................... 6452

                 

                and this:

                 

                ** Begin FDM Runtime Error Log Entry [2014-08-07-11:59:55] **

                -------------------------------------------------------------

                ERROR:

                Code............................................. 13

                Description...................................... Type mismatch: 'CDbl'

                At line: 18

                Procedure........................................ clsImpProcessMgr.fLoadAndProcessFile

                Component........................................ upsWObjectsDM

                Version.......................................... 1111

                Thread........................................... 6452

                 

                Line 18 reference:

                 

                Function PEER1Amount(strField, strRecord)
                '------------------------------------------------------------------
                'Oracle Hyperion FDM DataPump Import Script:
                '
                'Created By: pfrancoeur
                'Date Created: 8/6/2014 2:00:12 PM
                '
                'Purpose:      
                '              
                '------------------------------------------------------------------
                  If Left(strField,1) = "€" Then
                      PEER1Amount = Right(strField,Len(strField)-1)
                  Else
                      PEER1Amount = strField
                  End If

                  ' Return the value
                  PEER1Amount = CDbl(PEER1Amount)

                End Function

                 

                Thanks for your help!

                 

                Patrick

                • 5. Re: Import script FDM - remove currency symbol on amount field
                  Francisco Amores

                  Hi

                  that error is because some line has a value in field 8 that cannot be converted to double

                  typically: header line, empty amount...

                   

                  check it and troubleshoot a little :-)