This discussion is archived
3 Replies Latest reply: Feb 19, 2013 8:20 AM by 847750 RSS

BefImportMap Issue

847750 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

    Any suggestions??

Legend

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