Skip to Content
Technical Articles
Author's profile photo Stefan Schnell

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

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-------------------------------------------------------------------

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thank you for this code!

      I have just tryed it on my USR02 table, which consist about 2000 records.

      After scripting was done, my output file contains strings with gaps located between normally obtained table strings:

      q;w;e;r;t;y;q;w;e;r;t;y;u;

      ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

      q;w;e;r;t;y;q;w;e;r;t;y;u;


      Looks like SAP GUI store  data in memory allocated buffer. And when you call table.SetCurrentCell, SAP GUI:

      • scroll grid control
      • get info from memory
      • set info to cells
      • repaint cells (and i saw this repaint process)

      The result of calling table.GetCellValue may return nothing because the actual data not setted to control at moment.

      Is there a way to fetch data directly from memory instead of grid control?

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Serg,

      thanks for your reply and your hint.

      You are right, the function GetCellValue returns nothing because the data is not in the control at this moment.

      I solve it on the following way, I changed

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

        If i Mod 32 = 0 Then

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

        End If

      to

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

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

      And now it works without empty lines with the table USR02 with 801 entries.

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hello Stefan,

      glad to see your answer. After several attempts i came to the same solution.

      I hope we can try to solve another important issue of this solution.. What`s about performance?

      I think that simple iterating of grid control may be very expensive in some real tasks, because system spend much time for cells repainting. Moreover, i saw a lags while next portion data was appeared in grid during iteration.

      (however it should be noted that I rewrote your example in C++ ATL based application without any file output, but i don't think it matters...)

      So.. Do you know a way to access internal grid data? Maybe some mixing of scripting and ABAP, as mentioned in Gain Programmatic Access to Data of SAPGUI ALV Reports article?

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Serg,

      I think the SAP Data Provider is what you looking for. You find an old documentation here.

      Let us know your results.

      Thanks and cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      mmm thank you! very interesting document. Unfortunately i'm new to the SAP and especially ABAP programming, so i'll continue my research at next iteration.

      At this point my deсision is:

      1. switch table representation to 'SE16 standart list'
      2. dump data via 'Save as..' dialog.
      3. read and parse data from file

      In the first approximation it should be acceptable solution.

      Just for comparison: points 1 and 2 together take about 1.5-2 seconds in my system while cells iterating - 45-50. Tested on 1500 records of usr02 table.

      Author's profile photo Holger Köhn
      Holger Köhn

      Hello.

      Scroll during execution:

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

      Then data from Memory will load into control-objects.

      Holger

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Holger,

      thanks for sharing this Information, it works perfect.

      Cheers

      Stefan

      Author's profile photo Hoang Nga
      Hoang Nga

      Hello Stefan, Thank you for sharing. I think that is helful for me. But i have a bit matter. I need to copy automatic a column to another column in grid. I think i can use it. But it notice that is wrong because of table.ColumnOrder() and i don’t know why. I run it on vba excel. Can you help me!thank you so much. I hope for your answer

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Hoang,

      can you describe your problem more in detail please.

      Thanks and best regards
      Stefan

      Author's profile photo Hoang Nga
      Hoang Nga

      Thank Stefan Schnell, I try to fix it and I have done. I found that I need to use SAP GUI Scripting API from your other sharing.

      Author's profile photo Caio Jordão Calisto
      Caio Jordão Calisto

      Hi Stefan, how are you doing?

      Thanks for the initiative. I have tested your code and it played smoothly, but when we talk about performance, I was not able to have the data in a fast way (On my job I have to deal with large ammount of data).

       

      I've came up with the same solution as a former member of the thread who went to download the files instead of reading the grid. Below there is the link so you can download.

      https://1drv.ms/x/s!Aq49f1J8xcApia5PeDBC7NcPyhyaIg?e=ePd4ZW

      I Believe that by this time you already have improved your script skills. I think the way you deal with the menu was very clever, but the easiest way perhaps is the direct approach, reading the id of the item through the tools that sap create for it (I can share with you if you dont have it)

       

      If you have any question or perhaps you came up with any upgrade in the file,please let me Know.

       

      Thanks for your effort.

       

       

      Author's profile photo Bullobily B
      Bullobily B

      Hello Stefan, many thanks for your code! I adapted it to my own need, which is to load a SAP table into a VBA table. Here is the code I used to achieve this, if you need it don't hesitate to copy/paste it:

      'permet d'importer un tableau SAP shell sous forme de tableau VBA
      Private Function loadSAPshell(obj As Object) As Variant
      Dim i As Integer
      Dim j As Integer
      Dim iMax As Integer
      Dim jMax As Integer
      
      Dim columnTitle As Variant
      
      Dim data() As String
      
      iMax = obj.RowCount()
      jMax = obj.ColumnCount() - 1
      
      ReDim data(0 To iMax, 0 To jMax) As String
      
      'écriture des entêtes avec les codes colonnes SAP (ne correspond pas à ce que l'utilisateur voit)
      For j = 0 To jMax
          Set columnTitle = obj.GetColumnTitles(CStr(obj.ColumnOrder(j))) 
          data(0, j) = CStr(CStr(obj.ColumnOrder(j))) 
      Next
      
      'écriture du contenu
      For i = 1 To iMax
          For j = 0 To jMax
              data(i, j) = obj.GetCellValue(i - 1, CStr(obj.ColumnOrder(j))) 
          Next
      Next
      
      loadSAPshell = data
      End Function