Extracting Header fields and item fields From Excel

[Former Member]

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

[Former Member]

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

[Former Member]

Hi Meenachi,

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

Thanks & Regards,

arun

[Former Member]

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

[yogendra_bhaskar]

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

[Former Member]

hi,

Pls go through this.

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

1. 1.     Upload data from excel file into an internal table using FM ‘GUI_UPLOAD’ as explained in subroutine UPLOAD_FORM_EXCEL.
2. 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. 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. 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. 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. 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. 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.

[Mohamed_Mukhtar]

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

[Former Member]

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

[Former Member]

hi vaamalai,

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

Meena

[Former Member]

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

[Former Member]

you mean to say each sales order contains header?

Regards,

Vadamalai A

[Former Member]

yes

[thangam_perumal]

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