3 Replies Latest reply on Jul 3, 2014 9:08 AM by bd9daf30-ef8e-4739-b897-d0001a12a9b9

    Import cell text in CSV file to memo field in FDM

    bd9daf30-ef8e-4739-b897-d0001a12a9b9

      Hi everyone

       

      I am looking to import data to memo fields in FDM. I have a CSV source file that is imported via batch loader. I suspect I would have to adjust a script but I am not sure which one. Can anyone give nme some advise as to (in a best practice sense):

      - What should the layout of the CSV source file be? (this is an extract from an HFM app so prob not very flexible)

      - What script would have to be adjusted to import data to memo fields?

       

      Some background:

      The source application is HFM which produces a CSV file for import to FDM. The source data contains amount and cell text for the amounts. FDM is used as an ETL to load data to another HFM application. I understand theat the LOADB Action script exports data from memo fields to cell text, provided that integration option is enabled. Thus the second part of this data load should work.

        • 1. Re: Import cell text in CSV file to memo field in FDM
          Francisco Amores

          Hi,

           

          there are different options to achieve this.

           

          You can import your column with cell text information to an attribute dimension. Then before data is posted to the main table (Event script ImportAction / subevent PostWorkToMainProcess), you can create memo items based on this attribute and data being loaded. You can use either SQL to insert into memo item tables or use fMemoAddItem (class ArchiveMgr)

           

          You have to enable "Cell Text loading" from adapter in order these memo items are loaded as cell texts in HFM.

           

          Hope that clarifies

          • 2. Re: Import cell text in CSV file to memo field in FDM
            bd9daf30-ef8e-4739-b897-d0001a12a9b9

            Hi Francisco

             

            thanks for your quick reply. Your suggestion worked and I am able to write cell text to memo fields on import. There appear to be a few memo items that are always required, e.g. Author and ItemDate. When I export the memo field to HFM it writes not only the LongText to CellText but includes the Author and timestamp as well. I cannot regulate on import what to write to memo fields since some information is required so I would have to limit what it exported to HFM. Any ideas?

             

            I include the action script to import to memo fields:

             

            'Write Celltext to Memo field
            'Get the variables from the POV for the Import
            strPartKey = API.POVMgr.fCheckLocProf(API.POVMgr.PPOVLocation).lngLocKey
            strCatKey = API.POVMgr.fCategoryKey(API.POVMgr.PPOVCategory)
            strPerKey = API.POVMgr.fPeriodKey(API.POVMgr.PPOVPeriod).strDateKey
            strTable = "tDataSeg" & RES.PlngSegKey

            If strPartKey = 835 Then
            'SQL
            strSQL = "SELECT t.dataKey, t.Attr1 AS Celltext"
            strSQL = strSQL & " FROM " & strTable & " t"
            strSQL = strSQL & " WHERE t.partitionKey = " & strPartKey  
            strSQL = strSQL & " AND t.CatKey = " & strCatKey 
            strSQL = strSQL & " AND t.PeriodKey = '" & strPerKey & "'"

            ' 'Initialize the recordset object
            Set rs = DW.DataAccess.farsSnap(strSQL)

            'Loop through results
            If Not rs.BOF And Not rs.EOF Then
              Do While Not rs.EOF
               'Create new object
               API.ArchiveMgr.mIntializeMemoItemProperty 

               'Process the field value
               With API.ArchiveMgr.PobjMemoItem
                         .strAuthor = DW.Connection.PstrUserID
                         .dteMemoItemDate = Now
                         .lngDataKey = rs.fields("datakey").value
                         .lngDataTableID = RES.PlngSegKey
                         .strCategory = strCatKey
                         .strPeriod = strPerKey
                         .strShortText = "Memo"
                         .strLongText = rs.fields("Celltext").value
                         .lngPartitionKey = strPartKey
                         .lngMemoItemType = 1
               End With
                          
                        If API.ArchiveMgr.fMemoAddItem(API.ArchiveMgr.PobjMemoItem) Then
                         bOk = True
                        Else
                         bOk = False
                        End If

               'Move to the next record
               rs.Movenext
              Loop
            End If
            End If

            • 3. Re: Import cell text in CSV file to memo field in FDM
              bd9daf30-ef8e-4739-b897-d0001a12a9b9

              Sorry, a short search on the forum revealed the answer:

               

              Can we upload cell text via text file/excel file via FDM to HFM?

               

              Follow Yves LOADB script to eliminate unwanted memo field info from cell text

               

              Thanks again for your input, Francisco