SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow


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.

select-excel-file-for-abap-upload-data

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.

select-source-excel-file-for-abap-upload

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.

upload-excel-data-to-abap-internal-table





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
Code

I hope you find this ABAP Excel upload demo useful for your ABAP codes used in order to load excel data.



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.