Skip to Content
Technical Articles

Read an excel file from AL11 Directory

Introduction :

There are scenarios where the user will upload a file in the Application Server which needs to be consumed to manipulate that data (such as updating in a table or sending it as a mail). In that case, we can use the DATASET to read the file in the Application Server.

 

Problem Statement:

The user uploads an Excel File in the Application Server. That Excel file is picked from the list of files in the Application server and stored in an internal table and then the data is uploaded to a custom table in SAP.

 

Solution:

Step 1: Know the directory in which the file is stored.

DATA : lv_dir TYPE eps2filnam.

lv_dir   = '/VTS/DEV/'. 

Step 2: Get the list of files in that Directory path using the Function Module   EPS2_GET_DIRECTORY_LISTING

DATA it_files TYPE TABLE OF eps2fili.

  CALL FUNCTION 'EPS2_GET_DIRECTORY_LISTING'
    EXPORTING
      iv_dir_name                  = lv_dir
    tables
      dir_list                     = it_files
   EXCEPTIONS
     INVALID_EPS_SUBDIR           = 1
     SAPGPARAM_FAILED             = 2
     BUILD_DIRECTORY_FAILED       = 3
     NO_AUTHORIZATION             = 4
     READ_DIRECTORY_FAILED        = 5
     TOO_MANY_READ_ERRORS         = 6
     EMPTY_DIRECTORY_LIST         = 7
     OTHERS                       = 8.

Step 3: Find the file in that list using its name or date(or any indicator to identify the file given by the user)

DATA : p_file_n TYPE localfile .

  READ TABLE it_files INTO DATA(wa_files) INDEX 1. "reading recent file
  IF sy-subrc = 0.
     p_file_n = wa_files-name.
  ENDIF.

Step 4: Use OPEN DATASET keyword to open the file. Use READ DATASET to read each record in the file. CLOSE DATASET to close the file. Store each line item in an internal table.

DATA: BEGIN OF it_tab OCCURS 0,
        rec(1000) TYPE c,
      END OF it_tab.
DATA: wa_tab(1000) TYPE c.

  OPEN DATASET p_file_n FOR INPUT IN TEXT MODE ENCODING NON-UNICODE.
  IF sy-subrc = 0.
    DO.
      READ DATASET p_file_n INTO wa_tab.
      IF sy-subrc <> 0.
        EXIT.
      ENDIF.
      it_tab-rec = wa_tab.
      APPEND it_tab.
    ENDDO.
  ENDIF.
  CLOSE DATASET p_file_n.

Step 5: Now the excel data is stored in an internal table which can be uploaded into any table of the same structure.

MODIFY ztest FROM it_Tab.

 

Conclusion:

I hope this blog post helped you to get an idea on how to read an excel from the Application Server and store it in an internal table which can further be used to update a table. Apart from Excel, PDF, as well as an Image, can also be read from the Application Server. It can be stored in an XSTRING format.

2 Comments
You must be Logged on to comment or reply to a post.
  • This solution will not read data from a .xlsx or .xls file and allow its manipulation. For that you need abap2xlsx. Or for the data to be saved as csv or tab delimited – which are text files, not Excel files.

    • Yes. This sample is for a .CSV file which is similar to an Excel file incase of reading from the Application Server. I will share another blog which explains that scenario too.