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

[former_member677524]

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.

[abo]

bfeeb8ed7fa64a7d95efc21f74a8c135 how's november coming along, so far?

 

[former_member769204]

How you will exception , when Excel file will be very-very big ? It easy, may to crush process of you file copy..

[matt]

I think the magical appearance of lo_xlsx is a nice touch.

[former_member677524]

DATA : 

       iv_file  TYPE string.

iv_file = LV_FILE. " file path

DATA(lo_xls) = NEW cl_fdt_xl_spreadsheet( document_name = iv_file

                                          xdocument     = lv_xls_xstr ).

Sorry. This magical appearance comes from above code..:-P

[shais]

A good chance to remind the important warning of sandra.rossi regarding the class CL_FDT_XL_SPREADSHEET:

note 2468709 - Usage of standard class CL_FDT_XL_SPREADSHEET

[Sandra_Rossi]

I guess it's best to fix the code directly in your blog post 😉

[christian_brhl]

Take a look at "cl_cts_language_file_io=>copy_files_local " which despite the "local" is an easy way to move files about the application server.

 

Below is code that works (' iv_archived_file' variable is a parameter passed into the method).

You can also do away with the 'im'-type variables w/CONV if you like.

 

    DATA lv_name_with_ext     TYPE string.
DATA im_source_file       TYPE epsfilnam.  "TEXT40
DATA im_source_directory  TYPE epsdirnam.
DATA im_target_file       TYPE epsfilnam ##NEEDED.
DATA im_target_directory  TYPE epsdirnam.
************************************************************************
lv_name_with_ext = iv_archived_file.
REPLACE ALL OCCURRENCES OF REGEX '[\w:\\,\\,\/][\w,\s,[:punct:]]*[\\,\/]' IN lv_name_with_ext WITH ''.
im_source_file       = lv_name_with_ext.
im_source_directory  = iv_source_directory.
im_target_directory  = me->mv_archive_directory.
cl_cts_language_file_io=>copy_files_local(
EXPORTING
im_source_file           = im_source_file
im_source_directory      = im_source_directory
im_target_file           = im_source_file
im_target_directory      = im_target_directory
EXCEPTIONS
open_input_file_failed   = 1
open_output_file_failed  = 2
write_block_failed       = 3
read_block_failed        = 4
close_output_file_failed = 5
OTHERS                   = 6 ) ##SUBRC_OK.
IF sy-subrc = 0 ##NEEDED.
ENDIF.
TRY.

IF if_input_file_flag   = abap_true AND
im_source_directory <> me->mv_archive_directory.
DELETE DATASET iv_archived_file.
ENDIF.  "if if_input_file_flag = abap_true and...
CATCH cx_sy_file_authority ##NO_HANDLER.
ENDTRY.
ENDMETHOD.  "archive_file

[hardyp180]

OK Off I go.

What Andrea is alluding to is the fact that since 2010 every single month - and often many times each month - someone posts a blog like this in regard to uploading/downloading Excel files to and from ABAP.

Each time someone like me points the blog poster to ABAP2XLSX. https://github.com/abap2xlsx/abap2xlsx

Every month I have to do this at least once. Every month for twelve years and it is NEVER GOING TO END

Or can I? Can I stop this? I always have about ten things on the boil at once and on my plan is that in the near future I am going to fight back by posting an ABAP2XLSX blog each month. I think I will go through all the example programs one by one saying in minute detail how they work. This because many people claim they are too difficult to understand.

So, I am making a bet with myself. The next time a blog like this comes out - which could very well be tomorrow - I have to publish an ABAP2XLSX blog the next day.

[abo]

A claim which might have some basis to it is that the documentation of the project is somewhat lacking, a claim I almost but not completely disagree with. Therefore, we gladly take whatever help we can get in terms of documentation and publicity.

[jpsapabap21]

Hi bfeeb8ed7fa64a7d95efc21f74a8c135

I had also posted one on uploading excel to the application server using the SALV and related classes. The reason I had to use that method was the ABAP2XLSX was not allowed by the client.

The project has an associated code samples library but it is a bit difficult to understand. If you can really post the ABAP2XLSX blog each month - it will definitely help.

I was also planning to do the same on another portal - DiscoveringABAP and have posted about the non-ABAP2XLSX methods but always mentioned that ABAP2XLSX is the way to go forward. This is the first post in the series of my attempt to understand and use ABAP2XLSX

• ABAP Code Sample : ABAP2XLSX project

I still have to get around to posting about the samples and how they work.

As I mentioned, it would be really helpful if you are going to do this - I hope it will be okay if I add links to your blog posts in my attempts.