Skip to Content
author's profile photo Former Member
Former Member

Upload excel file into internal table and update the table

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Jun 17, 2008 at 02:54 AM

    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
    

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.