Tip: Write each table with SAP GUI Scripting to a CSV file

[stefan_schnell]

CSV files can be very easy imported in Excel. So is this file format a good platform for information exchange. It is possible to export table data via TAC SE16 and SAP GUI Scripting. Here is a SAP GUI Script which writes each table in a file in CSV (Comma Separated Value) format. Use only one sub call

ReadTableInFile "SFLIGHT", "C:\\Dummy\\SFlight.csv"

in your SAP GUI script and you get the table SFLIGHT in the file C:\Dummy\SFlight.csv.
I think it is a good way, if you need to download data tables e.g. to analyze data constellations. So you can download a few data tables without any manual activities.

Here you find a description how you can manipulate very big data files and the aspect to connect them to a Microsoft Access database for a fast and flexible analysis - it is in German language.

The following source code is full commented, so I hope it is easy to unterstand how it works.

 

2017/07/06: Here an interesting question in nearly the same context.

 

'-Begin-----------------------------------------------------------------

'-

'- Author: Stefan Schnell

'- Page: www.stschnell.de

'- Date: 2014/04/03

'-

'-----------------------------------------------------------------------



  '-Constants-----------------------------------------------------------

    Const Delimiter = ";"



  '-ReadTableInFile-----------------------------------------------------

    Sub ReadTableInFile(TableName, FileName)



      '-Reset the session-----------------------------------------------

        session.findById("wnd[0]/tbar[0]/okcd").text = "/n"

        session.findById("wnd[0]/tbar[0]/btn[0]").press



      '-Open TAC SE16---------------------------------------------------

        session.findById("wnd[0]/tbar[0]/okcd").text = "/nSE16"

        session.findById("wnd[0]/tbar[0]/btn[0]").press



      '-View table------------------------------------------------------

        session.findById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").text = _

          TableName

        session.findById("wnd[0]/tbar[1]/btn[7]").press

        session.findById("wnd[0]/tbar[1]/btn[8]").press



      '-Set display to ALV Grid view------------------------------------    

        '-Open user specific parameters dialog--------------------------

        '- Attention: Here is a language specific code, customize it

        '-

        '---------------------------------------------------------------

          '-German language---------------------------------------------

          'Set Einstellungen = Menu.FindByName("Einstellungen", "GuiMenu")

          'Set BenutzerPar = Einstellungen.FindByName("Benutzerparameter...", _

          '  "GuiMenu")

          '-English language--------------------------------------------

          Set Einstellungen = Menu.FindByName("Settings", "GuiMenu")

          Set BenutzerPar = Einstellungen.FindByName("User Parameters...", _

            "GuiMenu")

          BenutzerPar.Select()



        '-Set the display-----------------------------------------------

          Set ALVGridView = session.findById("wnd[1]/usr/tabsG_TABSTRIP/" & _

            "tabp0400/ssubTOOLAREA:SAPLWB_CUSTOMIZING:0400/radRSEUMOD-TBALV_GRID")

          If ALVGridView.Selected = vbFalse Then

            ALVGridView.select()

          End If

          session.findById("wnd[1]/tbar[0]/btn[0]").press        

        Set BenutzerPar = Nothing

        Set Einstellungen = Nothing

        Set Menu = Nothing



      '-Get rows and columns--------------------------------------------

        Set table = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell")

        Rows = table.RowCount() - 1

        Cols = table.ColumnCount() - 1



      '-Write the table to a CSV file-----------------------------------

        Set oFile = CreateObject("Scripting.FileSystemObject")

        If IsObject(oFile) Then

          Set SFlightFile = oFile.CreateTextFile(FileName, True)

          If IsObject(SFlightFile) Then



            '-Get the technical title of all columns in the first line--

              Set Columns = table.ColumnOrder()

              For j = 0 To Cols

                If j = Cols Then

                  SFlightFile.Write(CStr(Columns(j)))

                Else

                  SFlightFile.Write(CStr(Columns(j)) & Delimiter)

                End If

              Next

              SFlightFile.WriteLine("")



            '-Get the title of all columns in the second line-----------

              For j = 0 To Cols

                Set ColumnTitle = table.GetColumnTitles(CStr(Columns(j)))

                If j = Cols Then

                  SFlightFile.Write(CStr(ColumnTitle(0)))

                Else

                  SFlightFile.Write(CStr(ColumnTitle(0)) & Delimiter)

                End If

              Next

              SFlightFile.WriteLine("")



            For i = 0 To Rows

              For j = 0 To Cols

                If j = Cols Then

                  SFlightFile.Write(table.GetCellValue(i, _

                    CStr(Columns(j))))

                Else

                  SFlightFile.Write(table.GetCellValue(i, _

                    CStr(Columns(j))) & Delimiter)

                End If

              Next



              '-Each 32 lines actualize the grid------------------------

                If i Mod 32 = 0 Then

                  table.SetCurrentCell i, CStr(Columns(0))

                  table.firstVisibleRow = i

                End If



              '-Carriage and return after a line------------------------

                If i <> Rows Then

                  SFlightFile.WriteLine("")

                End If



            Next

            SFlightFile.Close



          End If

        End If



        Set ALVGridView = Nothing

        Set Columns = Nothing

        Set table = Nothing 

    End Sub



  '-Main----------------------------------------------------------------

    If Not IsObject(application) Then

      Set SapGuiAuto = GetObject("SAPGUI")

      Set application = SapGuiAuto.GetScriptingEngine

    End If



    If Not IsObject(connection) Then

      Set connection = application.Children(0)

    End If



    If Not IsObject(session) Then

      Set session = connection.Children(0)

    End If



    '-Read the table SFLIGHT in a file----------------------------------

      ReadTableInFile "SFLIGHT", "C:\\Dummy\\SFlight.csv"



'-End-------------------------------------------------------------------