Hello community,
here is a SAP GUI Script to write 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.
Any comments are welcome.
Cheers
Stefan
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