Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

ALV standard checks are not working for uploaded data through excel.

Sagar47
Participant
0 Kudos

I've added a custom button to the toolbar for uploading an Excel file and displaying its content in an ALV grid. Upon clicking 'save,' the data from the Excel file gets appended to the main data dictionary table. However, certain fields, with fixed values defined through domains, pose an issue. The ALV fails to validate incorrect cell values that don't align with the defined values through domains. Interestingly, manually inserting a row and adding data triggers an error log for any discrepancies.

Note: I'm using a dynamic table because the program adds data to a custom table through an Excel file.

>Below code to transfer the excel data to dynamic internal table <all_table_new>, and in ALV display I'm using this internal table to Display records.

FORM excel_upload .

DATA: lv_file_path TYPE ibipparms-path,
lv_filename TYPE string,
lv_program TYPE sy-repid,
lv_dynpro TYPE sy-dynnr,
lv_field_name TYPE dynpread-fieldname.

lv_program = sy-repid.
lv_dynpro = sy-dynnr.

CALL FUNCTION 'F4_FILENAME'
EXPORTING
program_name = lv_program
dynpro_number = lv_dynpro
field_name = lv_field_name
* FILE_FILTER = 'Excel Files (*.xlsx)|*.xlsx|All Files (*.*)|*.*'
IMPORTING
file_name = lv_file_path
EXCEPTIONS
no_filename_selected = 1
OTHERS = 2.

IF sy-subrc = 0.
lv_filename = lv_file_path.

*****************EXCEL TO INTERNAL TABLE***************
DATA: gv_file TYPE rlgrap-filename.
gv_file = lv_filename.

REFRESH it_excel.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = gv_file
i_begin_col = 1
i_begin_row = 2
i_end_col = 10
i_end_row = 100
TABLES
intern = it_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.

IF it_excel IS INITIAL.
MESSAGE i302(ztdcv1) DISPLAY LIKE 'E'. "Please try again
CALL SCREEN 0500.
ENDIF.


DATA: lv_max_col_count TYPE i,
lv_max_cat_count TYPE i.

lv_max_col_count = 0.

***********checking column number in excel data********
LOOP AT it_excel INTO wa_excel.
IF wa_excel-col > lv_max_col_count.
lv_max_col_count = wa_excel-col.
ENDIF.
ENDLOOP.

lv_max_col_count = lv_max_col_count + 1. " +1 to skip 'MANDT' field
lv_max_cat_count = lines( it_cat_new ) - 2. "-2 to skip 'MANDT' and 'Line_index' fileds

IF lv_max_col_count <> lv_max_cat_count.
MESSAGE i300(ztdcv1). " column mismatch in table structure and excel data
CALL SCREEN 0500.
ELSE.

SORT it_excel BY row col.

CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = it_cat
IMPORTING
ep_table = DATA(ldtab_new).


ASSIGN ldtab_new->* TO <excel_table>.


FIELD-SYMBOLS: <excel_wa> TYPE any, " work area of <excel_table>
<fs1> TYPE any.

FIELD-SYMBOLS: <fs_line_index> TYPE any.

DATA new_line TYPE REF TO data.

DATA: lv_row TYPE i,
lv_col TYPE i,
lv_max_col TYPE i,
lv_current_row TYPE i,
lv_line_num TYPE i.

lv_line_num = 1.


******Looping it_excel to get records from excel file**************
LOOP AT it_excel INTO wa_excel.

lv_max_col = lines( it_cat ).

lv_row = wa_excel-row.
lv_col = wa_excel-col.
lv_col = lv_col + 1.

IF lv_col <= lv_max_col.
DATA(lv_fieldname) = it_cat[ lv_col ]-fieldname.

IF lv_row > lv_current_row.
IF <excel_wa> IS ASSIGNED.

ASSIGN COMPONENT 'LINE_INDEX' OF STRUCTURE <excel_wa> TO <fs_line_index>.
IF sy-subrc = 0.
<fs_line_index> = lv_line_num.
lv_line_num = lv_line_num + 1.
ENDIF.

INSERT <excel_wa> INTO TABLE <excel_table>.
ENDIF.

CREATE DATA new_line LIKE LINE OF <excel_table>.
ASSIGN new_line->* TO <excel_wa>.
lv_current_row = lv_row.
ENDIF.

ASSIGN COMPONENT lv_fieldname OF STRUCTURE <excel_wa> TO <fs1>.
IF sy-subrc = 0.
<fs1> = wa_excel-value.
UNASSIGN <fs1>.
ENDIF.
ENDIF.

AT LAST.

ASSIGN COMPONENT 'LINE_INDEX' OF STRUCTURE <excel_wa> TO <fs_line_index>.
IF sy-subrc = 0.
<fs_line_index> = lv_line_num.
lv_line_num = 1.
ENDIF.

INSERT <excel_wa> INTO TABLE <excel_table>.
ENDAT.

ENDLOOP.



**********Assiging <excel_table> data to <all_table_new>*******
FIELD-SYMBOLS: <wa_all_table_new> TYPE any.
CREATE DATA new_line LIKE LINE OF <all_table_new>.
ASSIGN new_line->* TO <wa_all_table_new>.
REFRESH <all_table_new>.

LOOP AT <excel_table> INTO <excel_wa>.
MOVE-CORRESPONDING <excel_wa> TO <wa_all_table_new>.

INSERT <wa_all_table_new> INTO TABLE <all_table_new>.

CLEAR <wa_all_table_new>.
CLEAR <excel_wa>.
ENDLOOP.

ENDIF.
ENDIF.


ENDFORM.

>Below code to display ALV

MODULE display_data_0500 OUTPUT.

CLEAR event_new.

DATA : lwa_layout_0500 TYPE lvc_s_layo.

DATA : wa_stable TYPE lvc_s_stbl.


IF gt_cust_new IS INITIAL.

CREATE OBJECT gt_cust_new
EXPORTING
container_name = cust_new.

CREATE OBJECT gt_grid_new
EXPORTING
i_parent = gt_cust_new.

CLEAR lwa_layout_0500.
lwa_layout_0500-cwidth_opt = 'X'.

CALL METHOD gt_grid_new->set_table_for_first_display
EXPORTING
is_layout = lwa_layout_0500
it_toolbar_excluding = it_exclude_0500
CHANGING
it_fieldcatalog = it_cat_new
it_outtab = <all_table_new>.



IF event_new IS INITIAL.
CREATE OBJECT event_new.
ENDIF.

gt_grid_new->register_edit_event( i_event_id = cl_gui_alv_grid=>mc_evt_enter ).
gt_grid_new->register_edit_event( i_event_id = cl_gui_alv_grid=>mc_evt_modified ).

SET HANDLER event_new->handle_data_changed_new FOR gt_grid_new.
IF p_text = 'X'.
SET HANDLER event_new->handle_toolbar_txt_upld FOR gt_grid_new.
ELSE.
SET HANDLER event_new->handle_toolbar_xls_upld FOR gt_grid_new.
ENDIF.
* SET HANDLER event_new->handle_toolbar_xls_upld FOR gt_grid_new.
SET HANDLER event_new->handle_user_command_new FOR gt_grid_new.

CALL METHOD gt_grid_new->set_toolbar_interactive.

ELSE.

CALL METHOD gt_grid_new->refresh_table_display.
CALL METHOD cl_gui_cfw=>flush.


* CALL METHOD gt_grid_new->set_table_for_first_display
* EXPORTING
* is_layout = lwa_layout_0500
* it_toolbar_excluding = it_exclude_0500
* CHANGING
* it_fieldcatalog = it_cat_new
* it_outtab = <all_table_new>.

CALL METHOD gt_grid_new->check_changed_data( ).


*******Implicit data insertion into 'gt_mod' as type 'I'********
*******since there's no user action involved in the ALV grid during Excel upload*************
FIELD-SYMBOLS: <fs_data> TYPE any,
<fs_val> TYPE any.
REFRESH gt_mod.

LOOP AT <all_table_new> ASSIGNING <fs_data>.


ASSIGN COMPONENT 1 OF STRUCTURE <fs_data> TO <fs_val>.

IF sy-subrc = 0.

gt_mod-alv_indx = <fs_val>.
gt_mod-indx = <fs_val>.
gt_mod-type = type_ins.
gt_mod-save = 0.
ENDIF.
APPEND gt_mod.
UNASSIGN <fs_val>.

ENDLOOP.
ENDIF.


ENDMODULE.
1 ACCEPTED SOLUTION

Sandra_Rossi
Active Contributor

Test program to check the domain fixed values and foreign keys of any table/structure field, generically via DDUT_INPUT_CHECK:

REPORT.
PARAMETERS p_table TYPE tabname DEFAULT 'SFLIGHT'.
PARAMETERS p_field TYPE fnam_____4 DEFAULT 'CARRID'.
PARAMETERS p_value TYPE string DEFAULT '??'.

START-OF-SELECTION.
  DATA msgid TYPE symsgid.
  DATA msgty TYPE symsgty.
  DATA msgno TYPE symsgno.

  CALL FUNCTION 'DDUT_INPUT_CHECK'
    EXPORTING  tabname         = p_table
               fieldname       = p_field
               calling_program = sy-repid
               value           = p_value
    IMPORTING  msgid           = msgid
               msgty           = msgty
               msgno           = msgno
    EXCEPTIONS no_ddic_field   = 1
               illegal_move    = 2
               OTHERS          = 3.
  IF sy-subrc <> 0.
    WRITE: 'call failed'.
  ELSE.
    IF msgty CA 'AEX'.
      WRITE: / 'error', msgty, msgid, msgno.
    ENDIF.
  ENDIF.

NB: domain value tables are not checked, it checks only foreign keys.

18 REPLIES 18

Sandra_Rossi
Active Contributor

I can't read your code, it's just one line and it's full of < br >. Ctrl+Shift+V (unformatted paste) may help to avoid < br >.

Sandra_Rossi
Active Contributor

Please edit your question (Actions>Edit), select your code and press the button [CODE], which makes the code appear colored/indented, it'll be easier for people to look at it. Thanks!

Sandra_Rossi
Active Contributor

Don't use cl_alv_table_create=>create_dynamic_table as explained here:

Alternative to CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE | SAP Blogs

Sandra_Rossi
Active Contributor
0 Kudos

In the field catalog (not your code), you need to refer to the DDIC.

Sandra_Rossi
Active Contributor

Thanks for editing the format of your question.

It's still missing the information about your field catalog, which needs to refer to the DDIC.

Sagar47
Participant

I'm creating the field catalog 'it_cat' using this method, where 'p_table' represents the table name provided by the user from the selection screen. Please let me know if any further clarification needed.

Data: it_cat TYPE TABLE OF lvc_s_fcat. "Type of it_cat

  REFRESH it_cat.
CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
EXPORTING
i_structure_name = p_table
CHANGING
ct_fieldcat = it_cat
EXCEPTIONS
inconsistent_interface = 1
program_error = 2
OTHERS = 3.

Sagar47
Participant
0 Kudos

Now, refresh_table_display is working correctly. Previously, the column XYZSTYLEZYX was missing in <all_table_new>.

However, ALV is still unable to capture the wrong cell value that does not fall under the defined value through domains.

Sandra_Rossi
Active Contributor
0 Kudos

XYZSTYLEZYX doesn't concern your question.

Sagar47
Participant
0 Kudos

Got it, but any solution to fix the ALV standard validation issue?

Sagar47
Participant
0 Kudos

The aforementioned standalone program is not relevant to my issue. In my program also when a user manually modifies any cell value, ALV performs its standard checks. However, when we upload data from Excel and display it in ALV before saving it to the actual DDIC table, ALV fails to detect incorrect cell values that do not fall within the defined range.

Sandra_Rossi
Active Contributor
0 Kudos

My answer is not relevant. I'm deleting it.

Sandra_Rossi
Active Contributor
0 Kudos

Apologies. Your question is about validating the domain values in ALV. I think it's not part of standard checks, only foreign keys with check activated are part of standard checks.

Sagar47
Participant
0 Kudos

This code effectively conducts standard validation for both domain values and foreign keys with activated checks when manually entering or modifying data in ALV. However, it doesn't function when loading data programmatically.

Sandra_Rossi
Active Contributor
0 Kudos

Yes, the standard doesn't do any validation on the ALV internal table

  • when you pass it through set_table_for_first_display
  • when you change it directly by program

The validations are done by the ALV framework only if you make your ALV editable, and the user changes values on the screen, and only on fields where foreign keys have been defined with error check activated.

Sagar47
Participant
0 Kudos

Thank you for the clarification but is there a method or approach to programmatically trigger validation for the data loaded via the program itself? Essentially, is there a way to implement validation programmatically?

Sandra_Rossi
Active Contributor

I guess you mean a generic way to check domain values, foreign keys. You can find answers in the forum but don't mention ALV to search. It's not easy to find the right place → how to find the checktable assigned to a field dynamically | SAP Community.

Sagar47
Participant

Thank you, Sandra, for your consistent follow-up. With the provided FM, I am now able to validate the Excel data programmatically.

Sandra_Rossi
Active Contributor

Test program to check the domain fixed values and foreign keys of any table/structure field, generically via DDUT_INPUT_CHECK:

REPORT.
PARAMETERS p_table TYPE tabname DEFAULT 'SFLIGHT'.
PARAMETERS p_field TYPE fnam_____4 DEFAULT 'CARRID'.
PARAMETERS p_value TYPE string DEFAULT '??'.

START-OF-SELECTION.
  DATA msgid TYPE symsgid.
  DATA msgty TYPE symsgty.
  DATA msgno TYPE symsgno.

  CALL FUNCTION 'DDUT_INPUT_CHECK'
    EXPORTING  tabname         = p_table
               fieldname       = p_field
               calling_program = sy-repid
               value           = p_value
    IMPORTING  msgid           = msgid
               msgty           = msgty
               msgno           = msgno
    EXCEPTIONS no_ddic_field   = 1
               illegal_move    = 2
               OTHERS          = 3.
  IF sy-subrc <> 0.
    WRITE: 'call failed'.
  ELSE.
    IF msgty CA 'AEX'.
      WRITE: / 'error', msgty, msgid, msgno.
    ENDIF.
  ENDIF.

NB: domain value tables are not checked, it checks only foreign keys.