Easy Excel Import from local File into Table using GUI_UPLOAD
Hello,
here is an easy way to import a local Excel (text) file into a table.
In this example I have a simple table, named ZDE_EXAMPLE_TABLE, with the following structure:
- MANDT (CLNT) (3)
- COUNTRY (CHAR) (40)
- ACTIVE (CHAR) (1)
Now I want to import data from Excel in that table.
1. Prepare the data in Excel
In Excel prepare your data by considering the data structure of the table you want to import in.
Save the data as text file with tab stops. Your text file should look like this:
100 Germany X
100 Greece X
100 Ireland X
100 USA X
2. Use report (GUI_UPLOAD) to import your file into the table
Copy the following code into a new report (using transaction SE80). Save and activate it.
*&---------------------------------------------------------------------*
*& Report ZDE_EXCEL_IMPORT_INTO_TABLE
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZDE_EXCEL_IMPORT_INTO_TABLE.
tables: ZDE_EXAMPLE_TABLE.
data: it_data type standard table of ZDE_EXAMPLE_TABLE.
data: l_file type string,
l_table_count type I.
PARAMETERS file LIKE l_file.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
FILENAME = file
HAS_FIELD_SEPARATOR = 'X'
TABLES
DATA_TAB = it_data
EXCEPTIONS
FILE_OPEN_ERROR = 1
FILE_READ_ERROR = 2
NO_BATCH = 3
GUI_REFUSE_FILETRANSFER = 4
INVALID_TYPE = 5
NO_AUTHORITY = 6
UNKNOWN_ERROR = 7
BAD_DATA_FORMAT = 8
HEADER_NOT_ALLOWED = 9
SEPARATOR_NOT_ALLOWED = 10
HEADER_TOO_LONG = 11
UNKNOWN_DP_ERROR = 12
ACCESS_DENIED = 13
DP_OUT_OF_MEMORY = 14
DISK_FULL = 15
DP_TIMEOUT = 16
OTHERS = 17
.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
DELETE FROM ZDE_EXAMPLE_TABLE.
INSERT ZDE_EXAMPLE_TABLE FROM TABLE it_data.
DESCRIBE TABLE it_data LINES l_table_count.
COMMIT WORK AND WAIT.
WRITE l_table_count.
WRITE 'entries imported!'.
3. Run the report and start the import
Run the report (F8) and you will see an input text field.
Write the name of the local text file into the field and run the report.
That’s it!
Please note: In this example program there is no error handling icnluded. It is a very basic example. If you have duplicate entries in your text file and the table does not allow duplicate entries for a certain field, the program may crash.
Best regards,
Christian
Hi Christian,
Try to improve your example working with ABAP Object Oriented using below class.
CALL METHOD cl_gui_frontend_services=>…
Regards,
If you wish to avoid of manual steps, you should better use ABAP2XLSX.
Hi,
thank you Roberto for your comment. I think I will post another example or update my post with the class you mentioned.
Shai, the ABAP2XLSX sounds interesting, thank you. Is it for data export (Excel) only, or also for data import?
Best regards,
Christian
Despite its name, ABAP2XLSX is also using for import of XLSX.
Christian,
Sorry but I'm having trouble understanding why did you choose to post this and why with such code example?
There are several issues, from what I see.
For full disclosure, I've reported this blog to the moderators. In my opinion, they should have advised you of this privately and suggested to reconsider posting or at least to update the blog to include some story/business case and improve the code example.
Unfortunately, the moderators chose not to do anything, it seems.
Just for clarity, moderation of blogs is, at the time of writing, only carried out by global moderators not by those of us who have a particular specialism.
Yes. And if global moderators chose to moderate themselves then they should be reaching out to those with specific knowledge to advise in such situations. But it seems that instead they chose to rubber-stamp the content they can't understand. Which makes me wonder what is even the point then.