topic Re: How to read data from an excel using abap program? in Application Development and Automation Discussions

How to read data from an excel using abap program?

Former Member — Thu, 19 Apr 2007 09:58:57 GMT

I have to write an abap program to read the contents of ms excel across various

sheets. Can any one help me regarding this

Thanks in advance

Satish

Re: How to read data from an excel using abap program?

Former Member — Thu, 19 Apr 2007 10:04:55 GMT

Hi Satish

I think you can use FM ALSM_EXCEL_TO_INTERNAL_TABLE

Hope this helps,

Regards

Caglar

Re: How to read data from an excel using abap program?

Former Member — Thu, 19 Apr 2007 10:06:48 GMT

Hi,

Save the excel file as Tab delimited text file.

You can use FM : WS_UPLOAD as follows.

CALL FUNCTION 'WS_UPLOAD'

EXPORTING

CODEPAGE = ' '

FILENAME = P_FILE ( file path )

FILETYPE = 'DAT'

TABLES

DATA_TAB = IT_FILE ( internal Table to which data tobe uploaded )

EXCEPTIONS

CONVERSION_ERROR = 1

FILE_OPEN_ERROR = 2

FILE_READ_ERROR = 3

INVALID_TABLE_WIDTH = 4

INVALID_TYPE = 5

NO_BATCH = 6

UNKNOWN_ERROR = 7

GUI_REFUSE_FILETRANSFER = 8

CUSTOMER_ERROR = 9

OTHERS = 10

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ELSE.

CLEAR IT_FILE.

ENDIF.

Reward if useful.

Thanks,

USR

Re: How to read data from an excel using abap program?

Former Member — Thu, 19 Apr 2007 10:09:22 GMT

Hai Satish,

You save all your Excel sheets as Text Tab Delimited.

And then for each teext file can be uploaded into your daabase table.

See the Report that i have done.

REPORT Z_UPLOAD_TO_DATABASE_TABLE.

TABLES:

ZDETMAST. " Database table to be uploaded

"----

Data declaration of the structure to hold ZDETMAST(zdetcode) data *

"----

DATA:

BEGIN OF FS_ZDETMAST,

CNT(3) TYPE C, " Client

DETCODE(15) TYPE C, " Det Code

DETCAT(1) TYPE C, " Det category

DETTYPE(2) TYPE N, " Det type

DETDESC(30) TYPE C, " Det description

PFLAG(1) TYPE C, " Det Flag

TOTALDR(10) TYPE C, " Total DR

TOTALCR(10) TYPE C, " Total CR

END OF FS_ZDETMAST.

"----

Internal table to hold ZDETMAST(zdetcode) data *

"----

DATA:

T_ZDETMAST LIKE STANDARD TABLE OF FS_ZDETMAST.

*"----

Function Module to upload ZDETMAST Data

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'D:/ZDETMAST_2.TXT'

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = 'DAT'
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
CHECK_BOM = ' '
VIRUS_SCAN_PROFILE =
IMPORTING
FILELENGTH =
HEADER =

TABLES

DATA_TAB = T_ZDETMAST

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. " IF SY-SUBRC <> 0.

LOOP AT T_ZDETMAST INTO FS_ZDETMAST.

WRITE:

/ FS_ZDETMAST-CNT,

FS_ZDETMAST-DETCODE,

FS_ZDETMAST-DETCAT,

FS_ZDETMAST-DETTYPE,

FS_ZDETMAST-DETDESC,

FS_ZDETMAST-PFLAG,

FS_ZDETMAST-TOTALDR,

FS_ZDETMAST-TOTALCR.

ENDLOOP. " LOOP AT T_ZDETMAST ...

LOOP AT T_ZDETMAST INTO ZDETMAST.

IF SY-TABIX GE 2.

INSERT ZDETMAST.

DELETE ZDETMAST.

ENDIF. " IF SY-TABIX EQ 2

ENDLOOP. " LOOP AT T_ZDETMAST

Hope this helps you a lot.

You can also use ALSM_EXCEL_TO_INTERNAL_TABLE FM to have excel data in ITAB AND THEN YOU CAN INSERT DATA INTO DATABASE TABLE.

Reward points if it helps you.

Regds,

Rama chary.Pammi