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
No comments:
Post a Comment