topic Re: Extracting Header fields and item fields From Excel in Application Development and Automation Discussions

Extracting Header fields and item fields From Excel

Former Member — Fri, 23 May 2014 09:00:55 GMT

Hi Guru's

I am using BAPi : BAPI_SALESORDER_CREATEFORMAT2 for Data migration. My input file is Excel.

in single file they gave both header and item.

I need to split it header in to header internal table and item in to item internal table.

I am using function module to convert excel to internal table.

ALSM_EXCEL_TO_INTERNAL_TABLE

Its working fine.

How to split that header and item.

Thanks & Regards,

Meenachi

Re: Extracting Header fields and item fields From Excel

Former Member — Fri, 23 May 2014 09:11:37 GMT

Hi Meenachi,

Can you share how data are differentiated as Header and Item in excel ?

Thanks & Regards,

arun

Re: Extracting Header fields and item fields From Excel

Former Member — Fri, 23 May 2014 09:15:34 GMT

hi,

Pls go through this.

Various steps involved in posting a document from a file are:

1. Upload data from excel file into an internal table using FM ‘GUI_UPLOAD’ as explained in subroutine UPLOAD_FORM_EXCEL.
2. After we upload excel data into an internal table we need split the data into field patterns into header data and line item data as explained in subroutine ‘SPLIT_INTO_RECORDS’. This splitting is done according to my file format. In header data in the file in line no.2, posting date and header company code. That is why I have used case when 2. And all the other information is present at the lines as described by CASE statement. Line item details start from line number 14, that’s why LOOP AT t_upload INTO w_upload FROM 14is used. User can split the data according to their file format.
3. After the splitting, loop at the line item table and validate the GL account numbers. If the GL accounts are valid, Populate the line item data into BAPI GL and currency tables otherwise append that GL account number into error message table. This is explained in subroutines ‘VALIDATE_GL’ and ‘POPULATE_BAPI’.
4. In case of intercompany postings, in which line item and Header Company codes are different. Find out line item company code currency key and compare if the line item currency key and header currency key given is same or different. If these are different then we need to populate ‘EXCHANGE_RATE’ field of currency table if it is given in the file. Otherwise BAPI itself will fetch the exchange rate between 2 currencies and do the conversion.
5. Above step is required because a document is posted in 3 currencies document currency, company code currency and group currency. In case of the intra company code postings all the currencies are same so the amounts are same In all3 currencies. But in case of inter company code postings the currencies are different so the amounts are also different. Due to this we need to give and exchange rate base on which the BAPI will convert the amounts.
6. Next step in document posting is to check the data using BAPI ‘BAPI_ACC_DOCUMEN_CHECK’. If this BAPI do not return any error message, go to next step otherwise display error message given by BAPI.
7. Post the document using BAPI ‘BAPI_ACC_DOCUMEN_POST’. Check for sy-subrc, if initial; commit the work using ‘BAPI_TRANSACTION_COMMIT’. This step is explained in subroutine ‘POST_DOCUMENT’. While providing header data in W_HEADER work area do not provide OBJ_KEY, OBJ_TYPE and OBJ_SYS, these are automatically populated by BAPI.

Re: Extracting Header fields and item fields From Excel

Former Member — Fri, 23 May 2014 09:21:17 GMT

Thanks For reply arun and always learner

for ex excel sheet

Hdr Hdr Hdr Hdr plant mat quantoty

001 IN 001 02 1000 100 1

001 IN 001 02 1002 101 2

001 IN 001 02 1003 102 3

002 IN 001 02 1000 100 1

002 IN 001 02 1002 101 3

002 IN 001 02 1006 102 5

No link between hdr and itm

Re: Extracting Header fields and item fields From Excel

Mohamed_Mukhtar — Fri, 23 May 2014 09:23:17 GMT

Hello Meenachi,

There will be some indicator (eg H for Header , L for Line item) in excel.

1. Use GUI_upload fm to upload the file

2. loop internal table and based on indicator move the rows to either header or item table.

Thanks

Re: Extracting Header fields and item fields From Excel

Former Member — Sat, 24 May 2014 05:19:25 GMT

Hello Meenachi,

No challenge here just convert your excel data to internal table and read the first line this will be header rest of the entries are items.

LOOP AT LT_TAB ASSIGNING <LS>.

TRANSLATE <LS>-VALUE TO UPPER CASE.

CASE <LS>-COL.

WHEN '0001'.

LS_SERCOMBO-ZPOSTCODE = <LS>-VALUE.

WHEN '0002'.

LS_SERCOMBO-ZCOMDGRP = <LS>-VALUE.

WHEN '0003'.

LS_SERCOMBO-ZVESSELGRP = <LS>-VALUE.

WHEN '0004'.

LS_SERCOMBO-ZSERCOMBO = <LS>-VALUE.

ENDCASE.

AT END OF ROW.

APPEND LS_SERCOMBO TO LT_SERCOMBO.

CLEAR LS_SERCOMBO.

ENDAT.

ENDLOOP.

Now LT_SERCOMBO contains header and items

read with index 1-will be header

rest of the records are item entries.

What is your challenge tell me.

Regards,

Vadamalai A

Re: Extracting Header fields and item fields From Excel

yogendra_bhaskar — Sat, 24 May 2014 05:42:57 GMT

Hi Meenachi ,

Going through the excel , I think first column is the key in header

so you can transport the data into internal table ( itab ) and then follow the code as below.

LOOP AT itab INTO wa.

wa_temp = wa.

AT NEW Hdr.

MOVE-CORRESPONDING wa_temp TO wa_h.

APPEND wa_h TO itab_h.

CLEAR : wa_h , wa_temp.

ENDAT.

MOVE-CORRESPONDING wa TO wa_i.

APPEND wa_i TO itab_i.

CLEAR wa_i.

CLEAR : wa , wa_temp.

ENDLOOP.

Hope this will help you

Thanx and Regards ,

Yogendra Bhaskar

Re: Extracting Header fields and item fields From Excel

Former Member — Sat, 24 May 2014 06:50:21 GMT

hi vaamalai,

you are correct .. but for second sales order how yo will read/

Meena

Re: Extracting Header fields and item fields From Excel

Former Member — Sat, 24 May 2014 07:05:38 GMT

you mean to say each sales order contains header?

Regards,

Vadamalai A

Re: Extracting Header fields and item fields From Excel

Former Member — Sat, 24 May 2014 07:22:45 GMT

Hello Meenachi,

use at new statement for change of sales order from your excel extracted itab.

for eg:


at new vbeln.
-
-
-
-
end at.

Regards

Ritesh

Re: Extracting Header fields and item fields From Excel

Former Member — Sat, 24 May 2014 11:06:28 GMT

yes

Re: Extracting Header fields and item fields From Excel

thangam_perumal — Sat, 24 May 2014 11:39:38 GMT

Hi Meenachi,

You can use Two sheets in Excel . Consider sheet 1 contains header data and sheet two contains item data. commonly declare one Column for key field to link two sheets.

Regards

Thangam.P