EXCEL FILE READ FROM APPLICATION SERVER, MOVE FROM ONE FOLDER TO ANOTHER FOLDER AND DELETE FROM FOLDER

BUSINESS REQUIRNMENT
Read Exel file from Application Server(T Code – AL11) put it to internal table , Move file from
one folder to other folder and delete file from folder.

EXCEL UPLOAD FORMAT DEMO

EXCEL FORMAT DEMO

APPLICATION SERVER DIRECTORY TO UPLOAD

AL11 PATH

This path provided by SAP-BASIS team . It can vary as per system like :

p_dir = ‘/usr/sap/attachments/’.

CODE LOGIC

To upload excel file in al11 first we have to create a structure as per excel format. In current scenario excel  contains eight columns so we first create a structure of eight fields and declare the data types .

DATA : LV_FILE TYPE EPS2FILNAM,
       p_file LIKE rlgrap-filename.
DATA : p_file_n TYPE localfile ,
       iv_file  TYPE string.
DATA: lt_dir1 TYPE TABLE OF eps2fili,
      wa_dir1 like LINE OF lt_dir1.
DATA : gs_final_t1 TYPE ty_daily,
       p_dir            TYPE salfile-longname,
       gt_final_t1 TYPE TABLE OF ty_daily.
DATA lv_return TYPE c.

TYPES: BEGIN OF ty_daily,
         col1 TYPE char18,
         col2 TYPE char18,
         col3 TYPE char18,
         col4 TYPE char18,
         col5 TYPE char18,
         col6 TYPE char30,
         col7 TYPE char18,
         col8 TYPE char18,
       END OF ty_daily.
DATA : BEGIN OF it_final occurs 0,
        a TYPE char20,
        b TYPE char20,
        c TYPE char20,
        d TYPE char20,
        e TYPE char20,
        f TYPE char30,
        g TYPE char20,
        h TYPE char20,
      END OF it_final.

Next we have to fetch the directory details of al11 using FM ‘EPS2_GET_DIRECTORY_LISTING’.

p_dir = ‘/usr/sap/otattachments/’. " al11 path
CALL FUNCTION 'EPS2_GET_DIRECTORY_LISTING'
  EXPORTING
    iv_dir_name = p_dir 

  TABLES
    dir_list    = lt_dir1
  .
IF sy-subrc <> 0.
* Implement suitable error handling here
else.
  DELETE lt_dir1 WHERE rc <> '0000'.
ENDIF.
READ TABLE lt_dir1 INTO wa_dir1 INDEX 1.
IF sy-subrc eq 0.
  p_file_n = wa_dir1-name.
CONCATENATE p_path '/' wa_dir1-name INTO p_file.
LV_FILE = P_FILE. "File name path create

Now next step is open the file in al11 and fill it in our string

OPEN DATASET lv_file FOR INPUT IN BINARY MODE .
IF sy-subrc EQ 0.
  READ DATASET lv_file INTO lv_xls_xstr.
*  lv_xls_xstr = wa_str.
  IF sy-subrc NE 0.
*    MESSAGE e002 WITH lv_file.
  ENDIF.
ELSE.
*  MESSAGE e001 WITH lv_file.
ENDIF.

After filling the data in string we have to close dataset.

CLOSE DATASET lv_file.

Now next and challenging part is to convert the excel data properly without picking garbage value in internal table use that we manipulate in program according to our business logic. Various methods are available in other blogs but I get success only using this method.

lo_xlsx->if_fdt_doc_spreadsheet~get_worksheet_names(
  IMPORTING
    worksheet_names = DATA(lt_excel)
).
"
LOOP AT lt_excel INTO DATA(ls_excel).
  DATA(ir_ref) = lo_xlsx->if_fdt_doc_spreadsheet~get_itab_from_worksheet( ls_excel  ) .
  ASSIGN ir_ref->* TO FIELD-SYMBOL(<lfs_data_tab>).
  MOVE-CORRESPONDING <lfs_data_tab> TO it_final[].
ENDLOOP.
“ EXCEL DATA POPULATE IN IT FINAL.

Now if we want to Move this file from one folder to other like we want to move the file to archive folder  we can use below method using keyword transfer

DATA: V_OLD type localfile,
            V_NEW type localfile,

                L_NEWLINE(240) type c.

v_old = lv_file.
P_path_arc = ‘/usr/sap/otattachments/arc’
OPEN DATASET v_old FOR INPUT IN BINARY MODE .
IF sy-subrc eq 0.
  CONCATENATE p_path_arc '/' wa_dir1-name INTO p_file_arc.
  LV_FILE_ARC = p_file_arc.
  v_new = LV_FILE_ARC.
  OPEN DATASET v_new FOR OUTPUT IN BINARY MODE .
  IF sy-subrc eq 0.
    DO.
                                READ DATASET v_old INTO l_newline.
                                IF sy-subrc EQ 0.
                                              TRANSFER l_newline  TO v_new.
                                ELSE.
                                              if l_newline is not initial.
                                                      TRANSFER l_newline TO v_new.
                                              endif.
                                              EXIT.
                                ENDIF.
                ENDDO.


  ENDIF.
ENDIF.
  CLOSE DATASET v_new.
CLOSE DATASET v_old.

Now we want to delete file from previous folder . Its very easy and simple just using keyword we can achieve  that.

DELETE DATASET v_old. " v_old is the path from where we want to delete file

Conclusion:

I hope this blog post will help you and get the idea how to read the excel file  from application server (AL11) ,copy and delete .

Please like and share feedback or thoughts in comment. Follow my page for this type of ABAP related topics. Also follow https://community.sap.com/topics/abap  to enhance  knowledge in ABAP.