Skip to Content
Technical Articles

Dynamically Update Data From Excel To Database Table

Introduction

I got the requirement to update the database table using the excel sheet data provided by the end-user. End-user will provide different database table names and the corresponding excel data, we need to upload that particular excel data into that corresponding database provided by the end-user.

Handling the field level validations i.e, the field order of that particular table with the field order of the excel data.

Here I am going to explain the step by step procedure to update the database table based on the excel data dynamically.

Step 1:

Go to the T-code SE 38.

Step 2:

Give the program as “ZR_DYNAMIC_TABLE_UPDATE_EXCEL” and click on create button a pop up should be displayed, where we need to provide the title asDynamically Update The Excel Data To Database Table” and type as “Executable Program”, Then click on Save button a pop up will be displayed.

 

Step 3: Need to provide the package name and click on the continue button.

Here we need to write the source code.

SOURCE CODE:

REPORT zr_dynamic_table_update_excel NO STANDARD PAGE HEADING.
*** --- Data Declarations
DATA:lt_excel       TYPE TABLE OF alsmex_tabline,
     lt_dref        TYPE REF TO data,
     ls_dref        TYPE REF TO data,
     lv_col         TYPE i,
     lo_alv         TYPE REF TO cl_salv_table,
     lt_table_filds TYPE TABLE OF dfies.
*** --- Field Symbols
FIELD-SYMBOLS : <fs_table> TYPE any .
FIELD-SYMBOLS : <ft_table> TYPE STANDARD TABLE.
FIELD-SYMBOLS : <dyn_field> .

*** --- Selection screen designing
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS:p_file  TYPE rlgrap-filename OBLIGATORY,
           p_table TYPE dd02l-tabname OBLIGATORY,
           p_test  AS CHECKBOX DEFAULT abap_true.
SELECTION-SCREEN END OF BLOCK b1.

*** --- value request for p_file
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    IMPORTING
      file_name = p_file.
*** --- START-OF-SELECTION
START-OF-SELECTION.
*create OBJECT lr_descr.
*** --- Assigning fields symbols for Tables
  CREATE DATA lt_dref TYPE TABLE OF (p_table).
  CREATE DATA ls_dref TYPE (p_table).
*** --- Assign field symbol with table type of DDIC
  ASSIGN lt_dref->* TO <ft_table>.
*** --- Assign field symbol with Structure type of DDIC
  ASSIGN ls_dref->* TO <fs_table>.
*** --- Call the Function module ALSM_EXCEL_TO_INTERNAL_TABLE
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = 99
      i_end_row               = 999999
    TABLES
      intern                  = lt_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc EQ 0.
*** --- Sort
    SORT lt_excel BY row.

    LOOP AT lt_excel INTO DATA(ls_excel).

*** --- Adding count to skip the mapping for MANDT field
      lv_col = ls_excel-col + 1.
      ASSIGN COMPONENT lv_col OF STRUCTURE <fs_table> TO <dyn_field>.
      IF sy-subrc = 0.
***-- Compare Excel sheet column data and Dynamic table fields
            IF ls_excel-row = 1.
              CALL FUNCTION 'DDIF_FIELDINFO_GET'
                EXPORTING
                  tabname        = p_table
                TABLES
                  dfies_tab      = lt_table_filds
                EXCEPTIONS
                  not_found      = 1
                  internal_error = 2
                  OTHERS         = 3.
              READ TABLE lt_table_filds INTO DATA(ls_table_fields) INDEX lv_col.
              IF sy-subrc = 0.
                IF ls_table_fields-fieldname NE ls_excel-value.
                  WRITE: 'Excel sheet data and Dynamic table fields are not matching'.
                  EXIT.
                ENDIF.
              ENDIF.
            ELSE.
              <dyn_field> = ls_excel-value.
            ENDIF.
      ENDIF.
      IF ls_excel-row GT 1.
        AT END OF row.
          APPEND <fs_table> TO <ft_table>.
          CLEAR <fs_table>.
        ENDAT.
      ENDIF.
    ENDLOOP.

    IF <ft_table> IS NOT INITIAL.
      IF p_test IS INITIAL.
*** --- Modify
        MODIFY (p_table) FROM TABLE <ft_table>.
        IF sy-subrc EQ 0.
          COMMIT WORK.
          DATA(lv_lines) = lines( <ft_table> ).
          WRITE: TEXT-002,lv_lines.
        ELSE.
          ROLLBACK WORK.
          MESSAGE TEXT-003 TYPE 'E' DISPLAY LIKE 'I'.
        ENDIF.
      ELSE.
*** --- Factory Method
        cl_salv_table=>factory(
          IMPORTING
            r_salv_table   =  lo_alv   " Basis Class Simple ALV Tables
          CHANGING
            t_table        = <ft_table>
        ).
*** --- Display
        lo_alv->display( ).
      ENDIF.
    ENDIF.
  ENDIF.

Step 4:

Before uploading the data, check the table entries.

Step 5:

Provide the file name, table name and check the test checkbox click on execute button.

Negative Test Case:

Uploading the excel with end-user preferred columns order, irrespective of the corresponding table fields.

Output:

We are validating the particular table fields order with the end-user provided excel fields order and throwing the error if both the fields differ.

Positive Test Case

Output:

The excel data will be as like as below.

Step 6:

Provide the file name, and table name and Uncheck the Test checkbox, click on the execute button.

Output:

Step 7:

Go to the T-code (SE16N), provide the table name as “ZMARA_TABLE1″, and click on execute button the updated data will be reflected the database table.

Conclusion:

By following the above steps, we have successfully updated the database table with the excel data.

Hope this will help.

Thanks for reading.

5 Comments
You must be Logged on to comment or reply to a post.