How to validate excel file data that is being uploaded into a table in sap?

[former_member807987]

How do I perform data validation on an excel file that is being uploaded into a table so that only valid records are updated? Right now my program sometimes leads to incorrect data updates into the custom table because the file data is not validated. I am using function module F4_FILENAME and TEXT_CONVERT_XLS_TO_SAP.

[Former Member]

Thank you for visiting SAP Community to get answers to your questions. Since this is your first question, I recommend that you familiarize yourself with: https://community.sap.com/resources/questions-and-answers, as the overview provides tips for preparing questions that draw responses from our members.

Should you wish, you can revise your question by selecting Actions, then Edit.

By adding a picture to your profile you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html

[former_member808116]

Hope this help you.

REPORT Z__TEST01.
INCLUDE ZIN_TEST01_TOP.
INCLUDE ZIN_TEST01.

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.
  SEARCH P_FILE FOR '.xlsx'.
  IF SY-SUBRC = 0.
    MESSAGE 'FileUpload is MS Excel' TYPE 'S'.
  ELSE.
    MESSAGE 'FileUpload must be MS Excel' TYPE 'E'.
  ENDIF.

[former_member9115]

Hi,

Following Simple example may help you.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
      field_name    = ' '
    IMPORTING
      file_name     = p_file.

START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_line_header        = 'X'
      i_tab_raw_data       = lt_raw
      i_filename           = p_file
    TABLES
      i_tab_converted_data = lt_input    "This LT_INPUT table to use for excel file data validation
    EXCEPTIONS
      conversion_failed    = 1
      OTHERS               = 2.

"Here validation for entered status in selection screen with excel field.
 LOOP AT lt_input ASSIGNING FIELD-SYMBOL(<lfs_input>).
      IF <lfs_input>-status NE 'A' AND <lfs_input>-status NE 'O'.
        MESSAGE 'Input File Status does not match with Seletion Criteria' TYPE 'S' DISPLAY LIKE 'E'.
       ENDIF.
ENDLOOP.

[venkateswaran_k]

Hi

Can you please update in more detail.

When you say, the data updated are wrong in your custom table. what is that? Invalid Master data?

So, do you want to validate those master data before it is updated in custom tables?

[former_member807987]

I am trying to check if the column names of the excel sheet are the same for every file uploaded to the database table. I want to make sure that every excel file I upload will have the same column names. For example,

Name employee ID employee desk

Jane 1234 019234

I want each excel file to have the same header column of name, employee ID, and employee desk.

[Sandra_Rossi]

Please explain what value you get from TEXT_CONVERT_XLS_TO_SAP (parameter i_tab_converted_data), also provide your code (type of internal table you pass in parameter i_tab_converted_data + any validation that you already implemented), what kind of validation you want to do, etc.