Re: Excel in server to internal table

Former Member — Wed, 04 Jul 2007 11:18:15 GMT

Hi,

go through the following example.....

D E S C : USING BAPI WE SHOULD TRANSFER THE DATA FROM EXCEL TO SAP TABLE. HERE WE ARE USING THE F.M. BAPI_ACC_DOCUMENT_CHECK

TO VERIFY WHETHER WE CAN POST THE DOCUMENT OR NOT AND IF YES

WE USE ONE MORE F.M. BAPI_ACC_DOCUMENT_POST TO POST THE

DOCUMENT OR TO SAVE THE DATA.

----

T A B L E D E C L E R A T I O N

*----

TABLES : T001, " COMPANY CODES

SKB1, " G/L account master (company code)

BKPF, " Accounting Document Header

BSEG. " Accounting Document Segment

----

I N T E R N A L T A B L E

----

INTERNAL TABLE FOR HEADER INFORMATION USED IN BAPI FUNCTION CALLED
BAPI_ACC_DOCUMENT_CHECK

DATA : I_BAPI_HEADER LIKE BAPIACHE09.

DATA : I_BAPI_HEADER_OBJ_KEY LIKE BAPIACHE09-OBJ_KEY.

INTERNAL TABLE FOR DETAIL INFORMATION USED IN BAPI FUNCTION CALLED
BAPI_ACC_DOCUMENT_CHECK.

DATA : I_BAPI_DETAIL LIKE BAPIACGL09 OCCURS 0 WITH HEADER LINE.

*INTERNAL TABLE FOR CURRENCY INFO USED IN SAME FUNCTION.

DATA : I_BAPI_AMT LIKE BAPIACCR09 OCCURS 0 WITH HEADER LINE.

*INTERNAL TABLE FOR MESSAGES INFO USED IN SAME FUNCTION.

DATA : I_BAPI_RETURN LIKE BAPIRET2 OCCURS 0 WITH HEADER LINE.

DATA : I_HEADER1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA : I_DETAIL1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

----

V A R I A B L E D E C L E R A T I O N

----

DATA: V_ACTYPE(10) ,

V_INDICATOR(2).

DATA: V_DOC(20) ,

V_FSYEAR(4) ,

V_POSTING_KEY(2) ,

FNAME(70).

DATA: SPECIALCHAR(29) VALUE '~`!@#$%^&*()_-+=|\}]{[":;?/><'.

DATA: V_DATE LIKE SY-DATUM.

DATA: N , ADDREC , ERRORFLAG , ERRORFLAG1 TYPE I ,COUNT .

----

S E L E C T I O N - S C R E E N

----

PARAMETERS : P_FILE LIKE RLGRAP-FILENAME.

----

V A L I D A T I O N & H E L P

----

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = 'Z8VIJAY_BAPI_EXCEL_TO_SAP'

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = ' '

IMPORTING

FILE_NAME = P_FILE.

----

S T A R T - O F - S E L E C T I O N

----

START-OF-SELECTION.

PERFORM POPULATE_HEADER_DATA_FRM_EXCEL.

PERFORM ARRANGE_HEADER_DATA.

PERFORM POPULATE_DETAIL_DATA_FRM_EXCEL.

PERFORM ARRANGE_DETAIL_DATA.

PERFORM CALL_BAPI_FN_CHECK.

FNAME = 'CHECK'.

PERFORM DISPLAY_ERROR_FROM_FN USING FNAME.

PERFORM CALL_BAPI_FN_POST.

FNAME = 'POST'.

PERFORM DISPLAY_ERROR_FROM_FN USING FNAME.

*----~~-SUB-ROUTINE TO GET THE HEADER DATA FROM EXCEL FILE~~-- .

&----

*& Form POPULATE_HEADER_DATA_FRM_EXCEL

&----

text

----

--> p1 text
<-- p2 text

----

FORM POPULATE_HEADER_DATA_FRM_EXCEL .

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = 12

I_BEGIN_ROW = 17

I_END_COL = 13

I_END_ROW = 24

TABLES

INTERN = I_HEADER1

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

*--~~-SUB-ROUTINE TO ARRANGE THE HEADER DATA~~----

&----

*& Form ARRANGE_HEADER_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM ARRANGE_HEADER_DATA .

LOOP AT I_HEADER1 WHERE COL = 2.

CASE I_HEADER1-ROW.

WHEN 1.

I_BAPI_HEADER-COMP_CODE = I_HEADER1-VALUE. "CO-CODE

WHEN 2.

I_BAPI_HEADER-DOC_TYPE = I_HEADER1-VALUE. "DOC-TYPE

WHEN 4.

REPLACE ALL OCCURRENCES OF '.' IN I_HEADER1-VALUE WITH '/'.

PERFORM CONV_DATE.

I_BAPI_HEADER-DOC_DATE = V_DATE. "DOC-DATE

WHEN 5.

REPLACE ALL OCCURRENCES OF '.' IN I_HEADER1-VALUE WITH '/'.

PERFORM CONV_DATE.

I_BAPI_HEADER-PSTNG_DATE = V_DATE. "POST-DATE

WHEN 7.

I_BAPI_HEADER-REF_DOC_NO = I_HEADER1-VALUE. "REFERENCE

WHEN 8.

I_BAPI_HEADER-HEADER_TXT = I_HEADER1-VALUE. "SHORT TEXT

ENDCASE.

ENDLOOP.

I_BAPI_HEADER-OBJ_TYPE = 'BKPFF'.

*OBJ_KEY IS THE REFERENCE NO OF FORMAT

*REF_NO + CLIENT + ORG. NO + FISCAL YEAR

*10 LENGTH + 3 LENGTH + 3 LENGTH +4 LENGTH = 20

"TO GET THE FISCAL YEAR CALL FUNCTION GET_FISCAL_YEAR

PERFORM GETFISCALYEAR.

UNPACK I_BAPI_HEADER-REF_DOC_NO TO V_DOC.

CONCATENATE V_DOC '001' SY-MANDT V_FSYEAR INTO I_BAPI_HEADER_OBJ_KEY.

I_BAPI_HEADER-OBJ_KEY = I_BAPI_HEADER_OBJ_KEY.

I_BAPI_HEADER-USERNAME = 'TRAINEE'.

I_BAPI_HEADER-BUS_ACT = 'RFBU'.

I_BAPI_HEADER-OBJ_SYS = SY-SYSID.

ENDFORM.

*----~~-SUB-ROUTINE FOR GET THE DETAIL DATA FROM EXCEL FILE~~--

&----

*& Form POPULATE_DETAIL_DATA_FRM_EXCEL

&----

text

----

--> p1 text
<-- p2 text

----

FORM POPULATE_DETAIL_DATA_FRM_EXCEL .

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 27

I_END_COL = 16

I_END_ROW = 44

TABLES

INTERN = I_DETAIL1

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

*----~~SUB-ROUTINE FOR ARRANGE THE DETAIL DATA~~----

&----

*& Form ARRANGE_DETAIL_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM ARRANGE_DETAIL_DATA .

IF I_DETAIL1 IS INITIAL.

MESSAGE I001(Z8VIJAY) WITH 'NO LINE ITEM FOUND'.

ELSE.

LOOP AT I_DETAIL1.

CASE I_DETAIL1-COL.

WHEN 1.

IF I_DETAIL1-VALUE CS '(' AND I_DETAIL1-VALUE CS ')' .

" CS = CONTAINS STRING

ADDREC = 0.

CONTINUE.

ELSE.

unpack I_DETAIL1-VALUE to I_BAPI_DETAIL-PROFIT_CTR.

ENDIF.

WHEN 3.

IF IT_DETAIL1-VALUE = 'XX'.
ADDREC = 0.
EXIT.
ELSE.

I_BAPI_DETAIL-BUS_AREA = I_DETAIL1-VALUE.

WHEN 6.

IF I_DETAIL1-VALUE = 'xx'.

EXIT.

ENDIF.

WHEN 8.

V_POSTING_KEY = I_DETAIL1-VALUE.

ADDREC = 1.

WHEN 10.

IF I_DETAIL1-VALUE = 'XXXXX'.

EXIT.

ELSE.

UNPACK I_DETAIL1-VALUE TO I_BAPI_DETAIL-GL_ACCOUNT .

ADDREC = 1.

ENDIF.

WHEN 11.

IF V_POSTING_KEY = '40'.

REPLACE ALL OCCURRENCES OF ',' IN I_DETAIL1-VALUE WITH ''.

CONDENSE I_DETAIL1-VALUE NO-GAPS.

CONCATENATE I_DETAIL1-VALUE '+' INTO I_DETAIL1-VALUE.

I_BAPI_AMT-AMT_DOCCUR = I_DETAIL1-VALUE.

CLEAR V_POSTING_KEY.

ADDREC = 1.

ENDIF.

WHEN 12.

IF V_POSTING_KEY = '50'.

REPLACE ALL OCCURRENCES OF ',' IN I_DETAIL1-VALUE WITH ''.

CONDENSE I_DETAIL1-VALUE NO-GAPS.

CONCATENATE I_DETAIL1-VALUE '-' INTO I_DETAIL1-VALUE.

I_BAPI_AMT-AMT_DOCCUR = I_DETAIL1-VALUE.

CLEAR V_POSTING_KEY.

ADDREC = 1.

ENDIF.

ADDREC = 1.

WHEN 14.

I_BAPI_DETAIL-ITEM_TEXT = I_DETAIL1-VALUE.

ADDREC = 1.

WHEN 15.

I_BAPI_DETAIL-ALLOC_NMBR = I_DETAIL1-VALUE.

ADDREC = 1.

ENDCASE.

I_BAPI_AMT-CURRENCY = 'USD'.

AT END OF ROW. "TO CONNECT THE 2 STRUCTURES

COUNT = COUNT + 1.

I_BAPI_AMT-ITEMNO_ACC = COUNT.

I_BAPI_DETAIL-ITEMNO_ACC = COUNT.

APPEND I_BAPI_DETAIL.

CLEAR I_BAPI_DETAIL.

APPEND I_BAPI_AMT.

CLEAR I_BAPI_AMT.

ENDAT.

ENDLOOP.

ENDIF.

ENDFORM. " ARRANGE_DETAIL_DATA

*----~~SUB-ROUTINE FOR BAPI FUNCTION CHECK~~--

&----

*& Form CALL_BAPI_FN_CHECK

&----

text

----

--> p1 text
<-- p2 text

----

FORM CALL_BAPI_FN_CHECK .

CALL FUNCTION 'BAPI_ACC_DOCUMENT_CHECK'

EXPORTING

DOCUMENTHEADER = I_BAPI_HEADER

CUSTOMERCPD =
CONTRACTHEADER =

TABLES

ACCOUNTGL = I_BAPI_DETAIL

ACCOUNTRECEIVABLE =
ACCOUNTPAYABLE =
ACCOUNTTAX =

CURRENCYAMOUNT = I_BAPI_AMT

CRITERIA =
VALUEFIELD =
EXTENSION1 =

RETURN = I_BAPI_RETURN

PAYMENTCARD =
CONTRACTITEM =
EXTENSION2 =
REALESTATE =

ENDFORM. " CALL_BAPI_FN_CHECK

*--~~-SUB-ROUTINE FOR BAPI FUNCTION POST~~----

&----

*& Form CALL_BAPI_FN_POST

&----

text

----

--> p1 text
<-- p2 text

----

FORM CALL_BAPI_FN_POST .

CALL FUNCTION 'BAPI_ACC_DOCUMENT_POST'

EXPORTING

DOCUMENTHEADER = I_BAPI_HEADER

CUSTOMERCPD =
CONTRACTHEADER =
IMPORTING
OBJ_TYPE =
OBJ_KEY =
OBJ_SYS =

TABLES

ACCOUNTGL = I_BAPI_DETAIL

ACCOUNTRECEIVABLE =
ACCOUNTPAYABLE =
ACCOUNTTAX =

CURRENCYAMOUNT = I_BAPI_AMT

CRITERIA =
VALUEFIELD =
EXTENSION1 =

RETURN = I_BAPI_RETURN

PAYMENTCARD =
CONTRACTITEM =
EXTENSION2 =
REALESTATE =

ENDFORM. " CALL_BAPI_FN_POST

*----~~SUB-ROUTINE FOR DISPLAY ERROR~~--

&----

*& Form DISPLAY_ERROR_FROM_FN

&----

text

----

-->P_FNAME text

----

FORM DISPLAY_ERROR_FROM_FN USING P_FNAME.

IF FNAME = 'CHECK'.

WRITE : / 'REPORT FROM FUNCTION BAPI_CHECK'.

ELSE.

WRITE : / 'REPORT FROM FUNCTION BAPI_POST'.

ENDIF.

ULINE.

LOOP AT I_BAPI_RETURN.

WRITE : / I_BAPI_RETURN-TYPE,

I_BAPI_RETURN-MESSAGE,

I_BAPI_RETURN-MESSAGE_V1,

I_BAPI_RETURN-MESSAGE_V2.

ENDLOOP.

ULINE.

ENDFORM. " DISPLAY_ERROR_FROM_FN

*--

SUB-ROUTINE FOR DATE CONVERSION--

&----

*& Form CONV_DATE

&----

text

----

--> p1 text
<-- p2 text

----

FORM CONV_DATE .

CALL FUNCTION 'HR_HK_CONV_DATE_TO_INTN_FORMAT'

EXPORTING

DATUM = I_HEADER1-VALUE

DTYPE = 'DATS'

IMPORTING

IDATE = V_DATE

EXCEPTIONS

INVALID_DATE = 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. " CONV_DATE

*--~~-SUB-ROUTINE FOR FISCAL YEAR~~----

&----

*& Form GETFISCALYEAR

&----

text

----

--> p1 text
<-- p2 text

----

FORM GETFISCALYEAR .

CALL FUNCTION 'GM_GET_FISCAL_YEAR'

EXPORTING

I_DATE = SY-DATUM

I_FYV = 'K4'

IMPORTING

E_FY = V_FSYEAR

EXCEPTIONS

FISCAL_YEAR_DOES_NOT_EXIST = 1

NOT_DEFINED_FOR_DATE = 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. " GETFISCALYEAR

****do rewards if usefull

vijay

topic Re: Excel in server to internal table in Application Development and Automation Discussions

Excel in server to internal table

Re: Excel in server to internal table

Re: Excel in server to internal table

Re: Excel in server to internal table