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

Optimized logic to upload data from Excel

I need to upload data from excel to Internal Table and currently my logic works in this fashion:

1. Using Alsm_Excl_To_internal_table I upload data into it_excel

2. Then loop it_excel into wa_excel

3. Copy wa_excel into wa_tempdata

4. Case wa_tempdata-col.

when 1.

wa_data-fieldname = wa_tempdata-value.

5. at end of row.

append wa_data to it_data.

endloop.

Although this logic works fine the only problem is let us suppose there are 30 fields then I will have to write 30 when statements and assign values to fields.

So please let me know if there is any other alternative approaches.

Thanks.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 27, 2013 at 01:12 PM

    Hi,

    you cas see this example

    DATA: w_index TYPE sy-index.
    DATA: it_excel TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.
    FIELD-SYMBOLS: <fs> TYPE any.

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
    filename = p_file
    i_begin_col = '1'
    i_begin_row = '2'
    i_end_col = '256'
    i_end_row = '65536'
    TABLES
    intern = it_excel
    EXCEPTIONS
    inconsistent_parameters = 1
    upload_ole = 2
    OTHERS = 3.

    LOOP AT it_excel.
    MOVE it_excel-col TO w_index.
    ASSIGN COMPONENT w_index OF STRUCTURE it_tracciato TO <fs>.
    MOVE it_excel-value TO <fs>.

    AT END OF row.
    APPEND it_tracciato.
    CLEAR: it_tracciato.
    ENDAT.
    ENDLOOP.

    Cheers

    Ivan

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      TYPES: BEGIN OF ty_data,

      * data element: KTOKD

      ktokd_005(004),

      * data element: KUN16

      kunnr_001(016),

      * data element: VKORG

      vkorg_002(004),

      * data element: VTWEG

      vtweg_003(002),

      * data element: SPART

      spart_004(002),

      * data element: ANRED

      anred_006(015),

      * data element: NAME1_GP

      name1_007(035),

      * data element: SORTL

      sortl_008(010),

      * data element: STRAS_GP

      stras_009(035),

      * data element: ORT01_GP

      ort01_010(035),

      * data element: LAND1_GP

      land1_011(003),

      * data element: SPRAS

      spras_012(002),

      END OF ty_data.

      DATA: v_file TYPE string,

      p_skip TYPE n,

      wrk_row TYPE i,

      w_index TYPE sy-tabix.

      DATA: wa_data TYPE ty_data,

      it_data TYPE TABLE OF ty_data,

      it_excel TYPE STANDARD TABLE OF alsmex_tabline,

      wa_excel LIKE LINE OF it_excel,

      wa_return LIKE LINE OF it_excel.

      START-OF-SELECTION.

      PERFORM upload_data.

      PERFORM move_data.

      * PERFORM process_data.

      * IF NOT it_error IS INITIAL.

      * PERFORM display_errors.

      * ENDIF.

      *&---------------------------------------------------------------------*

      *& Form open_file

      *&---------------------------------------------------------------------*

      * text

      *----------------------------------------------------------------------*

      FORM open_file.

      CALL FUNCTION 'F4_FILENAME'

      * EXPORTING

      * PROGRAM_NAME = SYST-CPROG

      * DYNPRO_NUMBER = SYST-DYNNR

      * FIELD_NAME = ' '

      IMPORTING

      file_name = p_file

      .

      ENDFORM.

      FORM upload_data.

      CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

      EXPORTING

      filename = p_file

      i_begin_col = 1

      i_begin_row = 1

      i_end_col = 100

      i_end_row = 65536

      TABLES

      intern = it_excel

      EXCEPTIONS

      inconsistent_parameters = 1

      upload_ole = 2

      OTHERS = 3.

      ENDFORM.

      FORM move_data.

      data: w_index type sy-index.

      SORT it_excel BY row col.

      LOOP AT it_excel INTO wa_excel.

      move wa_excel-col to w_index.

      ASSIGN COMPONENT w_index of STRUCTURE wa_data to <l_fs>.

      move wa_excel-value to <l_fs>.

      AT END OF row.

      APPEND wa_data TO it_data.

      CLEAR: wa_data, wa_return.

      ENDAT.

      ENDLOOP.

      ENDFORM.

      -------------------------------------------------------------------------------------------------------------------------

      I debugged it and it says the field and field symbol is not of same type ... the assign component is not working.... actually i haven't used ASSIGN COMPONENT so am not sure what exactly its trying to do inorder to fix this

  • Posted on Nov 29, 2013 at 03:52 PM

    Dear Naveed,

    You use the following function to upload data from excel. You do not need to write any case-when-endcase statement.

    TEXT_CONVERT_XLS_TO_SAP

    It will directly upload data from excel into your structure of internal table in the same same sequence.

    Example:

    DATA: loc_filename TYPE rlgrap-filename. "string.
    DATA: it_raw TYPE truxs_t_text_data.
    loc_filename = p-file. "Your excel file name


    CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING

    * I_FIELD_SEPERATOR =
    i_line_header = 'X'
    i_tab_raw_data = it_raw " WORK TABLE
    i_filename = loc_filename
    TABLES
    i_tab_converted_data = timedata "Your internal table
    EXCEPTIONS
    conversion_failed = 1
    OTHERS = 2.

    REgards,

    Venkat

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 04, 2013 at 02:56 AM

    Still awaiting someone to clarify my query 😔

    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.