Uploading excel file to database and modify the ztable without popup excel file while executing the program.

Hi All,

This blog post will explain to upload the excel sheet to database and modify the database ztable.

User wants to update multiple invoice numbers with single date and wants to update the invoice number with particular date. I have three fields i.e. invoice no, material no and date.

In the above excel from 401 to 404 records update with the 11.11.2021 and 405 would update with 10.11.2021.

Follow the below steps.

TYPES: BEGIN OF ty_upload,
         inv_no   TYPE zacc_date-inv_no,
         inv_date TYPE zacc_date-inv_date,
         inv_mat  TYPE zacc_date-inv_mat,
       END OF ty_upload.

DATA it_text TYPE truxs_t_text_data.

DATA: lt_upload TYPE TABLE OF ty_upload,
      wa_upload TYPE ty_upload,
      it_tab    TYPE TABLE OF zacc_date,
      it_tab1   TYPE TABLE OF zacc_date,
      wa_tab    TYPE zacc_date,
      p_scol    TYPE i VALUE '1',
      p_srow    TYPE i VALUE '1',
      p_ecol    TYPE i VALUE '3',
      p_erow    TYPE i VALUE '250'.
DATA:lt_intern TYPE  kcde_cells OCCURS 0 WITH HEADER LINE.
*parameter to upload the input file.
PARAMETERS: p_file TYPE rlgrap-filename.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
      field_name    = 'P_FILE'
    IMPORTING
      file_name     = p_file.

START-OF-SELECTION.
  IF p_file IS NOT INITIAL.
    CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
      EXPORTING
        filename                = p_file
        i_begin_col             = p_scol
        i_begin_row             = p_srow
        i_end_col               = p_ecol
        i_end_row               = p_erow
      TABLES
        intern                  = lt_intern
      EXCEPTIONS
        inconsistent_parameters = 1
        upload_ole              = 2
        OTHERS                  = 3.
* loop the intern table,in that have a col and value.
    LOOP AT lt_intern INTO DATA(ls_intern).
      IF ls_intern-col = 0001.
        wa_upload-inv_no = ls_intern-value.
      ENDIF.
      IF ls_intern-col = 0002.
        wa_upload-inv_mat = ls_intern-value.
      ENDIF.
      IF ls_intern-col = 0003.
        CONCATENATE ls_intern-value+6(4) ls_intern-value+3(2) ls_intern-value+0(2) INTO DATA(lv_date1).
        wa_upload-inv_date = lv_date1.
      ELSEIF ls_intern-col = 0003 AND wa_upload-inv_date = '00000000'.
        wa_upload-inv_date = lv_date1.
      ENDIF.
      IF wa_upload-inv_no IS NOT INITIAL AND wa_upload-inv_mat IS NOT INITIAL AND wa_upload-inv_date IS NOT INITIAL.
        APPEND wa_upload TO lt_upload.
      ENDIF.
      CLEAR:ls_intern,lv_date1.
    ENDLOOP.
*Get the data from db table.
    SELECT inv_no inv_mat inv_date FROM zacc_date INTO TABLE it_tab.
    SORT it_tab BY inv_no ASCENDING.
*To update the date based on invoice no and material no
    SORT lt_upload BY inv_no ASCENDING.
    LOOP AT lt_upload INTO wa_upload.
      READ TABLE it_tab INTO DATA(ls_data) WITH KEY inv_no = wa_upload-inv_no inv_mat = wa_upload-inv_mat.
      IF ls_data-inv_date IS NOT INITIAL OR ls_data-inv_date <> '00000000'.
        DATA(lv_date) = wa_upload-inv_date.
      ELSEIF ls_data-inv_date IS INITIAL OR ls_data-inv_date = '00000000' .
        ls_data-inv_date = lv_date.
      ENDIF.
      wa_tab-inv_no = wa_upload-inv_no.
      wa_tab-inv_mat = wa_upload-inv_mat.
      wa_tab-inv_date = lv_date.
      APPEND wa_tab TO it_tab1.
      CLEAR : ls_data,wa_tab.
    ENDLOOP.
* TO lock the table
    CALL FUNCTION 'ENQUEUE_E_TABLE'
      EXPORTING
*       MODE_RSTABLE   = 'E'
        tabname        = 'zacc_date'
      EXCEPTIONS
        foreign_lock   = 1
        system_failure = 2
        OTHERS         = 3.
* if condition satisfy modify the ztable.
    IF sy-subrc = 0.
      MODIFY zacc_date FROM TABLE it_tab1.
    ENDIF.
* TO unlock the table.
    CALL FUNCTION 'DEQUEUE_E_TABLE'
      EXPORTING
*       MODE_RSTABLE       = 'E'
        tabname = 'zacc_date'.
  ENDIF.
  IF sy-subrc = 0.
    MESSAGE 'DATA UPDATED SUCESSFULLY' TYPE 'S'.
  ENDIF.

Save and activate the program and click on execute.

Now execute the program and give the input file.

After given the input file click on execute. we will get the success message.

 Then check the table whether it’s updated or not.

Yes dates were updated as per our requirement.

Hope this blog post will help to you.

Thank you.

Siva Sidda.