Transposing excel rows into columns using field symbols

Introduction

In this blog, I will discuss about  how to convert excel data from row format to column format.

Business scenario

Custom table records have to be updated from the excel file. The table contains 180 columns with one primary key.

Custom table

The format of the excel file is as follows:

Excel format

There may be any number of records for one object depending upon the number of fields to be updated for that object.

Conversion of excel data to the table format

Data type to hold the excel data consists of three columns only as shown above in the excel .

ot_excl contains the excel data .

ot_vpemain contains the converted data in the db table format.

Conversion logic as follows:

 LOOP AT ot_excl INTO ls_excl.
      IF <ls_vpe_main> IS ASSIGNED.
        IF <ls_vpe_main>-rollnr EQ ls_excl-rollnr.
*        same rollnr
          lf_flag = 'X' .
        ELSE.
*        new rollnr
          CLEAR lf_flag.
        ENDIF.
      ENDIF.
      IF lf_flag NE 'X'.
*      Add entry for new rollnr in the internal table.
        APPEND INITIAL LINE TO ot_vpemain ASSIGNING <ls_vpe_main>.
        READ TABLE ot_main_old INTO <ls_vpe_main> WITH KEY rollnr = ls_excl-rollnr .
        ASSIGN COMPONENT ls_excl-fname OF STRUCTURE <ls_vpe_main> TO <ls_fval> .
        <ls_fval> = ls_excl-fval.
      ELSE.
*       rollnr entry exists in the internal table , further characteristics to be added .
        ASSIGN COMPONENT ls_excl-fname OF STRUCTURE <ls_vpe_main> TO <ls_fval> .
        <ls_fval> = ls_excl-fval.
      ENDIF.
 ENDLOOP.

Reference to the particular field of the target structure is obtained and the excel value is assigned to it. Executing this logic for the above shown excel file will result in two records as shown below :

Converted table

Conclusion

Converting columns to rows can also be acheived using the similar logic, just need to look for correct field references for the destination table.

If there are any questions , please comment below.

Br,

Ketan