uploading excel issue

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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 .

[Former Member]

Hi

THANKS YOU YOUR REPLIES AND YOUR CODE SOLVED MY PROBLEM.

I awarded points for your answers