Upload Data from Excel File in ABAP using TEXT_CONVERT_XLS_TO_SAP
In this ABAP tutorial, ABAP developers can find code that shows how to upload data from Excel file to ABAP internal tables using TEXT_CONVERT_XLS_TO_SAP function call.
ABAP function module TEXT_CONVERT_XLS_TO_SAP is used for uploading from Excel data into SAP tables or ABAP internal tables.
To show how to upload from Excel data file, I have exported some data outside SAP system from MS SQL Server 2008 AdventureWorks database.
The vendor data from database is exported to Excel file and the below ABAP report/program will import or upload data from excel to ABAP internal table.
The UPLOAD_FROM_EXCEL sample ABAP program has a SELECTION-SCREEN where ABAP users or SAP users can select source file for Excel data.
The selection-screen uses cl_gui_frontend_services=>file_open_dialog method for displaying Windows type file exporer for file open dialog screen.
After the Excel file for data upload is selected within the file open dialog screen, the UploadExcelData form routine and DisplayInternalTableData form routine is executed in order.
UploadExcelData loads Excel data from selected Excel source file to target ABAP internal table using the ABAP TEXT_CONVERT_XLS_TO_SAP function call.
While TEXT_CONVERT_XLS_TO_SAP function call, it is important to set the internal table suitable for the source file.
For this reason, I have defined a custom type gty_Vendors in TYPES declaration section.
And a work area gs_Vendors and internal table gt_Vendors declarations are made using this global type.
After type declarations and data definitions are carried out, I used i_tab_converted_data property of TEXT_CONVERT_XLS_TO_SAP ABAP function to point to the target internal table for Excel upload process.
The DisplayInternalTableData form simply loops within the internal table and displays vendor information on screen using WRITE method.
REPORT Z_UPLOAD_FROM_EXCEL.
TYPE-POOLS : truxs.
TYPES :
BEGIN OF gty_Vendors,
VendorID TYPE I,
AccountNumber(20) TYPE C,
Name(50) TYPE C,
CreditRating(5) TYPE C,
PreferredVendorStatus(5) TYPE C,
ActiveFlag(5) TYPE C,
PurchasingWebServiceURL(40) TYPE C,
ModifiedDate(20) TYPE C,
END OF gty_Vendors.
DATA :
g_raw_data TYPE TRUXS_T_TEXT_DATA,
gs_Vendors TYPE gty_Vendors,
gt_Vendors TYPE TABLE OF gty_Vendors.
SELECTION-SCREEN BEGIN OF BLOCK BLOCK-1 WITH FRAME TITLE TEXT-001.
PARAMETERS : pa_file LIKE rlgrap-filename DEFAULT 'C:\excel.xls'.
" or CFFILE-FILENAME
SELECTION-SCREEN END OF BLOCK BLOCK-1.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_file.
PERFORM u_SelectFile USING pa_file.
START-OF-SELECTION.
PERFORM u_UploadExcelData.
PERFORM u_DisplayInternalTableData.
END-OF-SELECTION.
*&---------------------------------------------------------------------*
*& Form U_SELECTFILE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_PA_FILE text
*----------------------------------------------------------------------*
FORM U_SELECTFILE USING P_PA_FILE TYPE LOCALFILE.
DATA :
lv_subrc LIKE sy-subrc,
lt_it_tab TYPE filetable.
" Display File Open Dialog control/screen
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = 'Select Source Excel File'
default_filename = '*.xls'
multiselection = ' '
CHANGING
file_table = lt_it_tab
rc = lv_subrc.
" Write path on input area
LOOP AT lt_it_tab INTO p_pa_file.
ENDLOOP.
ENDFORM. " U_SELECTFILE
*&---------------------------------------------------------------------*
*& Form U_UPLOADEXCELDATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM U_UPLOADEXCELDATA .
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_line_header = 'X'
i_tab_raw_data = g_raw_data
i_filename = pa_file
TABLES
i_tab_converted_data = gt_Vendors[] " Data
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
ENDFORM. " U_UPLOADEXCELDATA
*&---------------------------------------------------------------------*
*& Form U_DISPLAYINTERNALTABLEDATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM U_DISPLAYINTERNALTABLEDATA .
WRITE : /
' VendorId' RIGHT-JUSTIFIED,
13 'AccountNumber',
34 'Name',
88 'CR' RIGHT-JUSTIFIED,
93 'PVS' RIGHT-JUSTIFIED,
101 'A' RIGHT-JUSTIFIED,
104 'URL',
144 'ModifiedDate'.
LOOP AT gt_Vendors INTO gs_Vendors.
WRITE : /
gs_Vendors-VendorId RIGHT-JUSTIFIED,
gs_Vendors-AccountNumber,
gs_Vendors-Name,
gs_Vendors-CreditRating RIGHT-JUSTIFIED,
gs_Vendors-PreferredVendorStatus RIGHT-JUSTIFIED,
gs_Vendors-ActiveFlag RIGHT-JUSTIFIED,
gs_Vendors-PurchasingWebServiceURL,
gs_Vendors-ModifiedDate.
ENDLOOP.
ENDFORM. " U_DISPLAYINTERNALTABLEDATA
I hope you find this ABAP Excel upload demo useful for your ABAP codes used in order to load excel data.
