This discussion is archived
4 Replies Latest reply: Dec 10, 2012 1:34 PM by 729728 RSS

FDM Script to import data

729728 Newbie
Currently Being Moderated
Hi,

I'm new to FDM scripting. So any pointers will be appreciated. I need to import a TB with the following format in exel file.

row1 ***CostCenter Number***
row2 ***TB name*
row3 - Other parameters
row4 - Blank
row5 - Data headers
row6 - Data begins - format given below -

row7 - Account Desc Opening Bal Closing Bal

-----

I have created an import format with the 4 fields - CostCentre, Account,Desc and closing bal. Now my question is -

1. How do i create script that picks the costcenter number from row1 ?

Thanks in Advance!!
  • 1. Re: FDM Script to import data
    SH Guru
    Currently Being Moderated
    That format will not work in an excel file, FDM requires a specific excel template, also the FDM excel templates do not use import scripts,so first convert to a csv file.

    You will then need to use a temporary variable for this e.g. RES.PvarTemp1 and create 2 import scripts

    Assuming that cost centre is the only value on line one of the file what you need to do is:

    Attach an Import script to the Amount entry in your import format and put in the following code (this assumes cost centre is always the first header line and your script is called GetCostCentre)


    If Not Len(RES.PvarTemp1) > 0 Then
    RES.PvarTemp1 = DW.Utilities.fParseString(strRecord,4,1,",")
    End If

    GetCostCentre = strField

    Attach an import script to the CostCentre entry in the import format called SetCostCentre, it will have one line

    SetCostCentre = RES.PvarTemp1

    There is a very similar example in the FDM admin guide Scripting cahpter which has a bit more description.

    Edited by: SH on Dec 7, 2012 11:17 AM
  • 2. Re: FDM Script to import data
    729728 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the reply.

    What if my entity number is in row number 2 ? I have gone through the FDM help file but struggling to get this right. I have my TB in the following csv format as shown below -

    * Amounts in curr
    CostCentre Name (Cost Centre Number),,,,,,,
    ,, Balance,,, Balance,,
    2063,Description,-182448.66,182448.66,0.00,0.00

    Also, I'm using two scripts (one for the amount and the other for entity) as you have mentioned in your reply. My script works only when entity number is in row one but doean't work when entity is in row 2 as given above.

    Any pointers will be appreciated.

    Thanks in Advance!!
  • 3. Re: FDM Script to import data
    user10757003 Journeyer
    Currently Being Moderated
    If you look a the example in the FDM admin guide (Section = Storing temporary values), you will see that you will need to perform a test in the script attached to the amount field, to see if the line begins with the word "CostCentre" (uses StrRecord rather than strfield). the example is an exact example of what you are trying to achieve, and it does not matter what line(s) in the data file that it occurs.
  • 4. Re: FDM Script to import data
    729728 Newbie
    Currently Being Moderated
    Thanks for the reply.

    I was able to figure out the logic for my problem.

    Thanks again for everyone's help!!

Legend

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