topic Re: Upload Excel into Internal table in Application Development and Automation Discussions

Upload Excel into Internal table

Former Member — Thu, 13 Dec 2007 05:13:05 GMT

Hi experts,

How to upload Excel data into internal table without using ALSM_to_internal_table.

because NW trail version does not have few function modules. could you pls help me, hw to upload excel data into internal table without using Function module.

It is very urgent.

Points will be Rewarded.

Re: Upload Excel into Internal table

kesavadas_thekkillath — Thu, 13 Dec 2007 05:16:17 GMT

Dear frnd,

Try this,

DATA:IT_EXCEL LIKE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-002.

PARAMETERS: P_FILE TYPE LOCALFILE OBLIGATORY .

SELECTION-SCREEN END OF BLOCK B1.

**********************AT SELECTION SCREEN EVENTS BEGINS***************

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

STATIC = 'X'

CHANGING

FILE_NAME = P_FILE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = 1 "From 1st Column

I_BEGIN_ROW = 2 "From 2nd row

I_END_COL = 6 "Till 6th Column

I_END_ROW = 65536 "Till Row

TABLES

INTERN = IT_EXCEL

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

CLEAR IT_EXCEL.

DESCRIBE TABLE IT_EXCEL.

IF SY-TFILL = 0.

MESSAGE I937.

STOP.

ENDIF.

LOOP AT IT_EXCEL.

CASE IT_EXCEL-COL.

WHEN '0001'.

MOVE: IT_EXCEL-VALUE TO RECORD1-MATNR_005.

WHEN '0002'.

MOVE: IT_EXCEL-VALUE TO RECORD1-WERKS_006.

WHEN '0003'.

MOVE: IT_EXCEL-VALUE TO RECORD1-BUDAT_002.

WHEN '0004'.

MOVE: IT_EXCEL-VALUE TO RECORD1-BKTXT_004.

WHEN '0005'.

MOVE: IT_EXCEL-VALUE TO RECORD1-ERFMG_007.

WHEN '0006'.

MOVE: IT_EXCEL-VALUE TO RECORD1-ERFMG_008.

ENDCASE.

AT END OF ROW.

APPEND RECORD1.

CLEAR RECORD1.

ENDAT.

ENDLOOP.

reward if it helped

Re: Upload Excel into Internal table

Former Member — Thu, 13 Dec 2007 05:18:00 GMT

Hi,

Copy the code of ALSM_EXCEL_TO_INTERNAL_TABLE to a Z Function module and use that in your program.

Thanks,

Kasiraman R

Re: Upload Excel into Internal table

former_member404244 — Thu, 13 Dec 2007 05:18:00 GMT

Hi,

use GUI_UPLOAD fm for ur requirement...or copy ALSM_EXCEL_TO_INTERNAL_TABLE FM to a ZFM use that in your program.

Regards,

Nagaraj

Re: Upload Excel into Internal table

Former Member — Thu, 13 Dec 2007 05:19:33 GMT

hi kavitha,

convert that excel file into text delimited file.

and then use GUI_UPLOAD function module to upload data into internal table.

Reward points if useful

Chandra

Re: Upload Excel into Internal table

former_member386202 — Thu, 13 Dec 2007 05:20:14 GMT

Hi,

Transfer flate file to application server using tcode CG3Z and then use OPEN DATASET to upload the data to internal Table.

Refer this code

*-- Concatenate the file path retrived from the function module

with additonal directory 'mm/ptpi006/in/' and pass it to a
variable.

CONCATENATE lv_fname c_const INTO pv_input_file.

CONCATENATE lv_fname c_const pv_flext INTO lv_file.

lv_dsn = lv_file.

*-- Open Dataset

OPEN DATASET lv_dsn FOR INPUT IN TEXT MODE ENCODING DEFAULT.

IF sy-subrc NE 0.

File not found

MESSAGE e503.

ELSE.

Read each line from the dataset and append to an internal table.

DO.

IF sy-subrc NE 0.

No records found in the file.

MESSAGE e504.

ENDIF.

*-- Read dataset and pass it to an internal table.

READ DATASET lv_dsn INTO lv_line.

CLEAR wa_eord.

wa_eord-matnr = lv_line+0(18).

wa_eord-werks = lv_line+18(4).

wa_eord-vdatu = lv_line+22(8).

wa_eord-bdatu = lv_line+30(8).

wa_eord-lifnr = lv_line+38(10).

wa_eord-ekorg = lv_line+48(4).

wa_eord-meins = lv_line+52(3).

wa_eord-autet = lv_line+55(1).

wa_eord-feskz = lv_line+56(1).

IF NOT wa_eord IS INITIAL.

APPEND wa_eord TO pr_eord.

ENDIF.

ENDDO.

Regards,

Prashant

Re: Upload Excel into Internal table

rajesh_akarte2 — Thu, 13 Dec 2007 05:20:56 GMT

hi Kavitha,

For direct uploading from excel file try function module

'KCD_EXCEL_OLE_TO_INT_CONVERT it may help u.

or u can convert first ur excel file to text (tab deliminated and the u can upload it using 'GUI_UPLOAD' function module.

Try with any of these function modul it may exist.

Regards,

Rajesh Akarte

Re: Upload Excel into Internal table

Former Member — Thu, 13 Dec 2007 05:35:17 GMT

Hi,

Plz go through the following code.

Reward if useful.

Thankx.

report zhdownload.

data : begin of itab occurs 100,

f_matnr type matnr,

f_ersda type ersda,

f_ernam type ernam,

end of itab.

select matnr ersda ernam into table itab from mara up to 10 rows.

append itab.

data : begin of itab1 occurs 0,

line(50) type c,

end of itab1.

itab1-line = 'MATERIAL NUMBER'.

append itab1.

itab1-line = 'DATE'.

append itab1.

itab1-line = 'NAME'.

append itab1.

call function 'GUI_DOWNLOAD'

exporting

filename = 'C:\Documents and Settings\ak0020758\abc2.xls'

filetype = 'ASC'

write_field_separator = 'X'

IMPORTING
FILELENGTH =

tables

data_tab = itab

fieldnames = itab1 " for headings

exceptions

file_write_error = 1

no_batch = 2

gui_refuse_filetransfer = 3

invalid_type = 4

no_authority = 5

unknown_error = 6

header_not_allowed = 7

separator_not_allowed = 8

filesize_not_allowed = 9

header_too_long = 10

dp_error_create = 11

dp_error_send = 12

dp_error_write = 13

unknown_dp_error = 14

access_denied = 15

dp_out_of_memory = 16

disk_full = 17

dp_timeout = 18

file_not_found = 19

dataprovider_exception = 20

control_flush_error = 21

others = 22

if sy-subrc <> 0.

message id sy-msgid type sy-msgty number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

Re: Upload Excel into Internal table

kesavadas_thekkillath — Thu, 13 Dec 2007 06:30:54 GMT

Do include this also to check whether the uploaded fiel is excel or not ???

at selection-screen on p_file.

wk_len = p_file.

condense wk_len no-gaps.

lent = strlen( wk_len ).

wk_alp = lent - 4.

b = wk_len+wk_alp(lent).

if ( b ne '.xls' and b ne '.XLS' ).

message e939.

clear p_file.

stop.

endif.