Optimized logic to upload data from Excel

[Former Member]

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.

[Former Member]

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

[Former Member]

Hello Ivan

Could you tell me what this structure it_tracciato refer to??

Thanks.

[Former Member]

Hi,

it_tracciato is an internal table that i use in the programs' flow.

it_tracciato represents the structure of the excel file.

for examole into your excel you have two column--> it_tracciato have two column.

excel

OdA Pos OdA

100  10

100   20

200  10

it_tracciato

   DATA: BEGIN OF it_tracciato OCCURS 0,
       ebeln LIKE ekpo-ebeln,
       ebelp LIKE ekpo-ebelp.
   DATA: END   OF it_tracciato.

Cheers

Ivan

[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

[venkateswaran_k]

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

[Former Member]

I guess this works only with xls files... I executed the program with 'TEXT_CONVERT_XLS_TO_SAP' function module using xlsx data file and it has thrown an error message 'the file format doesn't match'

[venkateswaran_k]

No,  i am using this function widely.. It works for all excel formats.

[Former Member]

its not check this:

[Former Member]

Still awaiting someone to clarify my query