Skip to Content
Author's profile photo Christian Pfeil

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

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Roberto FORTIS
      Roberto FORTIS

      Hi Christian,

      Try to improve your example working with ABAP Object Oriented using below class.

      CALL METHOD cl_gui_frontend_services=>…

      Regards,

      Author's profile photo Shai Sinai
      Shai Sinai

      If you wish to avoid of manual steps, you should better use ABAP2XLSX.

      Author's profile photo Christian Pfeil
      Christian Pfeil
      Blog Post Author

      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

      Author's profile photo Shai Sinai
      Shai Sinai

      Despite its name, ABAP2XLSX is also using for import of XLSX.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      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.

      1. Title is misleading. When you say "Excel file" this day and age people would assume XLSX format. But you're really talking about a plain text format masquerading as XLS.
      2. It's not clear what's the story behind this. What were you trying to achieve and why this way?
      3. FM GUI_UPLOAD has already been replaced by a method, as others also pointed out.
      4. TABLES should no longer be used. DESCRIBE is not needed anymore (we can use lines() ). This has been known for more than 10 years already.
      5. The code just doesn't make any sense. Why do you do that DELETE, INSERT and then COMMIT? This looks very strange and is not needed at all to demonstrate FM.
      6. Sorry to say but this whole thing is already common knowledge. Google: GUI_UPLOAD site:sap.com has more than 5000 hits. This goes back to the first question - why post this? Who did you think would benefit from it? I can assure you that ABAPers already know about this FM. And if you targeted CRM consultants dipping toes into ABAP then I'm afraid you're doing them disservice by offering such code example. This is actually covered by ABAP documentation already, with an example and all.

      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.

      Author's profile photo Matthew Billingham
      Matthew Billingham

      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.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      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.