Excel Data to Internal table using ALSM_EXCEL_TO_INTERNAL_TABLE without additional code

This blog post will help you avoid extraneous code every time you create Excel upload program by creating a simple Interface and a structure.

Before I came across FMs like TEXT_CONVERT_XLS_TO_SAP and SAP_CONVERT_TO_XLS_FORMAT, I used  ALSM_EXCEL_TO_INTERNAL_TABLE widely.
For ALSM_EXCEL_TO_INTERNAL_TABLE, explicitly we need to write code to transfer output of this FM into internal table of desired format. For this we have to map column number of FM output with columns of required internal table using

Refer below example.

CALL FUNCTION ‘ALSM_EXCEL_TO_INTERNAL_TABLE’
EXPORTING
filename            = p_file
i_begin_col        = 1
i_begin_row      = 1
i_end_col          = 9
i_end_row        = 65536
TABLES
intern                  = t_file
EXCEPTIONS
inconsistent_parameters = 1
upload_ole                      = 2
OTHERS                        = 3.

Output is in below format.

Further based on column number, work area is filled using CASE-ENDCASE and then Appending work area into required internal table in AT END OF ROW block.

LOOP

CASE (column)

when 1. wa_itab-col1 = value

when 2. wa_itab-col2 = value.

….and so on depending upon number of columns in Excel

ENDCASE

AT _END OF_ROW

APPEND wa_itab to itab.

END AT

ENDLOOP.

In order to avoid extraneous code when it comes to uploading Excel File with huge number of columns, create a FM with below code. Once you create Z FM, for every Excel File you’ll just need to create a structure in SE11  and you’re good to go.

Pass Desktop File path, Structure name in p_File and STR as input Parameters respectively.

First Create Structure in SE11 similar to the Excel File format.

FUNCTION zxls_into_it_2.

*”———————————————————————-

*”*”Local interface:

*”  IMPORTING

*”     REFERENCE(P_FILE) TYPE  RLGRAP-FILENAME

*”     REFERENCE(HEADER) TYPE  CHAR1 DEFAULT ‘X’

*”     REFERENCE(STR) TYPE  CHAR30

*”  TABLES

*”      IT_XLS_DATA TYPE  STANDARD TABLE

*”———————————————————————-

TYPE-POOLS: abap,slis.

FIELD-SYMBOLS:<dy_table> TYPE STANDARD TABLE,

<fs_current_field>,

<wa_str>,<wa_temp>

.

DATA:new_line    TYPE REF TO data,

new_line2 TYPE REF TO data,

li_fieldcat TYPE lvc_t_fcat,

dy_table    TYPE REF TO data,

t_file      TYPE STANDARD TABLE OF alsmex_tabline,

wa_file     TYPE alsmex_tabline,

v_col       TYPE i,

begin_row   TYPE i.

********This will provide Fields in Row format of Structure created(which should be similar to Excel Format).

CALL FUNCTION ‘LVC_FIELDCATALOG_MERGE’

EXPORTING

i_structure_name       = str

CHANGING

ct_fieldcat                  = li_fieldcat

EXCEPTIONS

inconsistent_interface = 1

program_error            = 2

OTHERS                    = 3.

IF sy–subrc EQ 0.

********This will create internal table which will have fields as per the Excel Format.

CALL METHOD cl_alv_table_create=>create_dynamic_table

EXPORTING

it_fieldcatalog           = li_fieldcat

i_length_in_byte          = ‘X’

IMPORTING

ep_table                  = dy_table

EXCEPTIONS

generate_subpool_dir_full = 1

OTHERS                    = 2.

IF sy–subrc EQ 0.

ASSIGN dy_table->* TO <dy_table>.

ENDIF.

******V_col will hold the number of Columns inside Excel File which is later passed to ALSM_EXCEL_TO_INTERNAL_TABLE’ .

DESCRIBE TABLE li_fieldcat LINES v_col.

******Logic to skip Header

IF header = ‘X’.

begin_row = 2.

ELSE.

begin_row = 1.

ENDIF.

******Core FM which will provide excel data in  t_file with row/coumn/value format

CALL FUNCTION ‘ALSM_EXCEL_TO_INTERNAL_TABLE’

EXPORTING

filename              = p_file

i_begin_col          = 1

i_begin_row        = begin_row

i_end_col            = v_col

i_end_row            = 65536

TABLES

intern                  = t_file

EXCEPTIONS

inconsistent_parameters = 1

upload_ole              = 2

OTHERS                  = 3.

IF sy–subrc EQ 0.

LOOP AT t_file INTO wa_file.

******This Block will create new area to store column wise data.<wa_temp> acts as a work area which is completely blank at start of new row

AT NEW row.

CREATE DATA new_line2 LIKE LINE OF <dy_table>.

ASSIGN new_line2->* TO <wa_temp>.

ENDAT.

*******for every iteration, wa_file-col will have increment till number of columns in Excel file and then reset to 1 when there’s new row i.e row = 1. (Refer example at end of blog)

ASSIGN COMPONENT wa_file–col OF STRUCTURE <wa_temp> TO <fs_current_field>.

IF sy–subrc EQ 0.

<fs_current_field> = wa_file–value.

UNASSIGN <fs_current_field>.

ENDIF.

*****Finally once all Columns are processed and new row is about to start, append the workarea into Internal table

AT END OF row.

APPEND <wa_temp> TO it_xls_data.

UNASSIGN <fs_current_field>.

UNASSIGN <wa_temp>.

ENDAT.

ENDLOOP.

ENDIF.

ENDIF.

ENDFUNCTION.

Explanation with example:

Now simply create a structure in SE11, define Internal table of that type in report and call above FM and pass the structure name and file path and its done.