2 Replies Latest reply on Dec 24, 2014 6:00 PM by Francisco Amores

    automating export to excel

    1035643

      I 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 and need to be saved in my local drive not in FDM server. 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.

       

      Can someone please provide the function i would use to do this?

        • 1. Re: automating export to excel
          1035643

          From below script i got to know that i can automate the same

           

          '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

           

           

          But it is exporting mappings and that too in .txt format in FDM server, but to export the imported data in excel file and get saved in my local drive

           

          Any sugestions

          • 2. Re: automating export to excel
            Francisco Amores

            Hi,

             

            did you have a look to API function mRecordSetToExcel?

             

            you can export recordset to excel using this function.

             

            You could use FDM SQL View VDATA to export your data as required.

             

            Here you have an example of custom VB function to export the result of SQL query:

             

             

            Function ExportTableToExcel(sTable,sDateTime)

              Dim sSQL

              Dim rsTable

              Dim sFilePath

             

              'SQL Statement

              sSQL = "SELECT * FROM " & sTable

              Set rsTable = API.DataWindow.DataAccess.farsSnap(sSQL)

              API.DataWindow.Utilities.mRecordsetToExcel "Table: " & sTable & " (" & sDateTime & ")",rsTable, sDateTime & "_" & sTable

             

              'Return file path

              sFilePath = DW.Connection.PstrDirOutbox & "\ExcelFiles\" & sDateTime & "_" & sTable

             

              ExportTableToExcel = sFilePath

             

             

            End Function