Technical Articles
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-------------------------------------------------------------------
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:
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?
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
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?
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
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:
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.
Hello.
Scroll during execution:
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").firstVisibleRow = i
Then data from Memory will load into control-objects.
Holger
Hello Holger,
thanks for sharing this Information, it works perfect.
Cheers
Stefan
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
Hello Hoang,
can you describe your problem more in detail please.
Thanks and best regards
Stefan
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.
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.
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: