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

uploading excel issue

Hi all,

Iam uploading an excel sheet to a table.

i moved all the rows and colums using FM into i_tab_sheet.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_file

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 5

I_END_ROW = 20

TABLES

INTERN = i_itab_sheet

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

loop at i_itab_sheet.

If i_itab_sheet-col = 1.

t_upload-erdat = i_itab_sheet-value.

elseIF i_itab_sheet-col = 2.

t_upload-matnr = i_itab_sheet-value.

<b>elseIF i_itab_sheet-col = 5.</b>

t_upload-kostl = i_itab_sheet-value.

append t_upload.

clear t_upload.

endif.

endloop.

The problem is that for 1 record say 2 record i.e 2 row my column 5 is empty.

Infact there is no record in i_tab_sheet with row 2 and column 5.

so when i loop it at i_tab_sheet.

The statement elseIF i_itab_sheet-col = 5.

t_upload-kostl = i_itab_sheet-value.

is never triggered and since i have append statement after this this particular record i.e record 2 of excel sheet is not moved in to internal table.

Can anyone suggest me how to overcome this.I want to move this one in to internal table

Add comment
10|10000 characters needed characters exceeded

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 06:34 PM

    Hai

    Check the following Code

    DATA : BEGIN OF ITAB OCCURS 0,

    KUNNR TYPE RF02D-KUNNR,

    SORT TYPE ADDR1_DATA-SORT1,

    STREET TYPE ADDR1_DATA-STREET,

    POST_CODE TYPE ADDR1_DATA-POST_CODE1,

    PO_BOX TYPE ADDR1_DATA-PO_BOX,

    TEL_NUMBER TYPE SZA1_D0100-TEL_NUMBER,

    FAX_NUMBER TYPE SZA1_D0100-FAX_NUMBER,

    END OF ITAB.

    *"INTERNAL TAB TO TAKE EXCEL SHEET.

    DATA : IT_EXCEL LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

    SELECTION-SCREEN BEGIN OF BLOCK B1.

    PARAMETERS : P_FILE TYPE RLGRAP-FILENAME DEFAULT

    'Customer_download.txt'.

    SELECTION-SCREEN END OF BLOCK B1.

    START-OF-SELECTION.

    PERFORM UPLOAD.

    &----


    *& Form upload

    &----


    FORM UPLOAD .

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

    EXPORTING

    FILENAME = P_FILE

    I_BEGIN_COL = 1

    I_BEGIN_ROW = 3

    I_END_COL = 7

    I_END_ROW = 4

    TABLES

    INTERN = IT_EXCEL

    • 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_EXCEL.

    CASE IT_EXCEL-COL.

    WHEN 1.

    ITAB-KUNNR = IT_EXCEL-VALUE.

    WHEN 2.

    ITAB-SORT = IT_EXCEL-VALUE.

    WHEN 3.

    ITAB-STREET = IT_EXCEL-VALUE.

    WHEN 4.

    ITAB-POST_CODE = IT_EXCEL-VALUE.

    WHEN 5.

    ITAB-PO_BOX = IT_EXCEL-VALUE.

    WHEN 6.

    ITAB-TEL_NUMBER = IT_EXCEL-VALUE.

    WHEN 7.

    ITAB-FAX_NUMBER = IT_EXCEL-VALUE.

    ENDCASE.

    AT END OF ROW.

    APPEND ITAB.

    CLEAR ITAB.

    ENDAT.

    ENDLOOP.

    ENDFORM. " upload

    Regards

    Sreeni

    Message was edited by: Sreenivasulu Ponnadi

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 06:35 PM

    Hi Kajol,

    1)<b>Just place the APPEND statement after all IF conditions.</b>

    Try as follows in BOLD format.

    loop at i_itab_sheet.

    If i_itab_sheet-col = 1.

    t_upload-erdat = i_itab_sheet-value.

    elseIF i_itab_sheet-col = 2.

    t_upload-matnr = i_itab_sheet-value.

    elseIF i_itab_sheet-col = 5.

    t_upload-kostl = i_itab_sheet-value.

    endif.

    <b>append t_upload.

    clear t_upload.</b>

    endloop.

    <b>Note: Plz reward all helpful answers and close the thread once it is solved.Dont open threads even if they were solved.</b>

    Thanks,

    Vinay

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 06:38 PM

    hi kajol,

    sort i_itab_sheet by row col.

    loop at i_itab_sheet.

    If i_itab_sheet-col = 1.

    t_upload-erdat = i_itab_sheet-value.

    elseIF i_itab_sheet-col = 2.

    t_upload-matnr = i_itab_sheet-value.

    elseIF i_itab_sheet-col = 5.

    t_upload-kostl = i_itab_sheet-value.

    endif.

    append t_upload.

    clear t_upload.

    endloop.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 06:54 PM

    HI Kajol,

    Refer to the code given by Rich to Vijay : This even worked for me

    report zrich_0002.

    types: begin of ttab ,

    fld1(30) type c,

    fld2(30) type c,

    fld3(30) type c,

    fld4(30) type c,

    fld5(30) type c,

    end of ttab.

    data: itab type table of ttab with header line.

    selection-screen skip 1.

    parameters: p_file type localfile default

    'C:\test.txt'.

    selection-screen skip 1.

    at selection-screen on value-request for p_file.

    call function 'KD_GET_FILENAME_ON_F4'

    exporting

    static = 'X'

    changing

    file_name = p_file.

    start-of-selection.

    clear itab. refresh itab.

    perform upload_data.

    loop at itab.

    write:/ itab-fld1, itab-fld2, itab-fld3, itab-fld4, itab-fld5.

    endloop.

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

    • Upload_Data

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

    form upload_data.

    data: file type rlgrap-filename.

    data: xcel type table of alsmex_tabline with header line.

    file = p_file.

    call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

    exporting

    filename = file

    i_begin_col = '1'

    i_begin_row = '1'

    i_end_col = '200'

    i_end_row = '5000'

    tables

    intern = xcel

    exceptions

    inconsistent_parameters = 1

    upload_ole = 2

    others = 3.

    loop at xcel.

    case xcel-col.

    when '0001'.

    itab-fld1 = xcel-value.

    when '0002'.

    itab-fld2 = xcel-value.

    when '0003'.

    itab-fld3 = xcel-value.

    when '0004'.

    itab-fld4 = xcel-value.

    when '0005'.

    itab-fld5 = xcel-value.

    endcase.

    at end of row.

    append itab.

    clear itab.

    endat.

    endloop.

    endform.

    Regards,

    Varun .

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 07:23 PM

    Hi

    THANKS YOU YOUR REPLIES AND YOUR CODE SOLVED MY PROBLEM.

    I awarded points for your answers

    Add comment
    10|10000 characters needed characters exceeded