topic Re: Optimized logic to upload data from Excel in Application Development and Automation Discussions

Optimized logic to upload data from Excel

Former Member — Wed, 27 Nov 2013 12:46:17 GMT

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.

Re: Optimized logic to upload data from Excel

Former Member — Wed, 27 Nov 2013 13:12:50 GMT

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

Re: Optimized logic to upload data from Excel

Former Member — Fri, 29 Nov 2013 15:30:51 GMT

Hello Ivan

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

Thanks.

Re: Optimized logic to upload data from Excel

Former Member — Fri, 29 Nov 2013 15:47:35 GMT

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

Re: Optimized logic to upload data from Excel

venkateswaran_k — Fri, 29 Nov 2013 15:52:06 GMT

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

Re: Optimized logic to upload data from Excel

Former Member — Fri, 29 Nov 2013 16:50:23 GMT

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'

Re: Optimized logic to upload data from Excel

venkateswaran_k — Fri, 29 Nov 2013 16:53:36 GMT

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

Re: Optimized logic to upload data from Excel

Former Member — Fri, 29 Nov 2013 17:07:08 GMT

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

Re: Optimized logic to upload data from Excel

Former Member — Fri, 29 Nov 2013 17:16:42 GMT

its not check this:

Re: Optimized logic to upload data from Excel

Former Member — Wed, 04 Dec 2013 02:56:49 GMT

Still awaiting someone to clarify my query