How to read Excel file from application server

Former Member · ‎03-28-2007

Hi,

Can any one tell me how to read the excel file from application server

Is there any FM exitst for the same or any other way.

I used open dataset and read data set, but i am getting junk data

please suggest me a solution

urs

sreenivas

Former Member · ‎03-28-2007

Hi Sreenivas,

Check this documentation.

Steps to create a BDC program.

1. Create an internal table with fields same as Excel sheet fields.

2. Declare an internal table with BDCDATA table to store the BDC recording.

3. Declare an internal table with BDCMSGCOL to store the error messages after

the execution of the BDC.

4. Write the code using the fun. module ALSM_EXCEL_TO_INTERNAL_TABLE

to upload the data from the excel sheet.

5. loop that internal table and write the Subroutines to fill the internal table

BDCDATA with the recording of a specified Transaction Code.

6. Using Call Transaction execute the BDC recording.

7. Check Sy-subrc = 0 and store the error messages in the internal table.

8. If you want you can pass those error reecords to a session using the SESSION method.

Here is an example of a BDC program, but this program is from a text file. Change the function module WS_UPLOAD with ALSM_EXCEL_TO_INTERNAL_TABLE

to upload an excel and write the program with your BDC reocrding. You can do recording using t-code SHDB.

Sample Program:

REPORT ZRAJ_DATASET_XD01 NO STANDARD PAGE HEADING LINE-SIZE 132

LINE-COUNT 60

MESSAGE-ID Z00.

----

Table/Structure declarations. *

----

TABLES : KNA1. "Customer master

----

Constants declarations. *

----

CONSTANTS : C_MODE VALUE 'N',

C_UPDATE VALUE 'S',

C_X VALUE 'X',

C_SESS TYPE APQI-GROUPID VALUE 'ZCUSTOMER', "Session Name

C_XD01 LIKE TSTC-TCODE VALUE 'XD01'.

----

Variable declarations. *

----

DATA : V_FNAME(15) VALUE SPACE, " Name of file to be created

V_FAILREC TYPE I, " No of failed records

V_MSG(255), " Message Text

V_ERRREC TYPE I, " No of failed records

V_LINES TYPE I. " No of records

----

*-- FLAG DECLARATIONS

----

DATA : FG_DATA_EXIST VALUE 'X', " Check for data

FG_SESSION_OPEN VALUE ' '. " Check for Session Open

----

Structures / Internal table declarations *

----

*-- Structure to hold BDC data

TYPES : BEGIN OF T_BDCTABLE.

INCLUDE STRUCTURE BDCDATA.

TYPES END OF T_BDCTABLE.

*-- Structure to trap BDC messages

TYPES : BEGIN OF T_MSG.

INCLUDE STRUCTURE BDCMSGCOLL.

TYPES : END OF T_MSG.

*-- Structure to trap ERROR messages

TYPES : BEGIN OF T_ERR_MSG,

MESSAGE(255),

END OF T_ERR_MSG.

*--Internal table to store flat file data

DATA:BEGIN OF IT_KNA1 OCCURS 0,

KUNNR LIKE KNA1-KUNNR,

KTOKD LIKE T077D-KTOKD,

NAME1 LIKE KNA1-NAME1,

SORTL LIKE KNA1-SORTL,

ORT01 LIKE KNA1-ORT01,

PSTLZ LIKE KNA1-PSTLZ,

LAND1 LIKE KNA1-LAND1,

SPRAS LIKE KNA1-SPRAS,

LZONE LIKE KNA1-LZONE,

END OF IT_KNA1.

*-- Internal table to hold BDC data

DATA: IT_BDCDATA TYPE STANDARD TABLE OF T_BDCTABLE WITH HEADER LINE,

*-- Internal Table to store ALL messages

IT_MSG TYPE STANDARD TABLE OF T_MSG WITH HEADER LINE,

*-- Internal Table to store error messages

IT_ERR_MSG TYPE STANDARD TABLE OF T_ERR_MSG WITH HEADER LINE.

----

Selection Screen. *

----

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

PARAMETERS : P_FLNAME(15) OBLIGATORY.

SELECTION-SCREEN END OF BLOCK B1.

SELECTION-SCREEN BEGIN OF BLOCK B2 WITH FRAME TITLE TEXT-002.

SELECTION-SCREEN BEGIN OF LINE.

PARAMETERS : R_LIST RADIOBUTTON GROUP GRP1.

SELECTION-SCREEN COMMENT 5(20) TEXT-003.

PARAMETERS : R_SESS RADIOBUTTON GROUP GRP1.

SELECTION-SCREEN COMMENT 30(20) TEXT-004.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK B2.

----

Event:Initialization *

----

INITIALIZATION.

----

AT Selection Screen. *

----

AT SELECTION-SCREEN.

----

Event: Start-of-Selection *

----

START-OF-SELECTION.

V_FNAME = P_FLNAME.

PERFORM GET_DATA.

PERFORM GENERATE_DATASET.

----

Event: End-of-Selection *

----

END-OF-SELECTION.

IF FG_DATA_EXIST = ' '.

MESSAGE I010 WITH TEXT-009.

EXIT.

ENDIF.

PERFORM GENERATE_BDCDATA.

PERFORM DISPLAY_ERR_RECS.

----

Event: top-of-page

----

TOP-OF-PAGE.

//////////////////////////////////////////////////////////////////////

*

FORM DEFINITIONS *

*

*//////////////////////////////////////////////////////////////////////

&----

*& Form get_data

&----

Subroutine to get the data from mard

----

--> p1 text
<-- p2 text

----

FORM GET_DATA.

CALL FUNCTION 'UPLOAD'

EXPORTING

CODEPAGE = ' '

FILENAME = 'C:\XD01.TXT'

FILETYPE = 'DAT'

ITEM = ' '
FILEMASK_MASK = ' '
FILEMASK_TEXT = ' '
FILETYPE_NO_CHANGE = ' '
FILEMASK_ALL = ' '
FILETYPE_NO_SHOW = ' '
LINE_EXIT = ' '
USER_FORM = ' '
USER_PROG = ' '
SILENT = 'S'
IMPORTING
FILESIZE =
CANCEL =
ACT_FILENAME =
ACT_FILETYPE =

TABLES

DATA_TAB = IT_KNA1

EXCEPTIONS
CONVERSION_ERROR = 1
INVALID_TABLE_WIDTH = 2
INVALID_TYPE = 3
NO_BATCH = 4
UNKNOWN_ERROR = 5
GUI_REFUSE_FILETRANSFER = 6
OTHERS = 7

.

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 IT_KNA1[] IS INITIAL.

FG_DATA_EXIST = ' '.

ENDIF.

ENDFORM. " get_data

&----

*& Form GENERATE_DATASET

&----

text

----

--> p1 text
<-- p2 text

----

FORM GENERATE_DATASET.

MESSAGE I010 WITH 'OPENING FILE IN APPLICATION SERVER'.

**--Creating a data set in application server

OPEN DATASET V_FNAME FOR OUTPUT IN TEXT MODE.

**---Transfering data from internal table to dataset

MESSAGE I010 WITH 'TRANSFERING DATA FROM INETERAL TABLE TO THE FILE'.

LOOP AT IT_KNA1.

TRANSFER IT_KNA1 TO V_FNAME.

ENDLOOP.

**--Closing the dataset

MESSAGE I010 WITH 'CLOSING THE FILE'.

CLOSE DATASET V_FNAME.

ENDFORM. " GENERATE_DATASET

&----

*& Form BDC_DYNPRO

&----

text

----

-->P_0467 text
-->P_0468 text

----

FORM BDC_DYNPRO USING PROGRAM DYNPRO.

CLEAR IT_BDCDATA.

IT_BDCDATA-PROGRAM = PROGRAM.

IT_BDCDATA-DYNPRO = DYNPRO.

IT_BDCDATA-DYNBEGIN = 'X'.

APPEND IT_BDCDATA.

ENDFORM.

&----

*& Form BDC_FIELD

&----

text

----

-->P_0472 text
-->P_0473 text

----

FORM BDC_FIELD USING FNAM FVAL.

IF NOT FVAL IS INITIAL.

CLEAR IT_BDCDATA.

IT_BDCDATA-FNAM = FNAM.

IT_BDCDATA-FVAL = FVAL.

APPEND IT_BDCDATA.

ENDIF.

ENDFORM.

&----

*& Form GENERATE_BDCDATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM GENERATE_BDCDATA.

REFRESH IT_KNA1.

Opening dataset for reading

OPEN DATASET V_FNAME FOR INPUT IN TEXT MODE.

Reading the file from application server

DO.

CLEAR: IT_KNA1,IT_BDCDATA.

REFRESH IT_BDCDATA.

READ DATASET V_FNAME INTO IT_KNA1.

IF SY-SUBRC <> 0.

EXIT.

ELSE.

Populate BDC Data for Initial Screen

PERFORM : BDC_DYNPRO USING 'SAPMF02D' '0100',

BDC_FIELD USING 'BDC_CURSOR' 'RF02D-KUNNR',

BDC_FIELD USING 'BDC_OKCODE' '/00',

BDC_FIELD USING 'RF02D-KUNNR' IT_KNA1-KUNNR,

BDC_FIELD USING 'RF02D-KTOKD' IT_KNA1-KTOKD.

Populate BDC Data for Second Screen

PERFORM : BDC_DYNPRO USING 'SAPMF02D' '0110',

BDC_FIELD USING 'BDC_CURSOR' 'KNA1-NAME1',

BDC_FIELD USING 'BDC_OKCODE' '/00',

BDC_FIELD USING 'KNA1-NAME1' IT_KNA1-NAME1,

BDC_FIELD USING 'KNA1-SORTL' IT_KNA1-SORTL,

BDC_FIELD USING 'KNA1-ORT01' IT_KNA1-ORT01,

BDC_FIELD USING 'KNA1-PSTLZ' IT_KNA1-PSTLZ,

BDC_FIELD USING 'KNA1-LAND1' IT_KNA1-LAND1,

BDC_FIELD USING 'KNA1-SPRAS' IT_KNA1-SPRAS.

Populate BDC Data for Third Screen

PERFORM : BDC_DYNPRO USING 'SAPMF02D' '0120',

BDC_FIELD USING 'BDC_CURSOR' 'KNA1-LZONE',

BDC_FIELD USING 'BDC_OKCODE' '=UPDA',

BDC_FIELD USING 'KNA1-LZONE' IT_KNA1-LZONE.

CALL TRANSACTION C_XD01 USING IT_BDCDATA

MODE C_MODE

UPDATE C_UPDATE

MESSAGES INTO IT_MSG.

IF SY-SUBRC <> 0.

*--In case of error list display

IF R_LIST = C_X.

V_ERRREC = V_ERRREC + 1.

PERFORM FORMAT_MESSAGE.

IT_ERR_MSG-MESSAGE = V_MSG.

APPEND IT_ERR_MSG.

CLEAR : V_MSG,IT_ERR_MSG.

ENDIF.

*--In case of session log

IF R_SESS = C_X.

*-- In case of transaction fails.

IF FG_SESSION_OPEN = ' '.

FG_SESSION_OPEN = C_X.

PERFORM BDC_OPEN_GROUP.

ENDIF. " IF FG_SESSION_OPEN = ' '.

*-- Insert BDC Data..

PERFORM BDC_INSERT_DATA.

ENDIF. " IF R_SESS = C_X.

ENDIF. " IF SY-SUBRC <> 0.

ENDDO.

Closing the dataset

CLOSE DATASET V_FNAME.

*-- Close the session if opened

IF FG_SESSION_OPEN = C_X.

PERFORM BDC_CLOSE_GROUP.

CALL TRANSACTION 'SM35'.

ENDIF.

ENDFORM. " GENERATE_BDCDATA

&----

*& Form BDC_OPEN_GROUP

&----

text

----

--> p1 text
<-- p2 text

----

FORM BDC_OPEN_GROUP.

CALL FUNCTION 'BDC_OPEN_GROUP'

EXPORTING

CLIENT = SY-MANDT

DEST = FILLER8

GROUP = C_SESS

HOLDDATE = FILLER8

KEEP = C_X

USER = SY-UNAME

RECORD = FILLER1
IMPORTING
QID =

EXCEPTIONS

CLIENT_INVALID = 1

DESTINATION_INVALID = 2

GROUP_INVALID = 3

GROUP_IS_LOCKED = 4

HOLDDATE_INVALID = 5

INTERNAL_ERROR = 6

QUEUE_ERROR = 7

RUNNING = 8

SYSTEM_LOCK_ERROR = 9

USER_INVALID = 10

OTHERS = 11

.

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. " BDC_OPEN_GROUP

&----

*& Form BDC_INSERT_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM BDC_INSERT_DATA.

CALL FUNCTION 'BDC_INSERT'

EXPORTING

TCODE = C_XD01

POST_LOCAL = NOVBLOCAL
PRINTING = NOPRINT

TABLES

DYNPROTAB = IT_BDCDATA

EXCEPTIONS

INTERNAL_ERROR = 1

NOT_OPEN = 2

QUEUE_ERROR = 3

TCODE_INVALID = 4

PRINTING_INVALID = 5

POSTING_INVALID = 6

OTHERS = 7

.

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. " BDC_INSERT_DATA

&----

*& Form BDC_CLOSE_GROUP

&----

text

----

--> p1 text
<-- p2 text

----

FORM BDC_CLOSE_GROUP.

CALL FUNCTION 'BDC_CLOSE_GROUP'

EXCEPTIONS

NOT_OPEN = 1

QUEUE_ERROR = 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. " BDC_CLOSE_GROUP

&----

*& Form FORMAT_MESSAGE

&----

text

----

--> p1 text
<-- p2 text

----

FORM FORMAT_MESSAGE.

CLEAR V_LINES.

DESCRIBE TABLE IT_MSG LINES V_LINES.

READ TABLE IT_MSG INDEX V_LINES.

CLEAR V_MSG.

CALL FUNCTION 'FORMAT_MESSAGE'

EXPORTING

ID = IT_MSG-MSGID

LANG = IT_MSG-MSGSPRA

NO = IT_MSG-MSGNR

V1 = IT_MSG-MSGV1

V2 = IT_MSG-MSGV2

V3 = IT_MSG-MSGV3

V4 = IT_MSG-MSGV4

IMPORTING

MSG = V_MSG

EXCEPTIONS

NOT_FOUND = 1

OTHERS = 2.

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. " FORMAT_MESSAGE

&----

*& Form DISPLAY_ERR_RECS

&----

text

----

--> p1 text
<-- p2 text

----

FORM DISPLAY_ERR_RECS.

LOOP AT IT_ERR_MSG.

WRITE: / IT_ERR_MSG-MESSAGE.

ENDLOOP.

ENDFORM. " DISPLAY_ERR_RECS

This is a sample BDC program.

And

To simply load Excel to Internal table follow this :

First of all , before you move your data from excel sheet to the internal table, you need to specify a location for uploading the excel sheet into your internal table.

for this u need to use two specific FM's .

the first one for file selection : WS_FILENAME_GET.

The second one for data upload : TEXT_CONVERT_XLS_TO_SAP.

Afetr doing this the regular part of mapping comes and then we can run the session and then execute and release it.

Hope this resolves your query.

Reward all the helpful answers.

Regards

Former Member · ‎03-28-2007

You have to use OPEN DATASET only. I don't think any fm is available.

Former Member · ‎03-28-2007

I think we can read text files easily , no probs doing this.

but while reading excel file, it is getting junk data using open dataset.

Former Member · ‎03-28-2007

Hi sreenivas..

below is a very simple code...where u can use open dataset and read teh contents on the file of application server...

PARAMETERS: P_FILE LIKE RLGRAP-FILENAME.

DATA: T_FILE TYPE STRING.

DATA: DT_FILE like RLGRAP-FILENAME VALUE 'file1.txt'.

*PARAMETERS: P_FILE TYPE PFEFLNAMEL.

DATA: BEGIN OF ITAB OCCURS 0 ,

INDEX LIKE SY-INDEX,

NAME(50),

CITY(50),

END OF ITAB.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = 'P_FILE'

IMPORTING

FILE_NAME = P_FILE.

START-OF-SELECTION.

T_FILE = P_FILE.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = T_FILE

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

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

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

.

IF SY-SUBRC <> 0.

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

ENDIF.

OPEN DATASET dt_FILE FOR INPUT IN TEXT MODE ENCODING DEFAULT.

READ DATASET dt_FILE INTO ITAB.

LOOP AT ITAB.

WRITE 😕 ITAB-NAME,ITAB-CITY.

ENDLOOP.

hope this helps u a bit,

all the best,

regards,

sampath

mark helpful answers

Former Member · ‎03-28-2007

Hi ,

You can easily read the Text Files and also CSV files from the application server . For CSV files use Open Dataset and then split the values that you get at ',' into an internal table.

Hope this helps you.

Regards,

Sowmya.