top of page
Search

SAP Excel Upload Program: What It Is and Why It Is Used

Introduction

In SAP systems, data entries are typically made manually through transaction codes. However, when dealing with large volumes of data, manual entry becomes time-consuming, error-prone, and inefficient. In such cases, Excel upload (uploading data from Excel) programs come into play. Users can automatically transfer data they have prepared into the SAP system.

In this article, we will discuss the need for uploading data via Excel files in SAP, the methods used, and an example scenario: Customer Master Data Update.


What is an Excel Upload Program?

An Excel upload program in SAP is a custom ABAP-based development that allows users to transfer data from prepared Excel files into the SAP system. These programs:

  • Read the Excel file into the SAP system,

  • Analyze and validate the data (e.g., are mandatory fields filled?),

  • Use necessary tables or BAPIs to save the data into the SAP system,

  • Provide meaningful messages for any incorrect entries.


Why Use an Excel Upload Program?


1. Bulk Data Entry:

For example, instead of manually entering hundreds of customer records, data can be uploaded within minutes using Excel.

2. User-Friendly Interface:

Even non-SAP users can easily input data in Excel.

3. Reduces Risk of Errors:

Pre-validation mechanisms can prevent incorrect or missing data entry.

4. Saves Time:

Significant time can be saved, especially in projects or during periodic data updates.

5. Version Control:

Uploaded Excel files can be stored, allowing for tracking of past data upload processes.



 

Scenario: Customer Master Data Update





In the rest of this article, we will go step by step through the following topics:


  • Preparing the Excel Template: Which fields will be included? Which fields are mandatory?


  • Developing the ABAP Upload Program:

    • Reading the Excel file (using FM)

    • Validating the data (validation checks)

    • Using BAPI_CUSTOMER_CHANGE or direct UPDATE to save the data to SAP

    • Logging and error messages


  • Testing Process and Considerations


Preparing the Excel Template

Before writing the Excel upload program, it is crucial to define the structure of the template. This is because the program will perform data reading, validation, and upload processes based on this template's structure.

In other words, the template acts as a "framework," and the program is designed around this framework. The fields included in the template directly determine which data fields in SAP will be updated. For example, in the customer master data update scenario, a template structure like the following may be needed:

Customer No

Company Code

Sales Area

Customer Group

Search Term

Tax Number

10000001

1000

10

01

TestCustomer

1234567890

10000002

1000

10

01

TestCustomer2

1234567891

The program will work based on the order and structure of these column headings. If the structure changes later, the program must be adapted accordingly.

Therefore, at the start of the project:

  • The data fields to be updated must be clearly defined,

  • These fields should be mapped to SAP tables,

  • The corresponding columns in Excel should be identified,

  • If necessary, a sample test file should be created with example data.

Correctly defining the template plays a critical role in ensuring both the program runs smoothly and that users do not make errors during data entry.


Technical Implementation Steps

3. Selecting the Excel File

In the SAP screen, the following function module is used to allow the user to select a file from their computer:


CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
CHANGING
    file_name     = p_ufile
  EXCEPTIONS
    mask_too_long = 1
    OTHERS        = 2.
IF sy-subrc <> 0.
  MESSAGE e004(zcustupd). " 'File selection failed'
ENDIF.

4. Transferring Excel Data to SAP

The data in the user-selected .xls file is transferred to SAP row by row using the following method:

DATA: lt_raw_data TYPE TABLE OF alsmex_tabline.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

  EXPORTING
    filename                = p_ufile
    i_begin_col             = 1
    i_begin_row             = 2
    i_end_col               = 10
    i_end_row               = 9999

  TABLES
    intern                  = lt_raw_data

  EXCEPTIONS
    inconsistent_parameters = 1
    upload_ole              = 2
    OTHERS                  = 3.

IF sy-subrc <> 0.
  MESSAGE e005(zcustupd) WITH 'File could not be read. The file might be open or in an invalid format.'.
  EXIT.
ENDIF.

5. Transferring Data to a Structured Table

The Excel data is stored in a temporary table called lt_raw_data at the cell level. Below is an example of how this data is converted into a customer data structure (lt_customer):




TYPES: BEGIN OF ty_customer,
         kunnr    TYPE kunnr,
         bukrs    TYPE bukrs,
         vkorg    TYPE vkorg,
         vtweg    TYPE vtweg,
         spart    TYPE spart,
         name1    TYPE name1_gp,
         stceg    TYPE stceg,
       END OF ty_customer.

DATA: lt_customer TYPE TABLE OF ty_customer,
      ls_customer TYPE ty_customer.
DATA: lv_row TYPE i VALUE 0.
SORT lt_raw_data BY row col.


LOOP AT lt_raw_data INTO DATA(ls_raw).
  IF lv_row IS INITIAL OR lv_row <> ls_raw-row.
    IF lv_row <> 0.
      APPEND ls_customer TO lt_customer.
      CLEAR ls_customer.
    ENDIF.
    lv_row = ls_raw-row.
  ENDIF.



  CASE ls_raw-col.
    WHEN 1. ls_customer-kunnr = ls_raw-value.
    WHEN 2. ls_customer-bukrs = ls_raw-value.
    WHEN 3. ls_customer-vkorg = ls_raw-value.
    WHEN 4. ls_customer-vtweg = ls_raw-value.
    WHEN 5. ls_customer-spart = ls_raw-value.
    WHEN 6. ls_customer-name1 = ls_raw-value.
    WHEN 7. ls_customer-stceg = ls_raw-value.
  ENDCASE.
ENDLOOP.



IF ls_customer-kunnr IS NOT INITIAL.
  APPEND ls_customer TO lt_customer.
ENDIF.

DELETE lt_customer WHERE kunnr IS INITIAL.

What Happens Next? In the next step:

  • Mandatory field validation on the lt_customer table data,

  • Check if the customer exists in SAP,

  • Update data in SAP (e.g., using BAPI_CUSTOMER_CHANGEFROMDATA1) are performed.

We can now proceed to the section on how to perform validation on the lt_customer content and how to report faulty rows to the user. Shall we continue?


Data Validation Process

Before transferring the data from Excel to SAP, it is essential to validate it through various checks. These steps help ensure data quality by detecting incorrect, incomplete, or system-incompatible data. Below are some examples of validation checks:

  • Mandatory Field Check: Are mandatory fields like customer number and company code left blank?

  • Data Format Check: Is the customer number composed of only digits? Is the tax number of the correct length?

  • System Comparison: Is the company code or sales organization in the file defined in the SAP system?

  • Duplicate Record Check: Is the same customer being uploaded multiple times?

  • Logical Data Check: Is the combination of company code and sales organization consistent within the same row?

These checks should be reported to the user with clear messages, and faulty rows should not be included in the upload process. This ensures that only validated and correct data is uploaded into the SAP system.

If you'd like, we can proceed to write the code for implementing these validation checks together. Are you ready?


SAP Data Update Process

Once the data has passed all the validation checks, it is ready to be transferred to the SAP system for updating. The customer master data update process is usually done through the following methods:

  • Using BAPIs (Business Application Programming Interfaces): SAP’s standard data update interfaces (BAPIs) provide a secure and sustainable way to update data. For instance, the BAPI_CUSTOMER_CHANGEFROMDATA1 function is commonly used for customer data updates.

  • CALL TRANSACTION Method: In some cases, data can be updated by executing transaction codes (e.g., XD02) in the background. However, this method offers less control compared to BAPIs.

  • Using Custom Z Tables: If there are custom developments specific to customer data, the data may first be written into a custom table, and the update process can then be triggered via a background job.

During the data update process:

  • Comparisons with existing records in SAP can be performed.

  • Feedback can be given to the user for faulty or unupdatable data.

  • Successful operations can be logged for tracking purposes.

After completing these steps, the customer data in the system will be successfully updated. It is also important to prepare a success/failure report at the end of the process for user satisfaction and tracking.


6. Providing the Excel Format to the User

In order for users to upload data in the correct format, a specific Excel template may need to be provided. This template can be made available to the user by adding a button on the program's Selection Screen, allowing them to download it. When this button is clicked, an example Excel template will open on the user's computer.



 DATA : lv_cellno TYPE i.

* start Excel
  CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
  go_exc->err_hdl( ).
  SET PROPERTY OF h_excel  'Visible' = 1.

* get list of workbooks, initially empty
  CALL METHOD OF
      h_excel
      'Workbooks' = h_mapl.

* add a new workbook
  CALL METHOD OF
      h_mapl
      'Add'  = h_map.

  lv_cellno = 1.

* output column headings to active Excel sheet
go_exc->fill_cell( EXPORTING iv_valuei =  1 iv_valuej =  lv_cellno iv_bold   =  1 iv_value  = 'MUSTERI_NO' ).
add 1 TO lv_cellno.
go_exc->fill_cell( EXPORTING iv_valuei =  1 iv_valuej =  lv_cellno iv_bold   =  1 iv_value  = 'SAP_CADDE' ).
add 1 TO lv_cellno.
go_exc->fill_cell( EXPORTING iv_valuei =  1 iv_valuej =  lv_cellno iv_bold   =  1 iv_value  = 'BİNA_NO' ).
add 1 TO lv_cellno.
go_exc->fill_cell( EXPORTING iv_valuei =  1 iv_valuej =  lv_cellno iv_bold   =  1 iv_value  = 'İLCE' ).
add 1 TO lv_cellno.
go_exc->fill_cell( EXPORTING iv_valuei =  1 iv_valuej =  lv_cellno iv_bold   =  1 iv_value  = 'İL' ).
add 1 TO lv_cellno.


**********************************************************************

* disconnect from Excel
  FREE OBJECT h_excel.

  ENDMETHOD.                    "sample_template


  METHOD fill_cell.

  CALL METHOD OF
      h_excel
      'Cells' = h_zl
    EXPORTING
      #1      = iv_valuei
      #2      = iv_valuej.


  SET PROPERTY OF h_zl 'Value' = iv_value .
  GET PROPERTY OF h_zl 'Font' = h_f.
  SET PROPERTY OF h_f 'Bold' = iv_bold .

  ENDMETHOD.                    "fill_cell

  METHOD err_hdl.
    IF sy-subrc <> 0.
      WRITE: / 'OLE de hata:', sy-subrc.
*    STOP. "bunun yerine return parametre koy exit yap
    ENDIF.

  ENDMETHOD.                    "err_hdl

Conclusion

By providing an easy-to-use Excel template and ensuring proper validation and data mapping, the upload process becomes more efficient, reducing errors and saving time for the users. With a smooth integration into the SAP system, data management becomes more streamlined, enhancing overall operational efficiency.

 
 
 

Comments

bottom of page