EXCEL FILE DATA UPLOAD & VALIDATION.

[Mohsin0076]

Hey experts,
I have one Excel sheet and I have to upload data from that sheet In SAP.
So in this case before uploading data in SAP how can I get the message that the data Is mismatched?
& Please help me with the procedure and (code).

[Sandra_Rossi]

The question has been asked one thousand times, what did you try? Clarify which kind of mismatch do you want to check? What Excel format is it? CSV, text with tab-delimited fields, XML 2003, old binary, Excel 2007?

[raymond_giuseppi · Accepted solution]

Basically, your program should

• Upload data from Excel to an internal table (FAQ)
• Check data (either your code or implicitly in the sap tool used: BAPI, IDOC, etc. - this part  depends on kind of data

What did you already do, where are you stuck?

[Mohsin0076]

Hi..

this is a  abap code...How to valid excel filed.....

REPORT zupload_data2.
TYPE-POOLS: slis,icon.
TABLES: ztb1fi,ztb1fi2.


TYPES: BEGIN OF  ty_ztb1fi,
        mandt    TYPE mandt,
       emp_id    TYPE zemp_id,
       emp_name  TYPE zemp_name,
       egender   TYPE ygender,
       eage      TYPE zdagee,
       icon       TYPE icon-id,                                                     """"""""""***types Declaration
       edob      TYPE zdob,
       emp_mob   TYPE zemp_mob,
       dep_location   TYPE zdep_location,
       dep_name       TYPE zdep_name,
       END OF    ty_ztb1fi.

TYPES: BEGIN OF ty_ztb1fi2,
       mandt        TYPE mandt,
       emp_id       TYPE zemp_id,
       dep_location TYPE zdep_location,
       dep_name     TYPE zdep_name,
       icon         TYPE icon-id,
       END OF   ty_ztb1fi2.

DATA: it_ztb1fi   TYPE STANDARD TABLE OF ty_ztb1fi,
      wa_upload   TYPE ty_ztb1fi,
      ls_cc_imp1  TYPE ty_ztb1fi,
      lt_cc_imp1  TYPE  TABLE OF ty_ztb1fi.
DATA: it_ztb1fi2  TYPE STANDARD TABLE OF ty_ztb1fi2,
      wa_ztb1fi2  TYPE ty_ztb1fi2.
DATA:   msg        TYPE string.
DATA: it_raw       TYPE truxs_t_text_data.                                       """"""""""" *** Data DDeclaration
DATA: it_fieldcat  TYPE slis_t_fieldcat_alv,
      wa_fieldcat  TYPE slis_fieldcat_alv,
      it_fieldcat1 TYPE slis_t_fieldcat_alv,
      wa_fieldcat1 TYPE slis_fieldcat_alv,
      lv_file      TYPE string.
DATA: repid        LIKE sy-repid.
DATA :wa_layout    TYPE slis_layout_alv.

PARAMETERS p_file TYPE rlgrap-filename.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'                                                              """"""""""""***CALL FUNCTION FOR F4
 EXPORTING

   program_name        = syst-cprog
   dynpro_number       = syst-dynnr
   field_name          = ' '
 IMPORTING
   file_name           = p_file.

END-OF-SELECTION.

*SET PF-STATUS 'AO1'.


CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
  EXPORTING
    i_field_seperator          = abap_true
    i_line_header              = abap_true                                 """"""""""""""""***CALL FUNCTION FOR CONVERT_EXCEL T0 SAP
    i_tab_raw_data             = it_raw
    i_filename                 = p_file
  TABLES
    i_tab_converted_data       = it_ztb1fi
 EXCEPTIONS
   conversion_failed           = 1
   OTHERS                      = 2
          .
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.


LOOP AT it_ztb1fi INTO wa_upload.
   ls_cc_imp1-mandt    = sy-mandt.
   ls_cc_imp1-emp_id   = wa_upload-emp_id.
   ls_cc_imp1-emp_name = wa_upload-emp_name.
   ls_cc_imp1-egender  = wa_upload-egender.
   ls_cc_imp1-eage    = wa_upload-eage.
   ls_cc_imp1-edob     = wa_upload-edob.
   ls_cc_imp1-emp_mob  = wa_upload-emp_mob.
   APPEND ls_cc_imp1 TO lt_cc_imp1.
   wa_ztb1fi2-mandt        = sy-mandt.
   wa_ztb1fi2-emp_id       = wa_upload-emp_id.
   wa_ztb1fi2-dep_location = wa_upload-dep_location.
   wa_ztb1fi2-dep_name     = wa_upload-dep_name.
   APPEND wa_ztb1fi2 TO it_ztb1fi2.

ENDLOOP.

   MODIFY ztb1fi FROM TABLE lt_cc_imp1.
           MESSAGE 'Data Has Been Uploaded Sucessfully' TYPE 'S'.
      MODIFY ztb1fi2 FROM TABLE it_ztb1fi2.
CLEAR : lt_cc_imp1.

SELECT * FROM ztb1fi INTO TABLE lt_cc_imp1.
  SORT:lt_cc_imp1 BY emp_id.
*  MESSAGE  W999(06) WITH MSG DISPLAY LIKE 'W'.


LOOP AT lt_cc_imp1 INTO wa_upload.
IF wa_upload-eage LE'30'.
  wa_upload-icon = '@08@'.
 ELSEIF wa_upload-eage BETWEEN '30' AND '50'.
   wa_upload-icon = '@09@'.
ELSEIF

  wa_upload-eage GT '50'.
  wa_upload-icon = '@0A@'.
   ENDIF.
* APPEND wa_upload TO lt_cc_imp1.
MODIFY lt_cc_imp1 FROM wa_upload TRANSPORTING icon.
CLEAR wa_upload.
ENDLOOP.

  IF sy-subrc = 0.

wa_fieldcat-col_pos       = '1'.
wa_fieldcat-tabname        = 'IT_UPLOAD'.
wa_fieldcat-fieldname      = 'EMP_ID'.                                                  """""""""""""""""""""""****field catalog
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-seltext_m      = 'Employee ID'.
wa_fieldcat-hotspot        = 'X'.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-col_pos        = '2'.
wa_fieldcat-tabname        = 'IT_UPLOAD'.
wa_fieldcat-fieldname      = 'EMP_NAME'.
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-seltext_m      = 'Employee Name'.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-col_pos        = '3'.
wa_fieldcat-tabname        = 'IT_UPLOAD'.
wa_fieldcat-fieldname      = 'EGENDER'.
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-seltext_m      = 'Employee Gender'.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-col_pos        = '4'.
wa_fieldcat-tabname        = 'IT_UPLOAD'.
wa_fieldcat-fieldname      = 'EAGE'.
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-seltext_m      = 'Employee Age'.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-col_pos        = '5'.
wa_fieldcat-tabname        = 'IT_UPLOAD'.
wa_fieldcat-fieldname      = 'ICON'.
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-icon           =   ''.
wa_fieldcat-seltext_m      = 'Employee light'.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-col_pos        = '6'.
wa_fieldcat-tabname        = 'IT_UPLOAD'.
wa_fieldcat-fieldname      = 'EDOB'.
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-seltext_m      = 'Employee DOB'.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-col_pos        = '7'.
wa_fieldcat-tabname        = 'IT_UPLOAD'.
wa_fieldcat-fieldname      = 'EMP_MOB'.
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-seltext_m      = 'Employee Mobile'.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
 EXPORTING
      i_callback_program             = SY-REPID
*   i_callback_program                = syst-cprog
  i_callback_pf_status_set          = 'PF_STATUS'
  is_layout                         = wa_layout
   i_structure_name                  = 'IT_UPLOAD'
   i_callback_user_command          = 'UCOMMAND'                              """""""""""""""""""*** CALL FUNCTION for display
   it_fieldcat                       = it_fieldcat
  TABLES
     t_outtab                          = lt_cc_imp1
      .
*ENDIF.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.

ENDIF.
FORM PF_STATUS_SET USING RT_EXTAB TYPE SLIS_T_EXTAB.
  SET PF-STATUS 'STATUS' EXCLUDING RT_EXTAB.
  ENDFORM.




FORM ucommand USING command LIKE sy-ucomm
                   selfield TYPE slis_selfield.                                                                    "    C LIKE SY-UCOMM
*   set PF-STATUS 'EMP_ID'.                                                                                     "   R_SELEFIELD  LIKE SLIS_SELFIELD.
  READ TABLE lt_cc_imp1 INTO ls_cc_imp1 INDEX selfield-tabindex.


  SELECT  mandt
          emp_id
          dep_location
          dep_name
        FROM ztb1fi2 INTO TABLE it_ztb1fi2
    WHERE emp_id = ls_cc_imp1-emp_id.

   IF lt_cc_imp1 IS NOT INITIAL.
      REFRESH :it_fieldcat1.

LOOP AT it_ztb1fi2  INTO  wa_ztb1fi2.
IF wa_ztb1fi2-dep_name ='IT'.
  wa_ztb1fi2-icon = '@08@'.
 ELSEIF wa_ztb1fi2-dep_name = 'MM'.
   wa_ztb1fi2-icon = '@09@'.
ELSEIF
  wa_ztb1fi2-dep_name ='GOOGLE'.
  wa_ztb1fi2-icon = '@0A@'.
   ENDIF.
* APPEND wa_upload TO lt_cc_imp1.
MODIFY it_ztb1fi2 FROM wa_ztb1fi2 TRANSPORTING icon.
CLEAR wa_ztb1fi2.
ENDLOOP.


wa_fieldcat-col_pos       = '1'.
wa_fieldcat-tabname        = 'IT_ZTB1FI2'.
wa_fieldcat-fieldname      = 'EMP_ID'.                                                  """""""""""""""""""""""****field catalog
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-seltext_m      = 'EMPLOYEE ID'.
wa_fieldcat-hotspot        = 'X'.
APPEND wa_fieldcat TO it_fieldcat1.
CLEAR wa_fieldcat.

wa_fieldcat-col_pos        = '2'.
wa_fieldcat-tabname        = 'IT_ZTB1FI2'.
wa_fieldcat-fieldname      = 'DEP_NAME'.
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-seltext_m      = 'EMP DEPRT'.
APPEND wa_fieldcat TO it_fieldcat1.
CLEAR:wa_fieldcat.

wa_fieldcat-col_pos        = '3'.
wa_fieldcat-tabname        = 'IT_ZTB1FI2'.
wa_fieldcat-fieldname      = 'DEP_LOCATION'.
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-seltext_m      = 'EMP LOCAT'.
APPEND wa_fieldcat TO it_fieldcat1.
CLEAR:wa_fieldcat.
* data :V_REPID = SY-REPID.

wa_fieldcat-col_pos        = '4'.
wa_fieldcat-tabname        = 'IT_ZTB1FI2'.
wa_fieldcat-fieldname      = 'ICON'.
wa_fieldcat-text_fieldname = ''.
wa_fieldcat-icon           =   ''.
wa_fieldcat-seltext_m      = 'Employee light'.
APPEND wa_fieldcat TO it_fieldcat1.
CLEAR wa_fieldcat.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
 i_callback_program                = syst-cprog
   i_structure_name                  = 'IT_ZTB1FI2'
   is_layout                         = wa_layout
   i_callback_pf_status_set        =  'PF_STATUS'
     i_callback_user_command          = 'UCOMMAND'
      it_fieldcat                    = it_fieldcat1
    TABLES
      t_outtab                       = it_ztb1fi2
    EXCEPTIONS
      program_error                  = 1
      OTHERS                         = 2.
  ENDIF.
ENDFORM.