ALSM_EXCEL_TO_INTERNAL_TABLE
目录
Roshan K
July 4, 2019 3 minute read
Excel Data to Internal table using ALSM_EXCEL_TO_INTERNAL_TABLE(obsolete) without additional code.
5116,455
WARNING: This post is intended to Excel upload FM which is now obsolete. Follow this, only if you are working on older systems to maintain the code where you’ve already used ‘ALSM_EXCEL_TO_INTERNAL_TABLE’.
Also this post will help you grasp concept of Field symbols with below application.
This blog post will help you avoid extraneous code every time you create Excel upload program by creating a simple Interface and a structure.
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.
LOOPCASE (column)
when 1. wa_itab-col1 = valuewhen 2. wa_itab-col2 = value.….and so on depending upon number of columns in Excel
ENDCASEAT _END OF_ROW
APPEND wa_itab to itab.END ATENDLOOP.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_tableEXPORTING
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.
FollowLikeRSS Feed
Alert Moderator
Assigned Tags
ABAP Development
ALSM_EXCEL_TO_INTERNAL_TABLE
field symbols
upload excel
Similar Blog Posts
if( !TLDR){ Upload .XLSX file to ZTable(Custom) on S4HANA Server }
By Sharath M GAug 28, 2019
Excel Upload and Download Program with multiple tabs from Fiori Launch Pad(Web GUI)
By Vijay ChintarlapalliApr 25, 2021
FM to retrieve data from excel into an internal table of any type
By Joshua GnanarajFeb 03, 2020
Related Questions
how to use TEXT_CONVERT_XLS_TO_SAP
By Former MemberApr 24, 2014
Released FM/Method to upload Excel
By Bhaskar NagulaJan 11, 2022
How to down load data from excel sheet into ABAP Program?
By Former MemberNov 15, 2006
5 Comments
Sandra Rossi
July 4, 2019 at 12:00 pm
There are many drawbacks with ALSM_EXCEL_TO_INTERNAL_TABLE
First, the limits explained in the SAP note 933420 - ALSM_EXCEL_TO_INTERNAL_TABLE:
It's not released for customers
It's only available in a R/3 or S/4 system (i.e. doesn't work on all other ABAP-based softwares)
The output structure is limited to 9999 rows and columns
The output structure is limited to 50 characters per cell.
Second, the limits because it's based on OLE
It's very slow (especially because it doesn't use Desktop Office Integration)
It cannot run in background (or any process not connected to a SAP GUI)
Workaround ---> use abap2xlsx (it's been discussed a lot in the forum)
Like 5
Reply
Alert Moderator
Share
Paul Hardy
July 4, 2019 at 12:50 pm
It would not be a normal week on the SAP Community if at least one person did not post a blog with some code they have come up with to do the same thing as ABAP2XLSX.
Like 5
Reply
Alert Moderator
Share
Peter Inotai
July 4, 2019 at 1:12 pm
#YAA2XB
(yet another ABAP to XLS blog)
Like 1
Reply
Alert Moderator
Share
小刚 哥哥
July 4, 2019 at 3:52 pm
very good , very helpfull for us.
Like 0
Reply
Alert Moderator
Share
春 余
May 19, 2021 at 7:07 am
This is very useful, but in a call in "do while", the task manager will have multiple Excel.exe processes with the number of calls.I tried using "CALL METHOD OF workbook 'CLOSE' " before the "FREE object", but it didn't work. How can I kill the Excel.exe process?
Like 0
Reply
Alert Moderator
Share
