topic Re: upload execl in Application Development and Automation Discussions

upload execl

Former Member — Mon, 12 Mar 2007 05:49:47 GMT

how to upload execl file into internal table?

jai

Re: upload execl

Former Member — Mon, 12 Mar 2007 05:52:44 GMT

ABAP code for uploading an Excel document into an internal table. See code below for structures. The

code is based on uploading a simple Excel spreadsheet or for an actual Excel file click here.

There are also a couple of alternatives which use fucntion modules 'KCD_EXCEL_OLE_TO_INT_CONVERT'

and 'ALSM_EXCEL_TO_INTERNAL_TABLE' but the method below is by far the simplest method to used.

Code:

REPORT zupload_excel_to_itab.

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE rlgrap-filename.

TYPES: BEGIN OF t_datatab,

col1(30) TYPE c,

col2(30) TYPE c,

col3(30) TYPE c,

END OF t_datatab.

DATA: it_datatab type standard table of t_datatab,

wa_datatab type t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

At selection screen

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

***********************************************************************

*START-OF-SELECTION.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = it_datatab[] "ACTUAL DATA

EXCEPTIONS

conversion_failed = 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.

***********************************************************************

END-OF-SELECTION.

END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3.

ENDLOOP.

Reward Point if Help full.

Reg,

Hiren Patel

Re: upload execl

Former Member — Mon, 12 Mar 2007 05:53:08 GMT

Hi Jaya,

Use the following function mdule for upload the data from excell to internal table.

ALSM_EXCEL_TO_INTERNAL_TABLE

Hope this helps you and reward for useful points.

Regards,

Kumar.

Re: upload execl

Former Member — Mon, 12 Mar 2007 15:58:05 GMT

Hi,

Use FM ALSM_EXCEL_TO_INTERNAL_TABLE

Re: upload execl

Former Member — Mon, 12 Mar 2007 16:01:34 GMT

hi jaya,

use the FM 'ALSM_EXCEL_TO_INTERNAL_TABLE'

check this sample code...

REPORT ZREPORTS NO STANDARD PAGE HEADING

LINE-COUNT 65(2).

PARAMETERS:

P_INFL like RLGRAP-FILENAME.

DATA:

BEGIN OF T_DATA1 OCCURS 0,

RESOURCE(25) TYPE C,

DATE(10) TYPE C,

DURATION TYPE P DECIMALS 2,

ACTIVITY(25) TYPE C,

B_NBILL(1) TYPE C,

END OF T_DATA1,

T_DATA TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE,

BEGIN OF T_FINAL OCCURS 0,

RESOURCE(25) TYPE C,

DATE(10) TYPE C,

DURATION(15) TYPE C,

ACTIVITY(25) TYPE C,

B_NBILL(10) TYPE C,

END OF T_FINAL.

DATA : HEADER TYPE XSTRING.

Work Variables Declaration.

CONSTANTS:

W_Y TYPE C VALUE 'Y',

W_N TYPE C VALUE 'N'.

Work area.

DATA:

WA_DATA LIKE T_FINAL.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_INFL.

PERFORM GET_FILENAME CHANGING P_INFL.

----

START-OF-SELECTION.

----

PERFORM UPLOAD_DATA_FROMEXCEL.

FORM UPLOAD_DATA_FROMEXCEL.

Downloading the data from presentation server

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_infl

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 8

I_END_ROW = 1000

TABLES

INTERN = T_DATA

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_fromexcel

&----

*& Form process_data

&----

text

----

FORM PROCESS_DATA .

T_FINAL-RESOURCE = 'Resource'.

T_FINAL-DATE = 'Date'.

T_FINAL-DURATION = 'Duration'.

T_FINAL-ACTIVITY = 'Activity'.

T_FINAL-B_NBILL = 'Billable'.

APPEND T_FINAL.

SORT T_DATA BY ROW COL.

LOOP AT T_DATA.

CASE T_DATA-COL.

WHEN 3.

T_DATA1-RESOURCE = T_DATA-VALUE.

WHEN 4.

T_DATA1-DATE = T_DATA-VALUE.

WHEN 5.

T_DATA1-DURATION = T_DATA-VALUE.

WHEN 6.

t_data1-activity = t_data-value.

WHEN 7.

T_DATA1-B_NBILL = T_DATA-VALUE.

ENDCASE.

AT END OF ROW.

COLLECT T_DATA1.

ENDAT.

ENDLOOP.

LOOP AT T_DATA1.

T_FINAL-RESOURCE = T_DATA1-RESOURCE.

T_FINAL-DATE = T_DATA1-DATE.

T_FINAL-DURATION = T_DATA1-DURATION.

T_FINAL-ACTIVITY = T_DATA1-ACTIVITY.

T_FINAL-B_NBILL = T_DATA1-B_NBILL.

APPEND T_FINAL.

ENDLOOP.

ENDFORM. " process_data

&----

*& Form get_filename

&----

FORM GET_FILENAME CHANGING P_FILENAME.

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

DEF_FILENAME = SPACE

DEF_PATH = P_FILENAME

MASK = ',. ,..'

MODE = 'O' " O = Open, S = Save

TITLE = BOX_TITLE

IMPORTING

FILENAME = P_FILENAME

EXCEPTIONS

INV_WINSYS = 1

NO_BATCH = 2

SELECTION_CANCEL = 3

SELECTION_ERROR = 4

OTHERS = 5.

CASE SY-SUBRC.
WHEN 1.
MESSAGE I999 WITH
'File selector not available on this windows system'(046).
WHEN 2.
MESSAGE E999 WITH
'Frontend function cannot be executed in background'(047).
WHEN 3.
MESSAGE I999 WITH 'Selection was cancelled'(048).
WHEN 4.
MESSAGE E999 WITH 'Communication error'(049).
WHEN 5.
MESSAGE E999 WITH 'Other error'(050).
ENDCASE.

ENDFORM. " get_filename

do reward if it helps,

priya.

Re: upload execl

Former Member — Tue, 13 Mar 2007 04:57:29 GMT

Hi,

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 will resolve your query.

Reward all the helpful answers.

Regards