Skip to Content
Technical Articles
Author's profile photo Ketan Sood

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%20table

Custom table

The format of the excel file is as follows:

Excel%20format

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%20table

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

 

 

 

 

 

 

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      There was a question posted on this some time ago, there were more solutions in the answers if anyone is curious: https://answers.sap.com/questions/756069/how-to-transpose-rows-to-columns-in-abap.html

      That question was in ALV context but I think the solution offered by Sandra Rossi was pretty much "all purpose".