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
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Upload excel file into internal table and update the table

Former Member

‎06-17-2008 12:54 AM

0 Kudos

Hi All,

I am having three fields on the selection screen like Promotion ID, Promotion Description and file upload. In the third field I am trying to upload an excel file which has 5 columns. How do I upload this file into the internal table and then further update the data in the corresponding tables. Here while uploading the file if any of the field data is null or blank then it should popup an error to the user stating one of the field data is null and should abort the process. If the data is present then it should update the data in the table. The 5 fieds in the excel file are ArticleNo, Colorcode, salesunit, salesqty and price. For the articleno and its corresponding colorcode I should update the salesunit, salesqty and price. The five fields are coming from the table wakt. For the first two fields on the selection screen the code I have written is as follows:

REPORT ZTest.

tables : wakh, wakt.

Parameter: PromoID type wakh-aktnr, PromoDec type wakt-aktkt, FileName type string.

AT selection-screen on PromoID.

select single * from wakh where aktnr = PromoID.

if sy-subrc EQ 0.

select aktkt from wakt into PromoDec where aktnr eq PromoID.

endselect.

else.

message A000(ZI) with 'Promotion ID is not Maintained.'.

endif.

Now I have to update my code for the third field on the report according to the logic I mentioned in the above lines. Please give me your expect guidance on this issue. Thanks one and all for your time.

Cheers

1 REPLY 1

former_member189059
Active Contributor

‎06-17-2008 3:54 AM

0 Kudos

Hello Cheng,

This code will partially fulfill your requirement

You can put the blank check in the subroutine INSERT_NEW_DATA

Replace mara with your table name wherever needed

The select-options in the selection screen allows you to choose the order

that means if the second field in the database table is the first field in your excel file, then enter it as follows

field2

field1

field3

field4

field5


*&---------------------------------------------------------------------*
*& Report  ZKRIS_UPLOAD_TABLE
*&---------------------------------------------------------------------*
*& Author  : Kris Donald
*& Date    : 23-05-2008
*& Purpose : Upload data to a table
*&---------------------------------------------------------------------*
*& Date Changed by Tag Description
*&
*&---------------------------------------------------------------------*

REPORT  zkris_upload_table.

DATA: lv_char25(25).

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE frttl1.
PARAMETERS: p_input TYPE rlgrap-filename DEFAULT
 'C:\test.xls' LOWER CASE.

PARAMETERS: p_scol TYPE i DEFAULT 1.
PARAMETERS: p_srow TYPE i DEFAULT 5.
PARAMETERS: p_ecol TYPE i DEFAULT 9.
PARAMETERS: p_erow TYPE i DEFAULT 35.

SELECT-OPTIONS s_order FOR lv_char25 NO INTERVALS OBLIGATORY.
SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE frttl2.
PARAMETERS: cx_del AS CHECKBOX.
PARAMETERS: cx_ins AS CHECKBOX.
SELECTION-SCREEN END OF BLOCK b2.

INITIALIZATION.
  frttl1 = 'Input Excel File'.
  frttl2 = 'Database data'.

START-OF-SELECTION.


  DATA: it_alsmex_tabline TYPE TABLE OF alsmex_tabline.
  DATA: wa_alsmex_tabline TYPE alsmex_tabline.

  DATA: it_zmara TYPE TABLE OF mara.
  DATA: wa_zmara TYPE mara.
  FIELD-SYMBOLS : <workarea1> TYPE ANY.

  DATA: lv_structname TYPE ddobjname VALUE 'MARA'.
  DATA: lv_waname TYPE ddobjname.
  CONCATENATE 'WA_' lv_structname INTO lv_waname.

  FIELD-SYMBOLS : <fs_waname> TYPE ANY.

  DATA: v_month TYPE string.
  DATA: v_day TYPE string.
  DATA: v_year TYPE string.
  DATA: lv_length TYPE i.

  DATA: it_months TYPE TABLE OF t247.
  DATA: wa_months TYPE t247.
  SELECT * FROM t247 INTO TABLE it_months
    WHERE spras = 'E'.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_input
      i_begin_col             = p_scol
      i_begin_row             = p_srow
      i_end_col               = p_ecol
      i_end_row               = p_erow
    TABLES
      intern                  = it_alsmex_tabline
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  WRITE:/ 'done'.

  PERFORM convert_2_table.
  PERFORM delete_current_data.
  PERFORM insert_new_data.

*&---------------------------------------------------------------------*
*&      Form  CONVERT_2_TABLE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM convert_2_table .

  DATA: it_fieldnames TYPE dfies OCCURS 0 WITH HEADER LINE.
  DATA: wa_fieldnames TYPE dfies.
  DATA: lv_firstfield TYPE dfies-fieldname.

  DATA: lv_row TYPE kcd_ex_row_n.
  FIELD-SYMBOLS <cell> TYPE ANY.
  DATA: lv_index LIKE sy-index.

  ASSIGN (lv_waname) TO <fs_waname>.

  CALL FUNCTION 'DDIF_FIELDINFO_GET'
    EXPORTING
      tabname        = lv_structname
    TABLES
      dfies_tab      = it_fieldnames
    EXCEPTIONS
      not_found      = 1
      internal_error = 2
      OTHERS         = 3.


  READ TABLE s_order INTO wa_fieldnames INDEX 1.
  lv_firstfield = s_order-low.

  lv_row = '0001'.
  LOOP AT it_alsmex_tabline INTO wa_alsmex_tabline.
    IF wa_alsmex_tabline-row <> lv_row.
      APPEND <fs_waname> TO it_zmara.
      CLEAR <fs_waname>.
      lv_row = wa_alsmex_tabline-row.
    ENDIF.

    READ TABLE s_order INDEX wa_alsmex_tabline-col.
    READ TABLE it_fieldnames INTO wa_fieldnames
      WITH KEY fieldname = s_order-low.

    ASSIGN COMPONENT sy-tabix OF STRUCTURE
      <fs_waname> TO <cell>.

*   put your date fields here
    IF wa_fieldnames-fieldname = 'DOB'.
      PERFORM date_field.
    ENDIF.

    <cell> = wa_alsmex_tabline-value.

  ENDLOOP.



ENDFORM.                    " CONVERT_2_TABLE

*&---------------------------------------------------------------------*
*&      Form  DATE_FIELD
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM date_field .
  SPLIT wa_alsmex_tabline-value AT '-' INTO v_day v_month v_year.

* for year
  lv_length = STRLEN( v_year ).
  IF lv_length = 2.
    CONCATENATE '19' v_year INTO v_year.
  ENDIF.

* for month
  TRANSLATE v_month TO UPPER CASE.
  READ TABLE it_months INTO wa_months WITH KEY ktx = v_month.

* for day
  lv_length = STRLEN( v_day ).
  IF lv_length = 1.
    CONCATENATE '0' v_day INTO v_day.
  ENDIF.

  CONCATENATE v_year wa_months-mnr v_day INTO wa_alsmex_tabline-value.
ENDFORM.                    " DATE_FIELD

*&---------------------------------------------------------------------*
*&      Form  DELETE_CURRENT_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM delete_current_data .
  IF cx_del = 'X'.
    DELETE FROM mara.
  ENDIF.
ENDFORM.                    " DELETE_CURRENT_DATA

*&---------------------------------------------------------------------*
*&      Form  INSERT_NEW_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM insert_new_data .
  IF cx_ins = 'X'.
    INSERT mara FROM TABLE it_zmara ACCEPTING DUPLICATE KEYS.
    IF sy-subrc = 0.
      WRITE:/ 'Data successfully uploaded to table :)'.
    ELSE.
      WRITE:/ 'Failed to upload data to table :('.
    ENDIF.
  ENDIF.
ENDFORM.                    " INSERT_NEW_DATA