topic Re: uploading excel issue in Application Development and Automation Discussions

uploading excel issue

Former Member — Wed, 02 Aug 2006 18:30:50 GMT

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.

elseIF i_itab_sheet-col = 5.

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

Re: uploading excel issue

Former Member — Wed, 02 Aug 2006 18:34:31 GMT

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

Re: uploading excel issue

Former Member — Wed, 02 Aug 2006 18:35:11 GMT

Hi Kajol,

1)Just place the APPEND statement after all IF conditions.

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.

append t_upload.

clear t_upload.

endloop.

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

Thanks,

Vinay

Re: uploading excel issue

Former Member — Wed, 02 Aug 2006 18:38:11 GMT

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.

Re: uploading excel issue

Former Member — Wed, 02 Aug 2006 18:54:13 GMT

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 .

Re: uploading excel issue

Former Member — Wed, 02 Aug 2006 19:23:42 GMT

THANKS YOU YOUR REPLIES AND YOUR CODE SOLVED MY PROBLEM.

I awarded points for your answers