Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
k_sood
Active Participant
1,613
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

 

 

 

 

 

 
1 Comment
Labels in this area