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