topic Re: upload from excel file in Application Development and Automation Discussions

upload from excel file

Former Member — Tue, 21 Aug 2007 10:09:31 GMT

i want to upload data from an excel file in BDC.

how to do that?

i could upload from .txt file.

Re: upload from excel file

Former Member — Tue, 21 Aug 2007 10:12:28 GMT

Hi Chrsity

here check this program i am spliting the record depend upon ',' and dont use ws_upload etc fm's are obsolete....

data: begin of itab_string occurs 0,

record type char255,

end of itab_string.

data: L_FILETABLE TYPE FILETABLE,

L_FILETAB_H TYPE FILETABLE WITH HEADER LINE.

data: p_file1 type string.

selection screen .

PARAMETERS: P_FILE TYPE LOCALFILE.

initialization.

at selection-screen on value-request for P_FILE.

IF THE USER SELECT EXTENTION BUTTON IT WILL OPEN THE LOCAL DIRECTORY FOR SELECTING THE FILE LOCATION.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG

EXPORTING
WINDOW_TITLE =
DEFAULT_EXTENSION = 'CSV'
DEFAULT_FILENAME = 'C:\Documents and Settings\196093\Desktop\STATUS.csv'
FILE_FILTER =
INITIAL_DIRECTORY = 'C:\Documents and Settings\196093\Desktop\'
MULTISELECTION =
WITH_ENCODING =

CHANGING

FILE_TABLE = L_FILETABLE

RC = RC

USER_ACTION =
FILE_ENCODING =

EXCEPTIONS

FILE_OPEN_DIALOG_FAILED = 1

CNTL_ERROR = 2

ERROR_NO_GUI = 3

NOT_SUPPORTED_BY_GUI = 4

others = 5

IF SY-SUBRC <> 0.

ELSE.

LOOP AT l_filetable INTO L_FILETAB_H.

P_FILE = L_FILETAB_H-FILENAME.

move p_file to p_file1.

EXIT.

ENDLOOP.

ENDIF.

passing the selected file name to gui_upload for loading the data
into internal table

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = p_file1

FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = ' '
HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = ' '
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
CHECK_BOM = ' '
NO_AUTH_CHECK = ' '
IMPORTING
FILELENGTH =
HEADER =

TABLES

DATA_TAB = itab_string

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 I000(Z00) WITH 'PLEASE PROVIDE CORRECT FILE NAME'.

ENDIF.

loop at itab_string.

now split the statuses

split itab_string at ',' into itab_status-aufnr itab_status-asttx itab_status-asttx1.

and move one internal table

append itab_status.

clear itab_status.

endloop.

reward points to all helpful answers

kiran.M

Re: upload from excel file

Former Member — Tue, 21 Aug 2007 10:14:24 GMT

Hi,

Use FM's ALSM_EXCEL_TO_INTERNAL_TABLE

or convert your file to text file and use UPLOAD/GUI_UPLOAD

Regards,

Sankar

Re: upload from excel file

shishupalreddy — Tue, 21 Aug 2007 10:17:58 GMT

hi ,

use the following...

FORM upload_data .

DATA : l_start_col TYPE i VALUE '1',

l_start_row TYPE i VALUE '1',

l_end_col TYPE i VALUE '25',

l_end_row TYPE i VALUE '65536'.

file_in = p_f_in.

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = file_in

i_begin_col = l_start_col

i_begin_row = l_start_row

i_end_col = l_end_col

i_end_row = l_end_row

TABLES

intern = t_data_in

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.

ENDFORM. " upload_Data

&----

*& Form format_data

&----

Format the read data from t_data_in to g_t_itab.

----

FORM format_data .

DATA : l_index TYPE i.

FIELD-SYMBOLS : <l_fs>.

CLEAR: g_t_itab.

SORT t_data_in BY row col.

*---Format the data uploaded

LOOP AT t_data_in .

MOVE t_data_in-col TO l_index .

ASSIGN COMPONENT l_index OF STRUCTURE g_t_itab TO <l_fs>.

MOVE t_data_in-value TO <l_fs>.

AT END OF row.

APPEND g_t_itab.

CLEAR g_t_itab.

ENDAT.

ENDLOOP.

ENDFORM. " format_data

regards,

Re: upload from excel file

Former Member — Tue, 21 Aug 2007 10:23:33 GMT

Hi george,

U cant upload from .xls,

u can go for .txt/.csv.

Reward points if helpful,

Thanks

Naveen khan

Re: upload from excel file

Former Member — Tue, 21 Aug 2007 10:58:45 GMT

Just i am giving a upload the data from text and excel sheet and displaying , just go through

tables :T001.

data : begin of ITAB occurs 0,

BUKRS like T001-BUKRS,

BUTXT like T001-BUTXT,

ORT01 like T001-ORT01,

LAND1 like T001-LAND1,

WAERS like T001-WAERS,

end of ITAB.

data : WA_T001_EXCEL like ITAB.

data : IT_T001_EXCEL like standard table of WA_T001_EXCEL.

data IT_TEXT like ITAB occurs 0 with header line.

data : IT_RETURN like standard table of ALSMEX_TABLINE,

WA_RETURN like ALSMEX_TABLINE,

WA_RETURNS like ALSMEX_TABLINE.

data : V_FILE type STRING,V_MASK(10) type C.

selection-screen begin of block B1 with frame title TEXT-001.

parameters P_FILE like RLGRAP-FILENAME .

selection-screen end of block B1.

selection-screen begin of block B2 with frame title TEXT-002.

parameter : R_TXT radiobutton group G1 default 'X' user-command C1,

R_EXCEL radiobutton group G1.

selection-screen end of block B2.

at selection-screen on value-request for P_FILE.

if R_TXT = 'X'.

V_MASK = '*TXT'.

elseif R_EXCEL = 'X'.

V_MASK = '*XLS'.

endif.

call function 'KD_GET_FILENAME_ON_F4'

exporting

PROGRAM_NAME = SYST-REPID

DYNPRO_NUMBER = SYST-DYNNR

MASK = V_MASK

changing

FILE_NAME = P_FILE.

at selection-screen.

translate P_FILE to upper case.

if R_TXT = 'X'.

search P_FILE for '*TXT'.

if SY-SUBRC <> 0.

message E011.

endif.

if R_EXCEL = 'X'.

search P_FILE for '*XLS'.

if SY-SUBRC <> 0.

message E012.

endif.

start-of-selection.

V_FILE = P_FILE.

if R_TXT = 'X'.

call function 'GUI_UPLOAD'

exporting

FILENAME = V_FILE

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

tables

DATA_TAB = IT_TEXT.

if SY-SUBRC eq 0.

message I013 with V_FILE.

loop at IT_TEXT .

write 😕 IT_TEXT-BUKRS color 4, IT_TEXT-BUTXT color 3,IT_TEXT-ORT01

color 5,

IT_TEXT-LAND1 color 2,IT_TEXT-WAERS color 1.

endloop.

endif.

elseif R_EXCEL = 'X'.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

FILENAME = P_FILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 4

I_END_ROW = 2

tables

INTERN = IT_RETURN.

if SY-SUBRC eq 0.

message I014 with P_FILE.

sort IT_RETURN by ROW COL.

loop at IT_RETURN into WA_RETURNS.

WA_RETURN = WA_RETURNS.

case WA_RETURN-COL.

when 1.

WA_T001_EXCEL-BUKRS = WA_RETURN-VALUE.

when 2.

WA_T001_EXCEL-BUTXT = WA_RETURN-VALUE.

when 3.

WA_T001_EXCEL-ORT01 = WA_RETURN-VALUE.

when 4.

WA_T001_EXCEL-LAND1 = WA_RETURN-VALUE.

endcase.

at end of ROW.

append WA_T001_EXCEL to IT_T001_EXCEL.

clear : WA_RETURN, WA_T001_EXCEL.

endat.

endloop.

endif.

loop at IT_T001_EXCEL into WA_T001_EXCEL.

write 😕 WA_T001_EXCEL-BUKRS color 1, WA_T001_EXCEL-BUTXT color 2,

WA_T001_EXCEL-ORT01 color 3,WA_T001_EXCEL-LAND1 color 4.

endloop.

endif.