This discussion is archived
4 Replies Latest reply: Jan 24, 2013 7:43 AM by user8714121 RSS

automating export to excel

user8714121 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Great, thank you. That worked.

Legend

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