4 Replies Latest reply: Dec 10, 2012 3:34 PM by 729728 RSS

    FDM Script to import data


      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
          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

            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,,

            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
              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
                Thanks for the reply.

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

                Thanks again for everyone's help!!