topic Re: Uploading data form Excel file in Application Development and Automation Discussions

Uploading data form Excel file

Former Member — Fri, 04 Jul 2008 06:40:20 GMT

Hi folks,

In my BDC i have to upload data into internal table from a Excel file. For this, i have used the FM 'ALSM_EXCEL_TO_INTERNAL_TABLE' . Data is coming to the itab perfectly. But the ITAB is declared as table line of structure ALSMEX_TABLINE. So this has contain just row no, col no and value. Now how to use my itab data for further process. we can loop the itab, as it is declared as a structure. So pls Suggest me...

Thanks,

Shaym.

Re: Uploading data form Excel file

former_member195383 — Fri, 04 Jul 2008 06:42:55 GMT

Hi..

If the data is coming to itab ...then definitely u can loop in to ITAB.

declare a work area of the type ALSMEX_TABLINE...say wa_itab.

loop at itab into wa_itab.

endloop.

hope this works...

Edited by: Rudra Prasanna Mohapatra on Jul 4, 2008 8:43 AM

Re: Uploading data form Excel file

sachin_mathapati — Fri, 04 Jul 2008 06:43:09 GMT

Try Declaring u r Internal table like this..

DATA : tb_alsmex TYPE TABLE OF alsmex_tabline WITH HEADER LINE .

Check this sample code for further help..

LOOP AT tb_alsmex.

For every new row

AT NEW row.

Move 'A00' into the version number of the infostructure

tb_s600-vrsio = co_a00.

ENDAT.

For every value of COL move comment into the corresponding
fields of S600.

CASE tb_alsmex-col.

WHEN 1.

tb_s600-spmon = tb_alsmex-value.

WHEN 2.

tb_s600-kunnr = tb_alsmex-value.

WHEN 3.

tb_s600-prodh1 = tb_alsmex-value.

WHEN 4.

tb_s600-prodh2 = tb_alsmex-value.

WHEN 5.

tb_s600-prodh3 = tb_alsmex-value.

WHEN 6.

tb_s600-zzprodh4 = tb_alsmex-value.

WHEN 7.

tb_s600-zzprodh5 = tb_alsmex-value.

WHEN 8.

tb_s600-zzprodh6 = tb_alsmex-value.

WHEN 9.

tb_s600-zzprodh7 = tb_alsmex-value.

WHEN 10.

tb_s600-zzprodh8 = tb_alsmex-value.

WHEN 11.

tb_s600-zztotalloc = tb_alsmex-value.

WHEN 12.

tb_s600-zzalloc = tb_alsmex-value.

WHEN 13.

tb_s600-zzrsrvqty = tb_alsmex-value.

WHEN 14.

tb_s600-zzremain = tb_alsmex-value.

WHEN OTHERS.

ENDCASE.

Append to the internal table all the above values

AT END OF row.

APPEND tb_s600.

ENDAT.

ENDLOOP.

Edited by: Sachin Mathapati on Jul 4, 2008 12:15 PM

Re: Uploading data form Excel file

Former Member — Fri, 04 Jul 2008 06:43:53 GMT

Do like this...

Read Data From Excel to Internal table

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = 1

i_begin_row = 1

i_end_col = 12

i_end_row = 20

TABLES

intern = i_inputdata

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.

SORT i_inputdata BY row col.

LOOP AT i_inputdata.

CASE i_inputdata-col.

WHEN 1.

i_exceldata-rectype = i_inputdata-value.

WHEN 2.

i_exceldata-kappl = i_inputdata-value.

WHEN 3.

i_exceldata-kschl = i_inputdata-value.

WHEN 4.

i_exceldata-kunnr = i_inputdata-value.

WHEN 5.

i_exceldata-matnr = i_inputdata-value .

WHEN 6.

i_exceldata-kbetr = i_inputdata-value .

WHEN 7.

i_exceldata-konwa = i_inputdata-value .

WHEN 8.

i_exceldata-kpein = i_inputdata-value .

WHEN 9.

i_exceldata-kmein = i_inputdata-value.

WHEN 10.

i_exceldata-datab = i_inputdata-value .

WHEN 11.

i_exceldata-datbi = i_inputdata-value .

WHEN 12.

i_exceldata-kstbm = i_inputdata-value .

ENDCASE.

AT END OF row.

APPEND i_exceldata.

CLEAR i_exceldata.

ENDAT.

ENDLOOP.

Re: Uploading data form Excel file

Former Member — Fri, 04 Jul 2008 06:54:23 GMT

hi,

'ALSM_EXCEL_TO_INTERNAL_TABLE' :

TYPES: BEGIN OF TY_ITAB,

some fields

END OF TY_ITAB.

DATA: T_ITAB TYPE TABLE OF TY_ITAB,

w_iTAB TYPE TY_ITAB.

DATA : ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA: R1 TYPE I VALUE 1,

C1 TYPE I VALUE 1,

R2 TYPE I VALUE 4,

C2 TYPE I VALUE 8,

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = FILEPATH

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 8

I_END_ROW = 4

TABLES

INTERN = ITAB1

EXCEPTION

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.

SORT ITAB1 BY ROW COL.

LOOP AT ITAB1.

CASE ITAB1-COL.

WHEN 1.

W_ITAB-LIFNR = ITAB1-VALUE.

WHEN 2.

W_ITAB-KTOKK = ITAB1-VALUE.

WHEN 3.

W_ITAB-NAME1 = ITAB1-VALUE.

WHEN 4.

W_ITAB-SORTL = ITAB1-VALUE.

WHEN 5.

W_ITAB-LAND1 = ITAB1-VALUE.

WHEN 6.

W_ITAB-SPRAS = ITAB1-VALUE.

ENDCASE.

AT END OF ROW.

APPEND W_ITAB TO T_ITAB.

CLEAR W_ITAB.

ENDAT.

ENDLOOP.

reward if useful,

thanks and regards

Re: Uploading data form Excel file

Former Member — Fri, 04 Jul 2008 06:55:35 GMT

Hi,

Plz check below block of code hope this will solve ur problem.

*&      Form  read_file
*&---------------------------------------------------------------------*
*Read data from File
*----------------------------------------------------------------------*
*  -->  p_file
*  <--  it_wty
*----------------------------------------------------------------------*
FORM read_file .

  IF p_file IS INITIAL.

    MESSAGE 'No File Name' TYPE 'E'.

  ENDIF.

  v_filename = p_file.

*Reading from Excel File into internal table it_data.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = v_filename
      i_begin_col             = 1
      i_begin_row             = 2
      i_end_col               = 14
      i_end_row               = p_rows
    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.


*Moving from excel to the internal table format

  LOOP AT it_data INTO wa_data1.

  wa_data = wa_data1.

    AT NEW row.

      CLEAR wa_wty.

    ENDAT.


    CASE wa_data-col.

      WHEN '001'.
        MOVE wa_data-value TO wa_wty-MGANR.
        MOVE wa_data-value TO wa_service-MGANR.
        MOVE wa_data-value TO wa_counter-MGANR.
      WHEN '002'.
        MOVE wa_data-value TO wa_wty-gaktx.

      WHEN '003'.
        MOVE wa_data-value TO wa_wty-gaart.

      WHEN '004'.
        MOVE wa_data-value TO wa_wty-gaerb.

      WHEN '005'.
        MOVE wa_data-value TO wa_wty-gasor.

      WHEN '006'.
        MOVE wa_data-value TO wa_wty-exgar.

      WHEN '007'.
        MOVE wa_data-value TO wa_wty-gagrp.

      WHEN '008'.
        MOVE wa_data-value TO wa_wty-LSTNR.
        MOVE wa_data-value TO wa_service-LSTNR.
        MOVE wa_data-value TO wa_counter-LSTNR.

      WHEN '009'.
        MOVE wa_data-value TO wa_wty-postx.
        MOVE wa_data-value TO wa_service-postx.

      WHEN '010'.
        MOVE wa_data-value TO wa_wty-inexcl.
        MOVE wa_data-value TO wa_service-inexcl.

      WHEN '011'.
        MOVE wa_data-value TO wa_wty-gazna.
        MOVE wa_data-value TO wa_counter-gazna.

      WHEN '012'.
        MOVE wa_data-value TO wa_wty-rangf.
        MOVE wa_data-value TO wa_counter-rangf.

      WHEN '013'.
        MOVE wa_data-value TO wa_wty-kzopr.
        MOVE wa_data-value TO wa_counter-kzopr.

      WHEN '014'.
        MOVE wa_data-value TO wa_wty-gazwe.
        MOVE wa_data-value TO wa_counter-gazwe.

    ENDCASE.


    AT END OF row.

      APPEND wa_wty TO it_wty.
      APPEND wa_service TO it_service.
      APPEND wa_counter TO it_counter.
    ENDAT.

    CLEAR : wa_data.


  ENDLOOP.

ENDFORM.                    " read_file

hope this solves ur problem.

plz reward if useful.

thanks,

dhanashri.