3 Replies Latest reply on Oct 5, 2016 9:40 PM by SH_INT

    FDMEE File to Database

    2837320

      Hi Team,

       

      Can we read a data file and load data to FDMEE Open Interface table using an Event Script. If so kindly please help in this.

       

      Regards,

      Roshin

        • 1. Re: FDMEE File to Database
          ThinkFDM

          Absolutely.  We've done this numerous times using the BefImport event script.  Check out this info in the FDMEE Admin Guide.

          1. Working with Open Interface Adapters for how to set up a OIA.
          2. Then see Events Script Sample for how to use Jython to insert data into the open interface table.
          1 person found this helpful
          • 2. Re: FDMEE File to Database
            2837320

            Thank you . That really helped

             

            I got that , But now getting an error at last step of writing to Database. Please find my code below.

             

            *****************************************

            import java.math.BigDecimal as BigDecimal

            import java.sql as sql

            import java.sql.DriverManager.getConnection

            import com.hyperion.aif.scripting.API as API

             

            infilename = fdmContext["INBOXDIR"]+"/InputFile1.txt"

            infile = open(infilename, "r")

            for line in infile:

                Period_Num = "0"

                column = line.rsplit(',',10)

                fdmAPI.logInfo("Loop1 Started for getting Columns")

                fdmAPI.logInfo(column[0])

                fdmAPI.logInfo(column[1])

                fdmAPI.logInfo(column[2])

                fdmAPI.logInfo(column[7])

                fdmAPI.logInfo(column[8])

                fdmAPI.logInfo(column[9])

                if column[8] == "Jun":

                    Period_Num = "6"

                elif column[8] == "Jul":

                    Period_Num = "7"

                elif column[8] == "Aug":

                    Period_Num = "8"

                fdmAPI.logInfo("Derived period num = "+Period_Num)

                fdmAPI.logInfo("Executing even script for population Open Interface Table")

                'Insert Statement is loaded'

                insertStmt = "INSERT Into AIF_OPEN_INTERFACE (BATCH_NAME,PERIOD_NUM,PERIOD,COL01,COL02,COL03,AMOUNT) Values (?,?,?,?,?,?,?)"

                params = [ 'IC_PS',Period_Num,column[8],column[0],column[1],column[2],getBigDecimal(column[9]) ]

                fdmAPI.executeDML(insertStmt, params, False)

                fdmAPI.logInfo("IC data inserted")

            infile.close()

             

            ***************************************************

             

            Am getting error when i import the Amount column in. Error log says as below

            ***********************************

            2016-10-05 12:39:51,527 ERROR [AIF]: The script has failed to execute:

            2016-10-05 12:39:51,543 FATAL [AIF]: Error in Comm.executeJythonScript

            Traceback (most recent call last):

              File "<string>", line 528, in executeJythonScript

              File "\\bvbhfmwp01\EPM_Share\FDMEE/data/scripts/custom/FileToSQL.py", line 28, in <module>

                params = [ 'IC_PBCS',Period_Num,column[8],column[0],column[1],column[2],BigDecimal(column[9]) ]

            NumberFormatException: java.lang.NumberFormatException

             

            2016-10-05 12:39:51,543 FATAL [AIF]: Error in CommProcess.executeCustomScript

            Traceback (most recent call last):

              File "<string>", line 191, in executeCustomScript

              File "<string>", line 486, in executeCustomScript

              File "<string>", line 528, in executeJythonScript

              File "\\bvbhfmwp01\EPM_Share\FDMEE/data/scripts/custom/FileToSQL.py", line 28, in <module>

                params = [ 'IC_PBCS',Period_Num,column[8],column[0],column[1],column[2],BigDecimal(column[9]) ]

            NumberFormatException: java.lang.NumberFormatException

             

            2016-10-05 12:39:51,574 FATAL [AIF]: Error in COMM Execute Custom Script

            2016-10-05 12:39:51,652 INFO  [AIF]: FDMEE Process End, Process ID: 1014

            **************************************

             

            But i have already defined it as BIG decimal. Please help in how can i take the Amount value from File to Open Interface Adapter.

             

            I am struck at this last part.

             

            Regards,

            Roshin

            • 3. Re: FDMEE File to Database
              SH_INT

              Replace getBigDecimal(column[9]) with BigDecimal(column[9]). Also are you sure column[9] is returning a string representation of a number? Because that is usually the cause of a number exception error when it is not.