topic Upload data from Excel file in Application Development and Automation Discussions

Upload data from Excel file

Former Member — Tue, 10 Oct 2006 10:08:42 GMT

Hi experts,

I have Excel file which contains data in 2 sheets in a single Excel file.

Sheet1 contains Headr item input data.

Sheet2 contains Item input data.

So how can we upload this data into 2 different internal table?

Regards

Re: Upload data from Excel file

shishupalreddy — Tue, 10 Oct 2006 10:14:35 GMT

Hi ,

Use this fm

ALSM_EXCEL_TO_INTERNAL_TABLE

otherwise create two excel sheets with .CSV format and use GUI_UPLAOD fm to upload data into two internal tables

regards,

Re: Upload data from Excel file

Former Member — Tue, 10 Oct 2006 10:20:10 GMT

Hi Ravi,

You need to upload both the excel sheets one by one.

Keep two Input parameter for file selection and upload the excel sheet.

Refer the code:

DATA li_intern TYPE STANDARD TABLE OF alsmex_tabline.

DATA ws_intern TYPE alsmex_tabline.

DATA ws_intern1 TYPE alsmex_tabline.

CONSTANTS: lc_extn TYPE string VALUE '.XLS',

lc_directory TYPE string VALUE 'C:\'.

REFRESH li_intern.

lws_title = text-003.

CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

window_title = lws_title

default_extension = lc_extn

initial_directory = lc_directory

CHANGING

file_table = li_file_table

rc = lws_rc

user_action = lws_user_action

EXCEPTIONS

file_open_dialog_failed = 1

cntl_error = 2

error_no_gui = 3

not_supported_by_gui = 4

OTHERS = 5.

IF sy-subrc <> 0.

MESSAGE i050 WITH text-004.

EXIT.

ENDIF.

IF lws_user_action <> cl_gui_frontend_services=>action_ok.

EXIT.

STOP.

ENDIF.

READ TABLE li_file_table INDEX 1 INTO wa_file_table.

IF sy-subrc = 0.

lws_filename = wa_file_table-filename.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = lws_filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 26

i_end_row = 9999

TABLES

intern = li_intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE i083 WITH text-003.

EXIT.

ENDIF.

PERFORM f2100_format_uploaded_data TABLES li_intern.

ENDIF.

ENDFORM. " f2000_upload

Manish.

Re: Upload data from Excel file

Former Member — Tue, 10 Oct 2006 10:22:34 GMT

Please use the FM

This should get you the data into the internal table in the same format as excel.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER =

i_tab_raw_data = wa_rawdata

i_filename = filename

tables

i_tab_converted_data = itab

EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2

Re: Upload data from Excel file

Former Member — Tue, 10 Oct 2006 10:42:23 GMT

Hi Mahesh,

Thanks for replies.

My requirement is In a <b>Single Excel File</b>there are two sheets.Sheet1 and Sheet2.So sheet1 contains header data and sheet contains item data.

So the code which you suggested will work for above requirement?

Regards

Re: Upload data from Excel file

anversha_s — Tue, 10 Oct 2006 10:48:31 GMT

hi,

chk this sample code.

types: begin of ttab,

rec(1000) type c,

end of ttab.

types: begin of tdat,

fld1(10) type c,

fld2(10) type c,

fld3(10) type c,

end of tdat.

data: itab type table of ttab with header line.

data: idat type table of tdat with header line.

data: file_str type string.

parameters: p_file type localfile.

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.

file_str = p_file.

call function 'GUI_UPLOAD'

exporting

filename = file_str

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.

delete itab index 1.

loop at itab.

clear idat.

split itab-rec at cl_abap_char_utilities=>horizontal_tab

into idat-fld1

idat-fld2

idat-fld3.

append idat.

endloop.

loop at idat.

write:/ idat-fld1, idat-fld2, idat-fld3.

endloop.

rgds

anver

if hlped mark points