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

[former_member774460]

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.