Technical Articles

Stefan Schnell

April 2, 2012 2 minute read

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

12 Comments

You must be Logged on to comment or reply to a post.

Former Member

February 6, 2014 at 4:15 pm

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?

Stefan Schnell

Blog Post Author

February 7, 2014 at 11:10 am

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

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

Former Member

February 10, 2014 at 8:43 am

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?

Stefan Schnell

Blog Post Author

February 10, 2014 at 3:57 pm

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

Former Member

February 11, 2014 at 8:45 am

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:

switch table representation to 'SE16 standart list'
dump data via 'Save as..' dialog.
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.

Holger Köhn

February 11, 2014 at 11:15 am

Hello.

Scroll during execution:

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

Then data from Memory will load into control-objects.

Holger

Stefan Schnell

Blog Post Author

February 11, 2014 at 4:41 pm

Hello Holger,

thanks for sharing this Information, it works perfect.

Cheers

Stefan

Hoang Nga

October 12, 2019 at 2:49 am

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

Stefan Schnell

Blog Post Author

October 14, 2019 at 5:05 am

Hello Hoang,

can you describe your problem more in detail please.

Thanks and best regards
Stefan

Hoang Nga

November 4, 2019 at 1:21 am

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.

Caio Jordão Calisto

September 5, 2019 at 4:40 pm

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.

Bullobily B

November 24, 2021 at 12:49 pm

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

Technical Articles

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

Assigned Tags