How to Upload Data to SAP from Excel File using alsm_excel_to_internal_table Function Module
Frequently, ABAP developers and SAP users require data upload from Excel to SAP.
Data upload to SAP systems can be managed using third-party SAP tools or using built-in ABAP function modules like alsm_excel_to_internal_table.
alsm_excel_to_internal_table function module help ABAP developers to upload Excel to SAP internal tables in their ABAP codes.
In this ABAP tutorial, I'll try to show how data from Excel files can be upload to SAP internal tables with a sample ABAP report.
Here is a sample Excel data file which contains currency codes and currency names in two columns in an Excel spreadsheet as shown in the below screenhot.
Using ABAP function module alsm_excel_to_internal_table, we will upload Excel to SAP internal table and then list the contents of the ABAP internal table as an ABAP list.
Below ABAP programmers will see the output of the sample ABAP report.
Here is the ABAP source codes of the sample program ZUploadExcelData which reads an Excel file from a given path "C:\currency.xls"
ABAP ALSM_EXCEL_TO_INTERNAL_TABLE function module upload Excel to SAP internal table gt_exceldata.
Please note that gt_exceldata is a type table of alsmex_tabline.
After Excel data is uploaded from Excel to ABAP internal table of type alsmex_tabline, by using ABAP Loop command each column value is assigned to the desired work-area structure.
And after data assignment to work area structures are completed each structure or workarea is added to the desired ABAP internal table using APPEND command.
REPORT ZUploadExcelData .
TYPES :
BEGIN OF gty_currency,
currencycode(5) TYPE c,
currencyname(50) TYPE c,
END OF gty_currency.
DATA :
currline TYPE i,
gs_exceldata TYPE alsmex_tabline,
gt_exceldata TYPE TABLE OF alsmex_tabline,
gs_currency TYPE gty_currency,
gt_currency TYPE TABLE OF gty_currency.
* Import data from Excel
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = 'C:\currency.xls'
i_begin_col = 1
i_begin_row = 1
i_end_col = 2
i_end_row = 1000
TABLES
intern = gt_exceldata
* EXCEPTIONS
* INCONSISTENT_PARAMETERS = 1
* UPLOAD_OLE = 2
* OTHERS = 3
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
* Load data into internal table
currline = 0.
SORT gt_exceldata BY row value.
LOOP AT gt_exceldata INTO gs_exceldata.
IF currline = 0.
currline = gs_exceldata-row.
ENDIF.
IF currline <> gs_exceldata-row.
currline = gs_exceldata-row.
APPEND gs_currency TO gt_currency.
ENDIF.
CASE gs_exceldata-col.
WHEN 1.
gs_currency-currencycode = gs_exceldata-value.
WHEN 2.
gs_currency-currencyname = gs_exceldata-value.
WHEN OTHERS.
ENDCASE.
ENDLOOP.
* Display data
WRITE : /
'Code',
10 'Currency'.
LOOP AT gt_currency INTO gs_currency.
WRITE : /
gs_currency-currencycode UNDER 'Code',
gs_currency-currencyname UNDER 'Currency'.
ENDLOOP.
For ABAP developers who want to execute the given sample ABAP report for data upload from Excel to SAP, sample Excel source file currency.xls can be download from Download currency.xls
|