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

uploading data from XL sheet

Hi,

How to upload data from XL sheet to internal table , can u please provide the sample code.

thanks

krishna

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Jan 22, 2008 at 04:28 AM
    * types declaration of ty_tab
    TYPES : BEGIN OF ty_tab,
              bukrs TYPE anla-bukrs,
              anln1 TYPE anla-anln1,
              anln2 TYPE anla-anln2,
              anlkl TYPE anla-anlkl,
            END OF ty_tab.
    
    * data declaration for ty_tab
    DATA : it_tab TYPE TABLE OF ty_tab INITIAL SIZE 0,
           is_tab TYPE ty_tab.
    
    * data declaration to upload excel file to internal table
    DATA : it_data TYPE TABLE OF alsmex_tabline INITIAL SIZE 0,
           is_data TYPE alsmex_tabline.
    
    * selection screen
    SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
    PARAMETERS       : p_file TYPE rlgrap-filename OBLIGATORY.
    SELECTION-SCREEN : END OF BLOCK b1.
    
    * at selection screen on value request
    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
    START-OF-SELECTION.
    
    * sub routine to upload data from excel sheet to internal table
      PERFORM upload_data.
    
    *&---------------------------------------------------------------------*
    *&      Form  upload_data
    *&---------------------------------------------------------------------*
    *       text
    *----------------------------------------------------------------------*
    *  -->  p1        text
    *  <--  p2        text
    *----------------------------------------------------------------------*
    FORM upload_data.
      IF NOT p_file IS INITIAL.
        CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
             EXPORTING
                  filename                = p_file
                  i_begin_col             = 1
                  i_begin_row             = 1
                  i_end_col               = 256
                  i_end_row               = 65536
             TABLES
                  intern                  = it_data
             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.
      ENDIF.
    
      IF NOT it_data[] IS INITIAL.
        LOOP AT it_data INTO is_data.
    
          AT NEW row.
            CLEAR : is_tab.
          ENDAT.
    
          IF is_data-col = '001'.
            MOVE is_data-value TO is_tab-bukrs.
          ENDIF.
          IF is_data-col = '002'.
            MOVE is_data-value TO is_tab-anln1.
            CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
                 EXPORTING
                      input  = is_tab-anln1
                 IMPORTING
                      output = is_tab-anln1.
          ENDIF.
          IF is_data-col = '003'.
            MOVE is_data-value TO is_tab-anln2.
            CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
                 EXPORTING
                      input  = is_tab-anln2
                 IMPORTING
                      output = is_tab-anln2.
          ENDIF.
          IF is_data-col = '004'.
            MOVE is_data-value TO is_tab-anlkl.
            CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
                 EXPORTING
                      input  = is_tab-anlkl
                 IMPORTING
                      output = is_tab-anlkl.
          ENDIF.
    
          AT END OF row.
            APPEND is_tab TO it_tab.
          ENDAT.
          CLEAR : is_data.
    
        ENDLOOP.
      ENDIF.
    
    ENDFORM.                    " upload_data

    Regards

    Gopi

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 22, 2008 at 04:36 AM

    HI Krishna

    See this code where i had writen to UPLOAD A EXCEL SHEET AND again pass that to APPLICATION server

    i think it wil be very useful for you

    you can understand this very easily

    REPORT ZSD_EXCEL_INT_APP.

    parameter: file_nm type localfile.

    types : begin of it_tab1,

    f1(20),

    f2(40),

    f3(20),

    end of it_tab1.

    data : it_tab type table of ALSMEX_TABLINE with header line,

    file type rlgrap-filename.

    data : it_tab2 type it_tab1 occurs 1,

    wa_tab2 type it_tab1,

    w_message(100) TYPE c.

    at selection-screen on value-request for file_nm.

    CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

    EXPORTING

    • PROGRAM_NAME = SYST-REPID

    • DYNPRO_NUMBER = SYST-DYNNR

    • FIELD_NAME = ' '

    STATIC = 'X'

    • MASK = ' '

    CHANGING

    file_name = file_nm

    EXCEPTIONS

    MASK_TOO_LONG = 1

    OTHERS = 2

    .

    IF sy-subrc <> 0.

    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

    WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

    ENDIF.

    start-of-selection.

    refresh it_tab2[].clear wa_tab2.

    file = file_nm.

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

    EXPORTING

    filename = file

    i_begin_col = '1'

    i_begin_row = '1'

    i_end_col = '10'

    i_end_row = '35'

    tables

    intern = it_tab

    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.

    loop at it_tab.

    case it_tab-col.

    when '002'.

    wa_tab2-f1 = it_tab-value.

    when '004'.

    wa_tab2-f2 = it_tab-value.

    when '008'.

    wa_tab2-f3 = it_tab-value.

    endcase.

    at end of row.

    append wa_tab2 to it_tab2.

    clear wa_tab2.

    endat.

    endloop.

    data : p_file TYPE rlgrap-filename value 'TEST3.txt'.

    OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

    *--- Display error messages if any.

    IF sy-subrc NE 0.

    MESSAGE e001(zsd_mes).

    EXIT.

    ELSE.

    *---Data is downloaded to the application server file path

    LOOP AT it_tab2 INTO wa_tab2.

    TRANSFER wa_tab2 TO p_file.

    ENDLOOP.

    ENDIF.

    *--Close the Application server file (Mandatory).

    CLOSE DATASET p_file.

    loop at it_tab2 into wa_tab2.

    write : / wa_tab2-f1,wa_tab2-f2,wa_tab2-f3.

    endloop.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 22, 2008 at 08:53 AM

    Hi

    1) Upload file using this FM into internal table.

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

    EXPORTING

    filename = p_hfile

    i_begin_col = 1

    i_begin_row = 1

    i_end_col = 7

    i_end_row = 1000

    TABLES

    intern = itab_excel1

    EXCEPTIONS

    inconsistent_parameters = 1

    upload_ole = 2

    OTHERS = 3.

    IF sy-subrc <> 0.

    ENDIF.

    2) Format the input data

    LOOP AT itab_excel1 INTO wa_excel1.

    CASE wa_excel1-col .

    WHEN '1' .

    wa_vbak-auart = wa_excel1-value.

    WHEN '2'.

    wa_vbak-vkorg = wa_excel1-value.

    WHEN '3'.

    wa_vbak-vtweg = wa_excel1-value.

    WHEN '4'.

    wa_vbak-spart = wa_excel1-value.

    WHEN '5'.

    wa_vbak-bstkd = wa_excel1-value.

    WHEN '6'.

    wa_vbak-bstdk = wa_excel1-value.

    WHEN '7'.

    wa_vbak-kunnr = wa_excel1-value.

    ENDCASE.

    AT END OF row.

    CONDENSE :wa_vbak-auart,wa_vbak-vkorg,wa_vbak-vtweg,wa_vbak-spart,wa_vbak-bstkd,

    wa_vbak-bstdk,wa_vbak-kunnr.

    APPEND wa_vbak TO it_vbak.

    CLEAR wa_vbak.

    ENDAT.

    ENDLOOP.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 22, 2008 at 09:23 AM

    Hi,

    Try:

    REPORT ZUPLOAD.

    TYPE-POOLS: truxs.

    PARAMETERS: p_file TYPE rlgrap-filename.

    TYPES: BEGIN OF t_datatab,

    col1(30) TYPE c,

    col2(30) TYPE c,

    col3(4069) TYPE c,

    END OF t_datatab.

    DATA: it_datatab type standard table of t_datatab,

    it_words type table of tdline with header line,

    wa_datatab type t_datatab.

    DATA: it_raw TYPE truxs_t_text_data.

    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.

    START-OF-SELECTION.

    CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

    EXPORTING

    i_line_header = 'X'

    i_tab_raw_data = it_raw " WORK TABLE

    i_filename = p_file

    TABLES

    i_tab_converted_data = it_datatab[] "ACTUAL DATA

    EXCEPTIONS

    conversion_failed = 1

    OTHERS = 2.

    IF sy-subrc ne 0.

    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

    ENDIF.

    ***********************************************************************

    *END-OF-SELECTION.

    LOOP AT it_datatab INTO wa_datatab.

    WRITE:/ wa_datatab-col1,

    wa_datatab-col2,

    wa_datatab-col3.

    ENDLOOP.

    Regards,

    Gy

    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.