4 Replies Latest reply on Jan 24, 2013 3:43 PM by user8714121

    automating export to excel

    user8714121
      I have several different scripts running on import and would like a way to automatically export the import screen in FDM to excel. This should give me an excel file that is the result of the import scripts but has not been translated through the maps. From what i have found i need to place this in the AftFileImport event script so that it is run every time. I realize there is a button on the import screen that performs this function however I would prefer not to have to manually press this every time a file is submitted. I assume there is some way to do this and have looked through the API guide, however, I wasn't about to find exactly what i was looking for. Can someone please provide the function i would use to do this?

      Thank you
        • 1. Re: automating export to excel
          user8714121
          So far my script goes something like this, although im sure there is an easier way to do it:

          Sub AftFileImport(strLoc, strCat, strPer, strFile, lngRecs)


          'Perform script on SAP-TB location only

          If RES.PlngLocKey = "750" Then


          'Declare Variables     

               'Pov variables     

               Dim dtePerKey 'Placeholder for Period key

               Dim strCatKey 'Placeholder for Category key

               Dim strLocKey 'Placeholder for Location Key

               Dim strSegKey 'Placeholder for Segment Key


               'File system variables

               Dim objFileSystem 'File System

               Dim strFileName 'File name

               Dim ObjTextFile

               Dim strString


               'SQL variables

               Dim strSQL ' Placehold for SQL Query

               Dim rssql 'Placeholder for result set for sql query execution


          'Initialize POV Variables

               dtePerKey = RES.PdtePerKey 'Period Key

               strCatKey = RES.PlngCatKey 'Category Key

               strLocKey= RES.PlngLocKey 'Location Key     

               strSegKey = API.DataWindow.Reports.PlngSegKey 'Segment key



          'SQL Statement

          strSQL = "Select * From tDataMapSeg" & strSegKey

          strSQL = strSQL & " WHERE "

          strSQL = strSQL & " PartitionKey = " & strLocKey & " AND "

          strSQL = strSQL & " CatKey = " & strCatKey & " AND "

          strSQL = strSQL & " PeriodKey = '" & dtePerKey & "'"


          Set rssql = API.DataWindow.DataAccess.farsKeySet(CStr(strSQL))

               
               'Create the filesystem object

               Set objFileSystem = CreateObject("Scripting.FileSystemObject")



               'Create File with file name

          Set ObjTextFile = objFileSystem.OpenTextFile(DW.Connection.PstrDirInbox & "\SAP-TB-May.txt", 2, TRUE, 0)

          ObjTextFile.Close




          With rssql

                    If Not .EOF and Not .BOF Then

                    Set objTextFile = objFileSystem.OpenTextFile(DW.Connection.PstrDirInbox & "\SAP-TB-May.txt", 2, FALSE, 0)

                    Do Until .EOF

               'Loop across all fields in recordset delimiting them with a tab key

               For i = 0 To .Fields.Count -1

               strString = StrString & (i) & vbTab

               Next

               'Output the resulting string to the text file

               ObjTextFile.WriteLine (strString)

               'Reset the string to empty for the next record

               strString = ""

               'Move to the next record in the recordset

               .MoveNext

                    Loop

                    'Close the recordset and the text file

                    .Close

                    ObjTextFile.Close

                    End If

                    'Destroy the instance of the recordset from memory

                    Set rssql = Nothing

          End With



               End IF

          End Sub
          Edited by: user8714121 on Jan 23, 2013 1:51 PM

          Edited by: user8714121 on Jan 23, 2013 2:12 PM
          • 2. Re: automating export to excel
            user8714121
            Alright, i was able to get this to work. It creates a text file with the tdatamap contents for the current month on import. All i am lacking now is the correct data table, i would like this file to include the results of the import step in FDM. If anyone knows what data table i should use please let me know.



            Sub AftFileImport(strLoc, strCat, strPer, strFile, lngRecs)

            'Perform script on SAP-TB location only

            If RES.PlngLocKey = "750" Then

            'Declare Variables     
                 'Pov variables     
                 Dim dtePerKey 'Placeholder for Period key
                 Dim strCatKey 'Placeholder for Category key
                 Dim strLocKey 'Placeholder for Location Key
                 Dim strSegKey 'Placeholder for Segment Key
            Dim StrPeriod

            'File system variables
                 Dim objFileSystem 'File System
                 Dim strFileName 'File name
                 Dim ObjTextFile
                 Dim strString
                 
            'SQL variables
                 Dim strSQL ' Placehold for SQL Query
                 Dim rssql 'Placeholder for result set for sql query execution

            'Initialize POV Variables
                 dtePerKey = RES.PdtePerKey 'Period Key
                 strCatKey = RES.PlngCatKey 'Category Key
                 strLocKey= RES.PlngLocKey 'Location Key     
                 strSegKey = API.DataWindow.Reports.PlngSegKey 'Segment key

            'SQL Statement
            strSQL = "Select * From tDataMapSeg" & strSegKey
            strSQL = strSQL & " WHERE "
            strSQL = strSQL & " PartitionKey = " & strLocKey & " AND "
            strSQL = strSQL & " CatKey = " & strCatKey & " AND "
            strSQL = strSQL & " PeriodKey = '" & dtePerKey & "'"

            Set rssql = API.DataWindow.DataAccess.farsKeySet(CStr(strSQL))
                 
                 'Create the filesystem object
                 Set objFileSystem = CreateObject("Scripting.FileSystemObject")

                 'Create File with file name
            StrPeriod = RES.PstrPer

            Set ObjTextFile = objFileSystem.OpenTextFile(DW.Connection.PstrDirInbox & "\SAP - TB- " & StrPeriod & ".txt", 2, TRUE, 0)
            ObjTextFile.Close



            If Not rssql.EOF and Not rssql.BOF Then
            Set objTextFile = objFileSystem.OpenTextFile(DW.Connection.PstrDirInbox & "\SAP - TB- " & StrPeriod & ".txt", 2, FALSE, 0)
            Do Until rssql.EOF

            'Loop across all fields in recordset delimiting them with a tab key
            For i = 0 To rssql.Fields.Count -1
            strString = StrString & rssql(i) & vbTab
            Next

            'Output the resulting string to the text file
            ObjTextFile.WriteLine (strString)

            'Reset the string to empty for the next record
            strString = ""

            'Move to the next record in the recordset
            rssql.MoveNext
            Loop

            'Close the recordset and the text file
            rssql.Close
            ObjTextFile.Close
                 End If

            'Destroy the instance of the recordset from memory
                 Set rssql = Nothing


            End IF
            End Sub

            Edited by: user8714121 on Jan 23, 2013 9:11 PM
            • 3. Re: automating export to excel
              SH_INT
              What you need to do is query the tPOVPartition table to retrieve the dataseg table associated with the location you are loading the data for. Field is called PartSegmentKey. This will be a number, just append this number to tDataSeg and voila you have a reference to the correct data table
              • 4. Re: automating export to excel
                user8714121
                Great, thank you. That worked.