Skip to Content
Technical Articles
Author's profile photo chavva paddu

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.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo RUSHIKESH YEOLE
      RUSHIKESH YEOLE

      Conversion exits for standard data types should be enabled

      Author's profile photo Michelle Crapo
      Michelle Crapo

      First of all great job working on your first blog!  It's so hard to put technical things out there.

      Here are a couple suggestions:

      Use abap2xlxs

      This could be left up to the people reading this to add.

      • Check for table content in your program
      • Add the ability to modify/delete the entries
      • Ask for a confirmation
      • Add Error Checking

      I did a quick search and this is one of the older programs suggested - https://answers.sap.com/questions/3616515/program-to-update-a-z-table-dynamically.html

      I believe there might be more out there.  So a quick search could help with your blog as well.

      Again nice job and keep doing this!  It's so hard to put a purely technical blog out there.  I know I worry about it before I do.  100 developers = 100 different ways to do things.

      Author's profile photo S Abinath
      S Abinath

      Though its already discussed many times in the community by several members... it's a good effort made this is clearly explained find below link which has already explained the process...

      Data Migration: Using a Single program to Upload any database table

      Author's profile photo Matthew Billingham
      Matthew Billingham

      There's a bad error in your code. You've used the prefix L for global variables. Otherwise not bad. It should be noted that the FM you're using,

      ALSM_EXCEL_TO_INTERNAL_TABLE

      is not part of the BASIS layer, and as such does not exist in non ERP systems. E.g. BW.

      Author's profile photo Sercan Küçükdemirci
      Sercan Küçükdemirci

      Nice clickbait title, i thought you did something like external service implementation to update database table directly from Excel interface.