How to SAP Upload Excel File with TEXT_CONVERT_XLS_TO_SAP

SAP ABAP

Upload Excel File to internal table in SAP using the standard FM TEXT_CONVERT_XLS_TO_SAP is a common business requirement for fast upload data or mass data manipulation.

Find also a full ABAP Program, just copy and paste it and start playing with Excel.

TEXT_CONVERT_XLS_TO_SAP: Excel FM in SAP

The simplest way is to use standard function TEXT_CONVERT_XLS_TO_SAP

  • i_line_header: defines if the file has a header line (column for title)
  • i_filename : set the filename
  • i_tab_converted_data : is the internal table containing data from Excel file.

SAP Upload Excel File to ABAP Internal Table

Here a sample ABAP Program to upload Excel file to internal table in sap. It will take an Excel file as input and import the content to an ABAP internal table.

The step-step guide is :

  1. Call the Function F4_FILENAME: This function will help select the Excel file from your local driver.
  2. Call to the magical standard SAP TEXT_CONVERT_XLS_TO_SAP.
    Look above for the Function TEXT_CONVERT_XLS_TO_SAP signature and description.
    These function will return the data in Excel into SAP internal table.
  3. Display Excel content with a sample Write Structure.
    Do whatever you want with the data.

ABAP Report to Upload Excel File in SAP

Here a sample ABAP Report ready to be copied and which, hopefully, will help you as a skeleton for your Excel File processing in SAP.
In fact, this ABAP code will

  • upload an Excel File for Local (using the F4_FILENAME to select the Excel file)
  • and transfer all the Excel data to ABAP internal Table using TEXT_CONVERT_XLS_TO_SAP (to be displayed later).
TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE  rlgrap-filename.
PARAMETERS: p_head TYPE char01 DEFAULT 'X'.

TYPES: BEGIN OF t_datatab,
      col1(30)    TYPE c,
      col2(30)    TYPE c,
      col3(30)    TYPE c,
      END OF t_datatab.
DATA: it_datatab TYPE STANDARD TABLE OF t_datatab,
      wa_datatab TYPE t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

* At selection screen
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      field_name = 'P_FILE'
    IMPORTING
      file_name  = p_file.

START-OF-SELECTION.
  " Convert Excel Data to SAP internal Table Data 
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR        =
      i_line_header            =  p_head
      i_tab_raw_data           =  it_raw       " WORK TABLE
      i_filename               =  p_file
    TABLES
      i_tab_converted_data     = it_datatab[]  "ACTUAL DATA
   EXCEPTIONS
      conversion_failed        = 1
      OTHERS                   = 2.

  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.


***********************************************************************
* END-OF-SELECTION.
END-OF-SELECTION.
  " For sample, Excel Data transfered to internal table is displayed with write
  LOOP AT it_datatab INTO wa_datatab.
    WRITE:/ wa_datatab-col1,
            wa_datatab-col2,
            wa_datatab-col3.
  ENDLOOP.

Managing Excel file with ABAP: ABAP2XLSX

If you want to go further with Excel in SAP, have a look on the SAPLINK ABAP2XLSX. It is available in GitHub.
I have already used it with a productive project, and I was surprised with how many opportunities it gives.

For example, you can manage very easily the formatting of Cell with the ABAP2XLSX classes.
Handling Data to and from Excel, is a way more completed and easily using the predefined methods.