Import Excel into the SAP database table

How to import EXCEL into the SAP system can be implemented by calling the function alsm_excel_to_internal_table in the SAP executable program .

The following simple results are achieved:

Select File:

When you click Select, the file address is written to the address bar:

Last Click to execute:

The specific code sections are as follows:

*&---------------------------------------------------------------------**& Report&*&---------------------------------------------------------------------**&*&*&---------------------------------------------------------------------* Report****. tables:ztable001."Suppose we have two fields to upload: serial number, content. Then define the type, which contains two components, namely serial number and content "TYPES:BEGIN  ofTY_ZT, NUMTYPEZtable001-num,"Serial number"TxtTYPEZtable001-txt,"Content"  END  ofTy_zt."Defines the internal tables and workspaces that refer to the TY_ZT type for temporary access to the Excel content"Data:i_tableTYPETABLE ofTY_ZT, w_tableTYPETy_zt.

We need to build a transparent table, note that when defining the type, the component we refer to is the self-built transparent table component, which is the transparent table we have defined:

Note: When you build a table, the client field must have, followed by the primary key to check the "key" (shown).

"Define inner table for INSERT or modify transparent table content"Data:gt_tableTYPETABLE ofZTABLE001,"Direct reference transparent table"Gw_tableTYPEZTABLE001."SELECT Screen"Selection-screenBEGIN  of BLOCKB1 withFRAME TITLE text-001.Selection-screenBEGIN  ofLine. Selection-screen COMMENT1(9) STR1."Maintenance"PARAMETERS p_1 RADIOBUTTONGROUPG1DEFAULT ' X '."single-choice"Selection-screenEND  ofLine. Selection-screenBEGIN  ofLine. Selection-screen COMMENT1(9) STR2."Import"PARAMETERS p_2 RADIOBUTTONGROUPG1."single-choice"Selection-screen COMMENT -(2) STR3. PARAMETERS P_file ( the)."Address bar"Selection-screenEND  ofLine. Selection-screenEND  of BLOCKB1. Initialization. STR1 = ' maintenance '. STR2 = ' Import '.the import file Address bar is a parameter, where you choose to maintain or import two functions by using a RadioButton radio button. After selecting the screen to write, the next thing to use is only p_file this parameter has called the corresponding function. "

ON VALUE-REQUESTFOR P_FILE.  PERFORM OPEN_DIALOG."调用选择文件函数"

This part of the code is implemented when we click on the icon in the red circle:

Pop-Up selection dialog box ()

This is accomplished by the class method ( see the Code in Form Open_dialog ):
The class interface Cl_gui_frontend_services calls the method File_open_dialog.

At SELECTION-screen."for screen input inspection"IFP_2= ' X '.  IFP_file is INITIAL.MESSAGE' The file address cannot be empty! ' TYPE ' S 'DISPLAY like' E '.STOP.ENDIF.ENDIF.START-of-selection. IFP_1= ' X '.PERFORM Frm_call_view.ELSEIF p_2= ' X '.PERFORM UPLOAD.ENDIF.END-of-selection.FORM Frm_call_view."Maintenance View"DATA:P _tabnam like dd02v-tabname."Char (+)"P_tabnam= ' ZTABLE001 '.Call FUNCTION' View_maintenance_call '   "Invoke Maintenance View"Exporting ACTION= ' S 'View_name=P_tabnam"Table name"EXCEPTIONS client_reference= 1Foreign_lock= 2Invalid_action= 3No_clientindependent_auth= 4No_database_function= 5No_editor_function= 6No_show_auth= 7No_tvdir_entry= 8No_upd_auth= 9Only_show_allowed= TenSystem_failure=  OneUnknown_field_in_dba_sellist=  AView_not_found=  -maintenance_prohibited=  -OTHERS=  the          .IFSy-SUBRC <> 0.MESSAGE ID SY-msgid TYPESy-msgtyNumber SY-msgno   withSy-msgv1Sy-msgv2Sy-msgv3Sy-msgv4.ENDIF.EndForm.FORM Open_dialog."Select a file session. "DATA: lt_file_tableTYPEFILETABLE.DATA: lw_file_tableTYPEFile_table.DATA: L_RCTYPEI.Call METHOD cl_gui_frontend_services=File_open_dialog Exporting Window_title= ' Select File 'Default_filename=  ' *. XLSX '  "Default Excel File"Initial_directory=  ' D:\ '  "D drive is open by default, and can be empty by default"Multiselection=  "'   "File Selection"changing file_table=Lt_file_table RC=L_RC EXCEPTIONS file_open_dialog_failed= 1Cntl_error= 2Error_no_gui= 3Not_supported_by_gui= 4Others= 5          .CHECK L_RCEQ 1."Because the file is a single choice, so here to determine the number of choices is 1"READ TABLE lt_file_table INDEX1  intoP_file."Write the selected file address to the Address bar"EndForm.FORM UPLOAD."The last and most critical step""Defines the table in the table structure. "DATA: I_excelTYPETABLE of Alsmex_tabline,"Table structure. "W_excelTYPEAlsmex_tabline."Alsmex_tabline is a table row with Excel data, with three components, row col value. "DATA: Get_fileTYPERlgrap-filename."This refers to the structure field in the system. "MOVE P_file toGet_file."Assign the value of the address bar to Get_file"call this function to store the contents of Excel in a I_excel table in a similar coordinate format. "Call FUNCTION' alsm_excel_to_internal_table 'Exporting FILENAME=Get_file I_begin_col= 1I_begin_row= 1I_end_col= 8I_end_row= 65535TABLES INTERN=I_excel EXCEPTIONS Inconsistent_parameters= 1Upload_ole= 2OTHERS= 3            .DELETE I_excelWHEREROW= 1."Deletes the first row header. If there is no header in the Excel file, this sentence is not written. "IFI_excel is INITIAL.Call FUNCTION' Popup_to_confirm_with_message 'Exporting*Defaultoption= ' Y 'DIAGNOSETEXT1= ' data error '*DIAGNOSETEXT2= "'*DIAGNOSETEXT3= "'TEXTLINE1=  ' no content in Excel table! 'Titel= ' Tips ' . RETURN.ENDIF.Clear:i_table.FIELD-symbols:<Fs>."The use of internal SAP fields is covered here, please refer to the Weibo section for details. "SORT I_excel byROW COL.DATA: Num_colTYPEI.LOOPAt I_excel intoW_excel.Num_col=W_excel-col.ASSIGN COMPONENT Num_col of STRUCTURE w_table to <Fs>.    <Fs> =W_excel-value.At END of ROW.APPEND w_table toI_table.Clear:w_table.ENDAT.Clear:w_excel.Endloop.LOOPAt i_table intoW_table.Gw_table-mandt =Sy-mandt."Client number, create a transparent table that must contain the fields. "Gw_table-num =W_table-num.Gw_table-txt =W_table-txt.APPEND gw_table toGt_table.Clear:gw_table.Clear:w_table.Endloop."Update the contents of the database"MODIFY ZTABLE001 from TABLE gt_table."Use modify here, take into account additions and modifications, if the primary key exists is modified, otherwise added"IFSy-SUBRC = 0.COMMIT work andWAIT."Successful commit, or final program end system will also be automatically submitted" DATA: Num_tab (6)TYPEC.DESCRIBE TABLE gt_table LINES num_tab.Condense Num_tab NO-gaps. DATA: Str_line1TYPE STRING .Concatenate' successfully import data 'Num_tab' line '  intoStr_line1."read out the number of data bars in Excel for message hinting"Call FUNCTION' Popup_to_confirm_with_message 'Exporting DIAGNOSETEXT1= ' Success 'TEXTLINE1=Str_line1 Titel= ' Tips '            .  ELSE.ROLLBACK work."otherwise failed"ENDIF.EndForm.

In this way, a simple import program is complete.
It is necessary to note that using the Alsm_excel_to_internal_table function to import data, the maximum number of rows in an EXCEL file can only be 9999, and if the excess is not fully read into the inner table, the remaining rows will be lost.

Finally, when choosing the maintenance option, we want to be able to display the contents of the database table, and can implement additions and deletions to check and change. Here we are implementing the Maintenance view feature.
Subroutines in the code form Frm_call_view, call the function, but simply call the system function can not be completed, we also need to create a Data Table Maintenance dialog box.
After we have created the transparent table:

Click utility –> Table Maintenance Generator:

Here you maintain permission groups, function groups. Then maintain the screen number, which can be selected by system.
Finally click on the top left corner of a sheet of paper icon, you can.
In this way, we execute the program selection maintenance, click on the execution, the corresponding window will pop up.

Import Excel into the SAP database table