excel to internal table

[Former Member]

Hi everyone

how to convert excel data into internal table?

thanks in advance

regards

priya

[Former Member]

Hi everyone

how to convert excel data into internal table?

thanks in advance

regards

priya

[Former Member]

Hi priya

here is the code for converting excel to internal table

REPORT UPLOAD_EXCEL no standard page heading.

*Data Declaration

*----

---

data: itab like alsmex_tabline occurs 0 with header line.

• Has the following format:

• Row number | Colum Number | Value

• ---------------------------------------

• i.e. 1 1 Name1

• 2 1 Joe

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.

regards

kishore

reward if helpful

[Former Member]

HI

thanks kishore & srikanth for your reply.

i want to upload the contents of the excel into ME51N for creating Purhcase requisition i cant able to do that using BDC. what is the solution?

regards

priya

[Former Member]

Hi again,

1. ENJOY transactions.

2. ME51N

is a enjoy transaction

using some complex controls

on screen.

(hence, BDC wont work in this case)

3. For such transctions, BAPI

or some FM

which takes the input of purchase requisitin,

is advisable.

regards,

amit m.

[Former Member]

HI priya

the best way to do that is by using BAPI.

for that fist convert you EXCEL into tab-delimited text file then use GUI_upload for uploading into internal table and use the BAPI

CALL FUNCTION 'BAPI_REQUISITION_CREATE'.

regards

kishore

[Former Member]

HI

here is the code for the BAPI

REPORT Z_BAPI_PURCHREQ.

DATA: BEGIN OF itab OCCURS 0,

bsart(4),

• ."Purchase requisition document type

txz01(40),

• ."Short text

menge(13),

• ."Purchase requisition quantity

meins(3),

• ."Purchase requisition unit of measure

eeind(10),

• ."Item delivery date

wgbez(9),

• ."Material Group

name1(4),

• ."Plant

ekgrp(3),

• ."Purchasing Group

afnam(12),

• ."Name of requisitioner/requester

bednr(10),

• ."Requirement Tracking Number

knttp(1),

• ."Account assignment category

sakto(10),

• ."G/L Account Number

ps_posid(8),

• ."Work Breakdown Structure Element (WBS Element)

editor(132),

• ."Text line

END OF itab.

DATA: itab_bapiebanc LIKE bapiebanc OCCURS 0 WITH HEADER LINE.

DATA: itab_bapiebkn LIKE bapiebkn OCCURS 0 WITH HEADER LINE.

DATA: itab_bapiebantx LIKE bapiebantx OCCURS 0 WITH HEADER LINE.

DATA: BEGIN OF errmsg OCCURS 10.

INCLUDE STRUCTURE bapiret2.

DATA: END OF errmsg.

DATA: errflag.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'C:\Documents and Settings\130503\My

Documents\purchase.txt'

• FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

• HEADER_LENGTH = 0

• READ_BY_LINE = 'X'

• DAT_MODE = ' '

• CODEPAGE = ' '

• IGNORE_CERR = ABAP_TRUE

• REPLACEMENT = '#'

• CHECK_BOM = ' '

• IMPORTING

• FILELENGTH =

• HEADER =

TABLES

DATA_TAB = itab

• EXCEPTIONS

• FILE_OPEN_ERROR = 1

• FILE_READ_ERROR = 2

• NO_BATCH = 3

• GUI_REFUSE_FILETRANSFER = 4

• INVALID_TYPE = 5

• NO_AUTHORITY = 6

• UNKNOWN_ERROR = 7

• BAD_DATA_FORMAT = 8

• HEADER_NOT_ALLOWED = 9

• SEPARATOR_NOT_ALLOWED = 10

• HEADER_TOO_LONG = 11

• UNKNOWN_DP_ERROR = 12

• ACCESS_DENIED = 13

• DP_OUT_OF_MEMORY = 14

• DISK_FULL = 15

• DP_TIMEOUT = 16

• OTHERS = 17

.

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

itab_bapiebanc-doc_type = itab-bsart.

itab_bapiebanc-short_text = itab-txz01.

itab_bapiebanc-quantity = itab-menge.

itab_bapiebanc-unit = itab-meins.

itab_bapiebanc-deliv_date = itab-eeind.

itab_bapiebanc-plant = itab-name1.

itab_bapiebanc-pur_group = itab-ekgrp.

itab_bapiebanc-preq_name = itab-afnam.

itab_bapiebanc-trackingno = itab-bednr.

itab_bapiebanc-mat_grp = itab-wgbez.

itab_bapiebanc-acctasscat = itab-knttp.

itab_bapiebkn-g_l_acct = itab-sakto.

itab_bapiebkn-wbs_elem = itab-ps_posid.

itab_bapiebantx-text_line = itab-editor.

APPEND itab_bapiebkn.

APPEND itab_bapiebanc.

APPEND itab_bapiebantx.

ENDLOOP.

*

CALL FUNCTION 'BAPI_REQUISITION_CREATE'

TABLES

requisition_items = itab_bapiebanc

requisition_account_assignment = itab_bapiebkn

requisition_item_text = itab_bapiebantx

return = errmsg.

LOOP AT errmsg.

IF errmsg-type EQ 'E'.

WRITE:/'Error in function', errmsg-message.

errflag = 'X'.

ELSE.

WRITE:/ errmsg-message.

ENDIF.

ENDLOOP.

CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'.

regards

kishore

[Former Member]

use this Function module::

ALSM_EXCEL_TO_INTERNAL_TABLE

regards

srikanth

[Former Member]

use GUI_UPLOAD fmodule

[Former Member]

Hi,

chk this link

[Former Member]

Hi priya,

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

2. a) is easy and recommended

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

4. <b>afTER THAT , we have to convert this cell by cell data,

into our format of internal table</b>.

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

(it is tried wit T001 structure data)

<b> (it will AUTOMATICALLY based upon the

fields of internal table,

convert data from cell by cell,

to that of internal table fields)</b>

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.

[Former Member]

REPORT ZV_LOCALFILE_ITTABLE .

DATA : BEGIN OF itab OCCURS 0 ,
carrid LIKE sflight-carrid,
connid LIKE sflight-connid,
fldate LIKE sflight-fldate,
END OF itab.
DATA : V_STRING TYPE STRING.

V_STRING = 'C:TempBook1.txt'.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = V_STRING
FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = 'X'
* HEADER_LENGTH = 0
* READ_BY_LINE = 'X'
* DAT_MODE = ' '
* IMPORTING
* FILELENGTH =
* HEADER =
tables
data_tab = itab
EXCEPTIONS
FILE_OPEN_ERROR = 1
FILE_READ_ERROR = 2
OTHERS = 17
.
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 itab.
write :/ itab-carrid,itab-connid,itab-fldate.
endloop.

also change the date in file to YYYYMMDD format