topic Re: Reading data from MS-Excel to ABAP program in Application Development and Automation Discussions

Reading data from MS-Excel to ABAP program

Former Member — Fri, 17 Aug 2007 08:41:58 GMT

Hello Experts

I have a requirement to read certain data from Excel file stored in the user's local machine to my ABAP program. Can anyone suggest me the way to do so or pointers to documentation on this functionality.

Thanks in anticipation.

Best Regards

Anand.

Re: Reading data from MS-Excel to ABAP program

Former Member — Fri, 17 Aug 2007 08:44:57 GMT

Use the FM ALSM_EXCEL_TO_INTERNAL_TABLE.

parameters : p_ifile like rlgrap-filename obligatory.

data t_ifile like table of alsmex_tabline with header line.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = p_ifile

i_begin_col = 1

i_begin_row = 1

i_end_col = 100

i_end_row = 5000

tables

intern = t_ifile

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

Re: Reading data from MS-Excel to ABAP program

Former Member — Fri, 17 Aug 2007 08:47:27 GMT

ALSM_EXCEL_TO_INTERNAL_TABLE

SAP_CONVERT_TO_XLS_FORMAT

U can also use

'KCD_EXCEL_OLE_TO_INT_CONVERT' FM

to upload data from excel into internal table.

1. CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 3

i_end_row = 65535

TABLES

intern = it_vbak.

2. DATA : l_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.

DATA : l_index TYPE i.

DATA : l_start_col TYPE i VALUE '1',

l_start_row TYPE i VALUE '1',

l_end_col TYPE i VALUE '256',

l_end_row TYPE i VALUE '65536'.

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = p_file

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 = l_intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

regards,

srinivas

<b>*reward for useful answers*</b>

Re: Reading data from MS-Excel to ABAP program

Former Member — Fri, 17 Aug 2007 08:50:01 GMT

Hi Anand

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: Reading data from MS-Excel to ABAP program

Former Member — Fri, 17 Aug 2007 09:02:46 GMT

Hi,

FM: ALSM_EXCEL_TO_INTERNAL_TABLE is the right choice.

Regs,

Venkat