3 Replies Latest reply: Feb 19, 2013 10:20 AM by 847750 RSS

    BefImportMap Issue

    847750
      Hi,

      We have a map file with 5 columns of which the last 2 cols are src and target.

      We need to skip the first 3 cols and load the last 2 columns.I am trying to use BefImportMap logic to get this.

      But, after we write the logic and try to load file, we are getting automation error.


      Below is the logic which we used:

      Sub BefImportMap(strLoc, strType, strFile)
      '------------------------------------------------------------------
      'Oracle Hyperion FDM EVENT Script:
      '
      'Created By:      PGSFDMAdmin
      'Date Created:      2013-02-15 06:22:19
      '
      'Purpose:
      '
      '------------------------------------------------------------------

      Dim strline
      Dim fso
      Dim f1
      Dim f2
      Dim strNewFileName
      Dim strSource
      Dim strTarget
      Dim strFile

      Set f1 = fso.OpenTextFile (strFile,1)

      strNewFileName = strFile & "Mod"
      Set f2 = fso.CreateTextFile (strNewFileName,True)

      Do While f1.AtEndofStream <> True

      strline = f1.ReadLine

      strSource = DW.Utilities.fParseString(strline,5,4,",")

      strTarget = DW.Utilities.fParseString(strline,5,5,",")

      f2.writeline strSource & "," & strTarget

      Loop

      f1.Close
      f2.Close

      Set fso=Nothing

      End Sub

      Can anyone let us know what is that we are missing here..

      Thanks in advance
        • 1. Re: BefImportMap Issue
          beyerch2
          I see the following issues:

          - On line 8, you declare a variable called strFile. This will generate an error as one of the parameters for the sub is called strFile. You cannot declare a variable that already exists.

          - strNewFileName = strFile & "Mod" : This probably will 'work', but if the source file is "Invoice1.dat", you'll get "Invoice1.datMod". I think you're probably wanting something like "Invoice1Mod.dat" You can use Instr or Split to find the file extension. If you like your FDM method calls, you could even use fparsestring as it is just a wrapper for split most likely...

          - fso : This is NEVER defined; therefore, you will have issues with lines 10 & 13. (i.e. Set fso = CreateObject("Scripting.FileSystemObject") )

          - f2.writeline strSource & "," & strTarget : What happens if the source or target values has a comma in it? I would wrap both the Source and Target in quotes so that it doesn't break your file.

          Hope that helps.
          • 2. Re: BefImportMap Issue
            847750
            Hi,

            Thanks for your suggestions.I have modified my script with your inputs and now I don't get error any more.

            But the purpose of using the script is not achieved.

            I want cols 4 & 5 to be seen in Map, but even after implementing the script, I get col 1, col 2 & col3 as src,tar & desc column.

            Below is the revised logic:
            -----------------------------------

            Dim strline
            Dim fso
            Dim f1
            Dim f2
            Dim strNewFileName
            Dim strSource
            Dim strTarget

            Set fso = CreateObject("Scripting.FileSystemObject")

            Set f1 = fso.OpenTextFile (strFile,1)

            strNewFileName = Left(strFile,Len(strFile)-4) & "Mod" & Right(strFile,4)
            Set f2 = fso.CreateTextFile (strNewFileName,True)

            Do While f1.AtEndofStream <> True

            strline = f1.ReadLine

            strSource = DW.Utilities.fParseString(strline,5,4,",")

            strTarget = DW.Utilities.fParseString(strline,5,5,",")

            f2.writeline strSource & "," & strTarget

            Loop

            f1.Close
            f2.Close

            Set fso=Nothing

            End Sub

            After implementing the script, a new file with xxxmod.trn has been generated which is as desired. But instead of using the new file,original file which has 5 cols is being used in creation of map.Can you advise me how can we change BefImportScript so that it picks the new file while creating map and not the original one.

            Thanks in adance
            • 3. Re: BefImportMap Issue
              847750
              Hi,

              Any suggestions??