In this blog, I will discuss about how to convert excel data from row format to column format.
Custom table records have to be updated from the excel file. The table contains 180 columns with one primary key.
The format of the excel file is as follows:
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 :
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.