topic Re: problem in extracting the data from bapi FM to EXCEL in Application Development and Automation Discussions

problem in extracting the data from bapi FM to EXCEL

Former Member — Thu, 16 Nov 2006 12:43:23 GMT

hi,

i am using the following code to get the details of po ,but data is comming properly in to all itabs,but i want all the data into one itab,from there i want to download into excel sheet,can any body have the idea.

REPORT abc.

PARAMETERS : p_ebeln TYPE ebeln.

DATA : it_items type table of bapiekpo with header line.

data: po_items_shedule type table of BAPIEKET with header line.

data: po_item_text type table of BAPIEKPOTX with header line.

data: PO_ITEM_ACCOUNT_ASSIGNMENT type table of BAPIEKKN

with header line.

data: PO_HEADER_TEXTS type table of BAPIEKKOTX with header line.

data: PO_HEADER type BAPIEKKOL.

data: return1 type table of BAPIRETURN with header line.

CALL FUNCTION 'BAPI_PO_GETDETAIL'

EXPORTING

purchaseorder = p_ebeln

ITEMS = 'X'

ACCOUNT_ASSIGNMENT = 'x'

SCHEDULES = ' '

HISTORY = 'X'

ITEM_TEXTS = 'X'

HEADER_TEXTS = 'X'

SERVICES = ' '
CONFIRMATIONS = ' '
SERVICE_TEXTS = ' '
EXTENSIONS = ' '
IMPORTING
PO_HEADER =
PO_ADDRESS =

TABLES

PO_HEADER_TEXTS = PO_HEADER_TEXTS

po_items = it_items

PO_ITEM_ACCOUNT_ASSIGNMENT = PO_ITEM_ACCOUNT_ASSIGNMENT

PO_ITEM_SCHEDULES = po_items_shedule

PO_ITEM_CONFIRMATIONS =

PO_ITEM_TEXTS = po_item_text

PO_ITEM_HISTORY =
PO_ITEM_HISTORY_TOTALS =
PO_ITEM_LIMITS =
PO_ITEM_CONTRACT_LIMITS =
PO_ITEM_SERVICES =
PO_ITEM_SRV_ACCASS_VALUES =

RETURN = return1.

PO_SERVICES_TEXTS =
EXTENSIONOUT =

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

BIN_FILESIZE =

filename = 'C:/poextract.xls'

FILETYPE = 'ASC'
APPEND = ' '
WRITE_FIELD_SEPARATOR = ' '
HEADER = '00'
TRUNC_TRAILING_BLANKS = ' '
WRITE_LF = 'X'
COL_SELECT = ' '
COL_SELECT_MASK = ' '
DAT_MODE = ' '
CONFIRM_OVERWRITE = ' '
NO_AUTH_CHECK = ' '
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
WRITE_BOM = ' '
TRUNC_TRAILING_BLANKS_EOL = 'X'
WK1_N_FORMAT = ' '
WK1_N_SIZE = ' '
WK1_T_FORMAT = ' '
WK1_T_SIZE = ' '
IMPORTING
FILELENGTH =

tables

data_tab = PO_HEADER_TEXTS

FIELDNAMES =
EXCEPTIONS
FILE_WRITE_ERROR = 1
NO_BATCH = 2
GUI_REFUSE_FILETRANSFER = 3
INVALID_TYPE = 4
NO_AUTHORITY = 5
UNKNOWN_ERROR = 6
HEADER_NOT_ALLOWED = 7
SEPARATOR_NOT_ALLOWED = 8
FILESIZE_NOT_ALLOWED = 9
HEADER_TOO_LONG = 10
DP_ERROR_CREATE = 11
DP_ERROR_SEND = 12
DP_ERROR_WRITE = 13
UNKNOWN_DP_ERROR = 14
ACCESS_DENIED = 15
DP_OUT_OF_MEMORY = 16
DISK_FULL = 17
DP_TIMEOUT = 18
FILE_NOT_FOUND = 19
DATAPROVIDER_EXCEPTION = 20
CONTROL_FLUSH_ERROR = 21
OTHERS = 22

IF sy-subrc <> 0.

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

ENDIF.

Re: problem in extracting the data from bapi FM to EXCEL

Former Member — Thu, 16 Nov 2006 16:26:41 GMT

Ram,

try to use 'EXCEL_OLE_STANDARD_DAT' FM to download into excel file

-Anu

Re: problem in extracting the data from bapi FM to EXCEL

former_member69765 — Fri, 17 Nov 2006 03:15:16 GMT

hi Rammohan,

your problem is that you want all the data in one internal table and not in diffrent internal tables so that you can download just one Excel sheet right...

I dont think there is any FM that will pull all the data into one Internal table.

what you can do is that write a small subroutine that will merge the header data and the item data into one table.

See the code below. this is not the exact code... just a pseudo code to give you Idea..

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

data : wa_po_header, " this is a structure that contains the PO Header data.

wa_po_item, " this is a structure that contains the Item Data.

it_po_header, " Internal table with header data for all the PO headers

it_po_item, " Internal table with Item data for all the POs

Declare a structure that has both the components ***

types : begin of merge_po,

header type wa_po_header,

item type wa_po_item,

end of merge_po.

data : it_po_merge type standard table of merge_po initial size 0,

wa_po_merge type merge_po.

loop at it_po_header into wa_po_header.

wa_po_merge-header = wa_po_header.

loop at it_po_item into wa_po_item where po_number = wa_po_header-po_number.

wa_po_merge-item = wa_po_item.

append wa_po_merge to it_po_merge.

endloop.

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

So you will have an internal table that looks like this --

Po No. Header_Related_Data Item_no Item_related_data

1 xxxxxxxxxxxxx 1 yyyyyyyyyyyyyyy

1 xxxxxxxxxxxxx 2 zzzzzzzzzzzzzzzz

2 aaaaaaaaaaaaa 1 mmmmmmmmmm

2 aaaaaaaaaaaaa 2 nnnnnnnnnnnnnnnnn

You can download this into Excel.

Important thing that you need to take care is while uploading this data from the Excel to SAP by using BAPI, you will have to split that one internal table into 2 tables.

I hope my pseudo code was not very much Pseudo and you got the Idea of what needs to be done.

To sum it up... you will have to merge 2 internal table into 1 where the link b/w 2 internal table is the PO No.

Please mark all the helpful and correct answers.

Re: problem in extracting the data from bapi FM to EXCEL

Former Member — Fri, 17 Nov 2006 03:27:16 GMT

HI Ram Mohan

I guess your requirement is to have all details downloaded to one single file.

You can try using APPEND parameter of FM: GUI_DOWNLOAD. If we pass 'X' to this parameter, the next internal table that you want to download will be appended to the same file.

Hope this helps.

Kind Regards

Eswar

Re: problem in extracting the data from bapi FM to EXCEL

Former Member — Fri, 17 Nov 2006 03:37:28 GMT

Below code can give you some idea: Use same according to your requirement.

PARAMETERS : P_EBELN TYPE EBELN.

DATA : IT_ITEMS TYPE TABLE OF BAPIEKPO WITH HEADER LINE.
DATA: PO_ITEMS_SHEDULE TYPE TABLE OF BAPIEKET WITH HEADER LINE.
DATA: PO_ITEM_TEXT TYPE TABLE OF BAPIEKPOTX WITH HEADER LINE.
DATA: PO_ITEM_ACCOUNT_ASSIGNMENT TYPE TABLE OF BAPIEKKN
WITH HEADER LINE.
DATA: PO_HEADER_TEXTS TYPE TABLE OF BAPIEKKOTX WITH HEADER LINE.
DATA: PO_HEADER TYPE BAPIEKKOL.
DATA: RETURN1 TYPE TABLE OF BAPIRETURN WITH HEADER LINE.

CALL FUNCTION 'BAPI_PO_GETDETAIL'
  EXPORTING
    PURCHASEORDER              = P_EBELN
    ITEMS                      = 'X'
    ACCOUNT_ASSIGNMENT         = 'x'
    SCHEDULES                  = ' '
    HISTORY                    = 'X'
    ITEM_TEXTS                 = 'X'
    HEADER_TEXTS               = 'X'
  TABLES
    PO_HEADER_TEXTS            = PO_HEADER_TEXTS
    PO_ITEMS                   = IT_ITEMS
    PO_ITEM_ACCOUNT_ASSIGNMENT = PO_ITEM_ACCOUNT_ASSIGNMENT
    PO_ITEM_SCHEDULES          = PO_ITEMS_SHEDULE
    PO_ITEM_TEXTS              = PO_ITEM_TEXT
    RETURN                     = RETURN1.

PERFORM DOWNLOAD_DATA: TABLES PO_HEADER_TEXTS USING SPACE,
                       TABLES IT_ITEMS USING 'X',
                       TABLES PO_ITEM_ACCOUNT_ASSIGNMENT USING 'X',
                       TABLES PO_ITEMS_SHEDULE USING 'X',
                       TABLES PO_ITEM_TEXT USING 'X'.

*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_DATA
*&---------------------------------------------------------------------*
FORM DOWNLOAD_DATA  TABLES   IT_TAB
                    USING    P_APP.

CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
    FILENAME = 'C:/poextract.xls'
    APPEND =  P_APP
  TABLES
    DATA_TAB = IT_TAB
* FIELDNAMES =
  EXCEPTIONS
    FILE_WRITE_ERROR        = 1
    NO_BATCH                = 2
    GUI_REFUSE_FILETRANSFER = 3
    INVALID_TYPE            = 4
    NO_AUTHORITY            = 5
    UNKNOWN_ERROR           = 6
    HEADER_NOT_ALLOWED      = 7
    SEPARATOR_NOT_ALLOWED   = 8
    FILESIZE_NOT_ALLOWED    = 9
    HEADER_TOO_LONG         = 10
    DP_ERROR_CREATE         = 11
    DP_ERROR_SEND           = 12
    DP_ERROR_WRITE          = 13
    UNKNOWN_DP_ERROR        = 14
    ACCESS_DENIED           = 15
    DP_OUT_OF_MEMORY        = 16
    DISK_FULL               = 17
    DP_TIMEOUT              = 18
    FILE_NOT_FOUND          = 19
    DATAPROVIDER_EXCEPTION  = 20
    CONTROL_FLUSH_ERROR     = 21
    OTHERS                  = 22.
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.                    " DOWNLOAD_DATA

Kind Regards

Eswar

Re: problem in extracting the data from bapi FM to EXCEL

Former Member — Fri, 17 Nov 2006 05:41:26 GMT

hi eswar,

What ever the code you are given is working fine,bur after downloading the data in to excel sheet ,in the status bar it is giving message '0 bytes transfered',but there is data in excel sheet,instead of parameters can we use the select-options,because i want to extract more than one po details,and one more question was in the excel sheet data is comming continusly,it is not comming colum wise ,if u have any idea ,kindly let me know,thanks for u r help full answer.

Re: problem in extracting the data from bapi FM to EXCEL

Former Member — Fri, 17 Nov 2006 06:28:40 GMT

Hi Ram

Have made few modifications to your earlier code, please check the same:

TABLES: EKKO.
*PARAMETERS : P_EBELN TYPE EBELN.
SELECT-OPTIONS: S_EBELN FOR EKKO-EBELN OBLIGATORY.

TYPES: BEGIN OF T_PO,
         EBELN TYPE EBELN,
       END OF T_PO.
DATA: IT_PO TYPE STANDARD TABLE OF T_PO,
      WA_PO TYPE T_PO.

DATA : IT_ITEMS TYPE TABLE OF BAPIEKPO WITH HEADER LINE.
DATA: PO_ITEMS_SHEDULE TYPE TABLE OF BAPIEKET WITH HEADER LINE.
DATA: PO_ITEM_TEXT TYPE TABLE OF BAPIEKPOTX WITH HEADER LINE.
DATA: PO_ITEM_ACCOUNT_ASSIGNMENT TYPE TABLE OF BAPIEKKN
WITH HEADER LINE.
DATA: PO_HEADER_TEXTS TYPE TABLE OF BAPIEKKOTX WITH HEADER LINE.
DATA: PO_HEADER TYPE BAPIEKKOL.
DATA: RETURN1 TYPE TABLE OF BAPIRETURN WITH HEADER LINE.

SELECT EBELN INTO TABLE IT_PO FROM EKKO WHERE EBELN IN S_EBELN.

PERFORM DOWNLOAD_DATA: TABLES IT_ITEMS USING SPACE.
LOOP AT IT_PO INTO WA_PO.
CLEAR: PO_HEADER_TEXTS[], IT_ITEMS[], PO_ITEM_ACCOUNT_ASSIGNMENT[],
       PO_ITEMS_SHEDULE[], PO_ITEM_TEXT[], RETURN1[].
CALL FUNCTION 'BAPI_PO_GETDETAIL'
  EXPORTING
    PURCHASEORDER              = WA_PO-EBELN
    ITEMS                      = 'X'
    ACCOUNT_ASSIGNMENT         = 'x'
    SCHEDULES                  = ' '
    HISTORY                    = 'X'
    ITEM_TEXTS                 = 'X'
    HEADER_TEXTS               = 'X'
  TABLES
    PO_HEADER_TEXTS            = PO_HEADER_TEXTS
    PO_ITEMS                   = IT_ITEMS
    PO_ITEM_ACCOUNT_ASSIGNMENT = PO_ITEM_ACCOUNT_ASSIGNMENT
    PO_ITEM_SCHEDULES          = PO_ITEMS_SHEDULE
    PO_ITEM_TEXTS              = PO_ITEM_TEXT
    RETURN                     = RETURN1.

PERFORM DOWNLOAD_DATA: TABLES PO_HEADER_TEXTS USING 'X',
                       TABLES IT_ITEMS USING 'X',
                       TABLES PO_ITEM_ACCOUNT_ASSIGNMENT USING 'X',
                       TABLES PO_ITEMS_SHEDULE USING 'X',
                       TABLES PO_ITEM_TEXT USING 'X'.
ENDLOOP.

*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_DATA
*&---------------------------------------------------------------------*
FORM DOWNLOAD_DATA  TABLES   IT_TAB
                    USING    P_APP.

CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
    FILENAME = 'C:/poextract.xls'
    APPEND =  P_APP
    WRITE_FIELD_SEPARATOR = 'X'
  TABLES
    DATA_TAB = IT_TAB
* FIELDNAMES =
  EXCEPTIONS
    FILE_WRITE_ERROR        = 1
    NO_BATCH                = 2
    GUI_REFUSE_FILETRANSFER = 3
    INVALID_TYPE            = 4
    NO_AUTHORITY            = 5
    UNKNOWN_ERROR           = 6
    HEADER_NOT_ALLOWED      = 7
    SEPARATOR_NOT_ALLOWED   = 8
    FILESIZE_NOT_ALLOWED    = 9
    HEADER_TOO_LONG         = 10
    DP_ERROR_CREATE         = 11
    DP_ERROR_SEND           = 12
    DP_ERROR_WRITE          = 13
    UNKNOWN_DP_ERROR        = 14
    ACCESS_DENIED           = 15
    DP_OUT_OF_MEMORY        = 16
    DISK_FULL               = 17
    DP_TIMEOUT              = 18
    FILE_NOT_FOUND          = 19
    DATAPROVIDER_EXCEPTION  = 20
    CONTROL_FLUSH_ERROR     = 21
    OTHERS                  = 22.
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.                    " DOWNLOAD_DATA

Kind Regards

Eswar

Re: problem in extracting the data from bapi FM to EXCEL

Former Member — Fri, 17 Nov 2006 09:42:55 GMT

Hi,

Thanks eswar a lot,it helped me all the way.

One more questions i have ,

Is there any chance to get the headers in EXCEL sheet,and can i load the same po's By using BAPI_PO_CREATE ,if u have any idea you can help.

Thanks,

Rammohan

Re: problem in extracting the data from bapi FM to EXCEL

Former Member — Fri, 17 Nov 2006 10:07:24 GMT

Hi Ram

Please use Table: FIELDNAMES of FM: GUI_DOWNLOAD to populate the Headers while downloading to Excel Sheet.

For the second point, i couldnt understand the necessity for loading the same PO again???

I do agree that we can use BAPI: BAPI_PO_CREATE and BAPI_PO_CREATE1 can be used to load PO's in SAP.

Kind Regards

Eswar

Re: problem in extracting the data from bapi FM to EXCEL

Former Member — Fri, 17 Nov 2006 10:29:37 GMT

hi eswar,

we have process like this,

our data is alredy in production,that data we are extracting and then we are loading into the other server(GLOBAL) with some changes in data,so we need to load the pos inthe new server.

i not got ur point to get the headers in to excel,in the GUI_Download there is no field names table.

Thanks,'

Rammohan.

Re: problem in extracting the data from bapi FM to EXCEL

Former Member — Fri, 17 Nov 2006 12:01:11 GMT

hi eswar,

how to get the field headers automatically in the excel sheet.

can u help me in this regard,

Thanks,

Rammohan.