How to read data from an excel using abap program?

[Former Member]

Hi

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

[Former Member]

Hi Satish

I think you can use FM ALSM_EXCEL_TO_INTERNAL_TABLE

Hope this helps,

Regards

Caglar

[Former Member]

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

[Former Member]

Hai Satish,

<b>You save all your Excel sheets as Text Tab Delimited.</b>

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.

<b>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.</b>

<b>Reward points if it helps you.</b>

Regds,

Rama chary.Pammi