Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Excel in server to internal table

Former Member

‎07-04-2007 12:03 PM

0 Kudos

Hello

I have a XLS file into the server and i have to download it to an internal file to make operations with it, but the problem is that i have to do it in an interface that runs in batch mode so i can't use FM text_convert_xls_to_sap, ALSM_EXCEL_TO_INTERNAL_TABLE, etc

I try to use open dataset and returns me ilegible characters, anybody knows how can i do this?

Thank you

3 REPLIES 3

amit_khare
Active Contributor

‎07-04-2007 12:12 PM

0 Kudos

Refer the links -

Regards,

Amit

Reward all helpful replies.

Former Member

‎07-04-2007 12:18 PM

0 Kudos

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

Former Member

‎07-04-2007 12:22 PM

0 Kudos

Excuse me if i'm wrong but i think i can't use FM

ALSM_EXCEL_TO_INTERNAL_TABLE

TEXT_CONVERT_XLS_TO_SAP

GUI_DOWNLOAD

and similars in batch mode, is this correct¿?

Thanks