topic Re: problem in excel sheet data upload in Application Development and Automation Discussions

problem in excel sheet data upload

Former Member — Thu, 25 May 2006 05:49:46 GMT

hello every one,

i m trying to upload data from excel sheet , into sap system , i m having the following problem . plz refer the following code and c if u can help me............

1.DATA : BEGIN OF ITAB OCCURS 1,

LIFNR TYPE LIFNR,

KTOKK TYPE KTOKK,

EKROG TYPE EKORG,

NAME1 TYPE NAME1,

SORTL TYPE SORTL,

LAND1 TYPE LAND1,

WAERS TYPE WAERS,

END OF ITAB. (THIS IS FOR MK01 TRANASACTION)

2.DATA : JTAB LIKE ALSMEX_TABLINE OCCURS 1 WITH HEADER LINE.

3.DATA : BDCDATA TYPE BDCDATA OCCURS 1 WITH HEADER LINE.

4.DATA : K1 TYPE I VALUE 1,

M1 TYPE I VALUE 1,

K2 TYPE I VALUE 100,

M2 TYPE I VALUE 9999.

5.CALL FUNCTION ALSM_EXCEL_TO_INTERNAL_TABLE

6.PASS VALUES TO THIS FUNCTION IN WHICH THE FILENAME IS (C:\BOOK1.XLS)

7.IN THE FM PASS INTERN = JTAB.(WHICH IS LIKE ALSMEX_TABLINE)

8.CHECK THE VALUES FOR JTAB.IT SHOWS THE CONTENTS OF THE EXCEL SHEET DATA LIKE FOLLOWS

0001000115406

000100020001

000100030001

00010004RAVI

00010005RV

00010006IN

00010007INR

PROBLEM : PROBLEM IS THAT I DONT WANT ROW AND COL NO , I ONLY WANT THE VALUE . I CAN GET IT THORGH USING ONLY vALUE FOR JTAB.BUT THW SYSTEM CAN NOT DIFFERENTIATE BETWEEN THE VALUE AND THE ROW AND COL NUMBER,SO IT TAKES THE WHOLE VALUE WHILE ENTERNIG THE TRANSACTION.

PLZ HELP ME , HOW I SHOULD TAKE ONLY VALUE OF JTAB INTO AN INTERNAL TABLE SO THAT

I CAN THEN PASS IT IN TO PERFORM STATEMENTS FOR ENTRY IN MK01.

Re: problem in excel sheet data upload

Former Member — Thu, 25 May 2006 05:54:52 GMT

Hi aafaqhusain,

1. U want to upload data from EXCEL

into internal table.

2. and u are using ALSM_EXCEL_TO_INTERNAL_TABLE.

3. But We cannot do this direclty !

4. In this FM,

there is one TABLES

INTERN = t_upload1

The definition of this t_upload1

should be as per

INTERN LIKE ALSMEX_TABLINE

and not as per your data.

5. Hence, it is giving error here .

*----

For uploading purpose :

6. There are TWO options.

a) either save the excel to TAB Delimited file,

and use GUI_UPLOAD to upload the data in internal table.

b) use FM for excel purpose.

7. a) is easy and recommended

8. b) there is a FM for it,

but we have to apply some more logic

bcos the FM uploads data of excel

in the intenal table,

CELL BY CELL

9. afTER THAT , we have to convert this cell by cell data,

into our format of internal table.

10. use this code (just copy paste in new program)

(it is tried wit T001 structure data)

(it will AUTOMATICALLY based upon the

fields of internal table,

convert data from cell by cell,

to that of internal table fields)

REPORT abc.

*----

DATA : ex LIKE TABLE OF alsmex_tabline WITH HEADER LINE.

DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.

DATA : cmp LIKE TABLE OF rstrucinfo WITH HEADER LINE.

DATA : col TYPE i.

DATA : col1 TYPE i.

FIELD-SYMBOLS : <fs> .

DATA : fldname(50) TYPE c.

*----

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = 'd:\def.xls'

i_begin_col = 1

i_begin_row = 1

i_end_col = 100

i_end_row = 100

TABLES

intern = ex

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

BREAK-POINT.

*----

CALL FUNCTION 'GET_COMPONENT_LIST'

EXPORTING

program = sy-repid

fieldname = 'T001'

TABLES

components = cmp.

*----

LOOP AT ex.

AT NEW row.

IF sy-tabix <> 1.

APPEND t001.

CLEAR t001.

ENDIF.

ENDAT.

col = ex-col.

col1 = col + 1.

READ TABLE cmp INDEX col.

CONCATENATE 'T001-' cmp-compname INTO fldname.

ASSIGN (fldname) TO <fs>.

<fs> = ex-value.

ENDLOOP.

BREAK-POINT.

regards,

amit m.

Re: problem in excel sheet data upload

Former Member — Thu, 25 May 2006 06:03:24 GMT

just copy and paste and see the output...

when u fil excel sheet just leave blank 1st row....

give the file path in parameter

ex---C:\Documents and Settings\singkish\Desktop\kis.xls.

&----

*& Report ZNEGI6 *

*& *

&----

*& *

&----

REPORT ZNEGI6 .

data: itab like alsmex_tabline occurs 0 with header line.

TYPES: Begin of t_record,

name1 like itab-value,

name2 like itab-value,

age like itab-value,

End of t_record.

DATA: it_record type standard table of t_record initial size 0,

wa_record type t_record.

DATA: gd_currentrow type i.

*Selection Screen Declaration

*----

PARAMETER p_infile like rlgrap-filename .

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

*START OF SELECTION

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = p_infile

i_begin_col = '1'

i_begin_row = '2' "Do not require headings

i_end_col = '14'

i_end_row = '31'

tables

intern = itab

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

if sy-subrc <> 0.

message e010(zz) with text-001. "Problem uploading Excel Spreadsheet

endif.

Sort table by rows and colums

sort itab by row col.

Get first row retrieved

read table itab index 1.

Set first row retrieved to current row

gd_currentrow = itab-row.

loop at itab.

Reset values for next row

if itab-row ne gd_currentrow.

append wa_record to it_record.

clear wa_record.

gd_currentrow = itab-row.

endif.

case itab-col.

when '0001'. "First name

wa_record-name1 = itab-value.

when '0002'. "Surname

wa_record-name2 = itab-value.

when '0003'. "Age

wa_record-age = itab-value.

endcase.

endloop.

append wa_record to it_record.

*!! Excel data is now contained within the internal table IT_RECORD

Display report data for illustration purposes

loop at it_record into wa_record.

write:/ sy-vline,

(10) wa_record-name1, sy-vline,

(10) wa_record-name2, sy-vline,

(10) wa_record-age, sy-vline.

endloop.

Re: problem in excel sheet data upload

Former Member — Thu, 25 May 2006 06:09:52 GMT

Hai

Go through the following Code

REPORT ZK_REPORT.

internal table declarations

DATA: BEGIN OF ITAB OCCURS 0,

NAME(20) TYPE C,

ADDR(20) TYPE C,

END OF ITAB.

DATA: ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA: K1 TYPE I VALUE 1,

M1 TYPE I VALUE 1,

K2 TYPE I VALUE 100,

M2 TYPE I VALUE 9999.

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

use FM for uploading data from EXCEL to internal table

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = 'C:\book1.xls'

I_BEGIN_COL = K1

I_BEGIN_ROW = M1

I_END_COL = K2

I_END_ROW = M2

TABLES

INTERN = ITAB1

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 ITAB1.

WRITE:/ ITAB1.

Thanks & regards

Sreeni

Re: problem in excel sheet data upload

Former Member — Thu, 12 Apr 2007 12:27:40 GMT

thankx