topic Re: Importing Excel data into SAP in Application Development and Automation Discussions

Importing Excel data into SAP

Former Member — Mon, 14 Apr 2008 19:23:42 GMT

I want to import my excel data into SAP. Where do I start? The original

data needed to be cleaned up and some new material ID's were created. What

effect will this have in SAP if any?

Thanks for any responses,

Re: Importing Excel data into SAP

Former Member — Mon, 14 Apr 2008 19:38:45 GMT

use FM 'GUI_UPLOAD'

DATA : INput_FILE TYPE STRING.

C_HAS_FLDSEP TYPE C VALUE 'X',

call function 'GUI_UPLOAD'

exporting

filename = input_file

filetype = 'ASC'

has_field_separator = C_HAS_FLDSEP

tables

data_tab = datafile

datafile will have all the fields which you have in your excel ..

what do u mean by this ? "The original

data needed to be cleaned up and some new material ID's were created. What

effect will this have in SAP if any? " ?

Re: Importing Excel data into SAP

Former Member — Mon, 14 Apr 2008 19:45:57 GMT

By your question I think you are trying to upload materials into the system.

You have to check is this a one time load, then you can use a conversion program like LSMW.

If this is going to be needed time to time, you will have to develop an an object for this, first use gui_upload and then Bapi I think it is Bapi_Material_create or BAPI_MATERIAL_SAVEDATA.

or u can check this program -

*&----

*& Report ZBAPI_MATERIAL_SAVEDATA

*&----

**& AUTHOR *

*&----

*& PURPOSE : THIS REPORT USES BAPI MATERIAL SAVE DATA TO UPDATE AND CREATE

*& THE MATERIAL

*&----

REPORT ZBAPI_MATERIAL_SAVEDATA NO STANDARD PAGE HEADING MESSAGE-ID (ZHNC).

TYPES:BEGIN OF TY_MAT,

MATERIAL(4),

IND_SECTOR(1),

MATL_TYPE(4),

BASIC_VIEW(1),*

SALES_VIEW(1),*

PURCHASE_VIEW(1),*

STORAGE_VIEW(1),

MATL_GROUP(9),

BASE_UOM(3),

BASE_UOM_ISO(3),

MATL_GROUP1(1),
BASE_UOM1(1),
BASE_UOM_ISO1(1),

PLANT(4),

DEL_FLAG(1),

PUR_GROUP(3),

BASE_QTY(13),

PLANT2(4),
DEL_FLAG5(1),
PUR_GROUP1(1),
BASE_QTY1(1),
PLANT3(4),

STGE_LOC(4),

MRP_IND(1),

PLANT4(4),
STGE_LOC1(4),
MRP_IND1(1),

SALES_ORG(4),

DISTR_CHAN(2),

DEL_FLAG1(1),

MIN_ORDER(13),

SALES_ORG1(4),
DISTR_CHAN1(2),
DEL_FLAG2(1),
MIN_ORDER1(1),

LANGU(2),

MATL_DESC(40),

END OF TY_MAT.

DATA: IT_DATA TYPE TABLE OF TY_MAT,

WA_DATA LIKE LINE OF IT_DATA.

*decalraing flag

data: v_flag value ''.

*DECLARING WORK AREAs TO BE PASSED TO THE FUNCTION MODULE.

DATA: BAPI_HEAD LIKE BAPIMATHEAD,

BAPI_CLIENTDATA LIKE BAPI_MARA,

BAPI_CLIENTDATAX LIKE BAPI_MARAX,

BAPI_PLANTDATA LIKE BAPI_MARC,

BAPI_PLANTDATAX LIKE BAPI_MARCX,

BAPI_STORAGELOCATIONDATA LIKE BAPI_MARD,

BAPI_STORAGELOCATIONDATAX LIKE BAPI_MARDX,

BAPI_SALESDATA LIKE BAPI_MVKE,

BAPI_SALESDATAX LIKE BAPI_MVKEX,

BAPI_MAKT LIKE BAPI_MAKT,

BAPI_RETURN LIKE BAPIRET2.

*INTERNAL TABLE TO HOLD THE MATERIAL DESCRIPTION

DATA: BEGIN OF IT_MAKT OCCURS 0.

INCLUDE STRUCTURE BAPI_MAKT.

DATA END OF IT_MAKT.

DATA:BEGIN OF IT_RET OCCURS 0.

INCLUDE STRUCTURE BAPIRET2.

DATA END OF IT_RET.

*INTERNAL TABLE TO HOLD HEADER DATA

DATA: IT_EXCEL TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

*SELECTION-SCREEN ELEMENTS

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

PARAMETER: FNAME TYPE RLGRAP-FILENAME OBLIGATORY DEFAULT 'C:\Documents and Settings\Administrator\Desktop\MATMAS.XLS' .

PARAMETERS: P_BEGCOL TYPE I DEFAULT 1 NO-DISPLAY,

P_BEGROW TYPE I DEFAULT 1 NO-DISPLAY,

P_ENDCOL TYPE I DEFAULT 100 NO-DISPLAY,

P_ENDROW TYPE I DEFAULT 32000 NO-DISPLAY.

SELECTION-SCREEN END OF BLOCK B1.

*DECLARATION OF EXCELAL TABLE

AT SELECTION-SCREEN ON VALUE-REQUEST FOR FNAME.

PERFORM F_GET_FILE USING FNAME.

START-OF-SELECTION.

PERFORM F_XLS_ITAB USING FNAME

CHANGING IT_EXCEL.

PERFORM F_MOVE_DATA.

perform F_GET_DATA.

*&----

*& Form F_GET_FILE

*&----

text

*----

-->P_FNAME text
<--P_SY_SUBRC text

*----

FORM F_GET_FILE USING P_FNAME LIKE FNAME.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

PROGRAM_NAME = SYST-REPID

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = ' '
STATIC = ' '
MASK = ' '

CHANGING

FILE_NAME = P_FNAME

EXCEPTIONS
MASK_TOO_LONG = 1
OTHERS = 2

IF SY-SUBRC 0.

MESSAGE E006(ZHNC).

ENDIF.

ENDFORM. " F_GET_FILE

*&----

*& Form F_XLS_ITAB

*&----

text

*----

-->P_FNAME text
<--P_IT_EXCEL text

*----

FORM F_XLS_ITAB USING P_FNAME

CHANGING P_IT_EXCEL.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = FNAME

I_BEGIN_COL = P_BEGCOL

I_BEGIN_ROW = P_BEGROW

I_END_COL = P_ENDCOL

I_END_ROW = P_ENDROW

TABLES

INTERN = IT_EXCEL

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

*&----

*& Form F_MOVE_DATA

*&----

text

*----

--> p1 text
<-- p2 text

*----

FORM F_MOVE_DATA .

DATA : LV_INDEX TYPE I.

FIELD-SYMBOLS <FS>.

*--- Sorting the internal table

SORT IT_EXCEL BY ROW COL.

CLEAR IT_EXCEL.

LOOP AT IT_EXCEL.

MOVE IT_EXCEL-COL TO LV_INDEX.

*--- Assigning the each record to an internal table row

ASSIGN COMPONENT LV_INDEX OF STRUCTURE WA_DATA TO <FS>.

*--- Asigning the field value to a field symbol

MOVE IT_EXCEL-VALUE TO <FS>.

AT END OF ROW.

APPEND WA_DATA TO IT_DATA.

CLEAR WA_DATA.

ENDAT.

ENDLOOP.

***

ENDFORM. " F_MOVE_DATA

*&----

*& Form F_GET_DATA

*&----

text

*----

--> p1 text
<-- p2 text

*----

FORM F_GET_DATA .

LOOP AT IT_DATA INTO WA_DATA.

MOVE-CORRESPONDING WA_DATA TO BAPI_HEAD.

BAPI_HEAD-BASIC_VIEW ='X'.

BAPI_HEAD-SALES_VIEW ='X'.

BAPI_HEAD-PURCHASE_VIEW ='X'.

BAPI_HEAD-STORAGE_VIEW ='X'.

MOVE-CORRESPONDING WA_DATA TO BAPI_CLIENTDATA.

BAPI_CLIENTDATAX-MATL_GROUP = 'X'.

BAPI_CLIENTDATAX-BASE_UOM = 'X'.

BAPI_CLIENTDATAX-BASE_UOM_ISO = 'X'.

MOVE-CORRESPONDING WA_DATA TO BAPI_PLANTDATA.

BAPI_PLANTDATAX-PLANT = BAPI_PLANTDATA-PLANT.

BAPI_PLANTDATAX-DEL_FLAG = 'X'.

BAPI_PLANTDATAX-PUR_GROUP = 'X'.

BAPI_PLANTDATAX-BASE_QTY = 'X'.

MOVE-CORRESPONDING WA_DATA TO BAPI_STORAGELOCATIONDATA.

BAPI_STORAGELOCATIONDATA-PLANT = BAPI_PLANTDATA-PLANT.

BAPI_STORAGELOCATIONDATAX-PLANT = BAPI_STORAGELOCATIONDATA-PLANT.

BAPI_STORAGELOCATIONDATAX-STGE_LOC = BAPI_STORAGELOCATIONDATA-STGE_LOC.

BAPI_STORAGELOCATIONDATAX-MRP_IND = 'X'.

MOVE-CORRESPONDING WA_DATA TO BAPI_SALESDATA.

BAPI_SALESDATAX-SALES_ORG = BAPI_SALESDATA-SALES_ORG.

BAPI_SALESDATAX-DISTR_CHAN = BAPI_SALESDATA-DISTR_CHAN.

BAPI_SALESDATAX-DEL_FLAG = BAPI_SALESDATA-DEL_FLAG.

BAPI_SALESDATAX-MIN_ORDER = 'X'.

REFRESH IT_MAKT.

IT_MAKT-LANGU = WA_DATA-LANGU.

IT_MAKT-MATL_DESC = WA_DATA-MATL_DESC.

APPEND IT_MAKT.

CLEAR IT_RET.

REFRESH IT_RET.

PERFORM F_CALL_BAPI.

READ TABLE IT_RET WITH KEY TYPE = 'S'.

IF SY-SUBRC EQ 0.

PERFORM F_BAPI_COMMIT.

WRITE:/ 'MATERIAL CREATED OR UPDATED SUCESSFULLY WITH MATERIAL NO',WA_DATA-MATERIAL.

ELSE.

MESSAGE E000(ZHNC) WITH 'ERROR IN CREATING THE MATERIAL'.

*WRITE: / 'ERROR IN CREATIN MATERIAL',IT_RET-MESSAGE.

*PERFORM F_DOWNLOAD.

ENDIF.

*ENDIF.

ENDLOOP.

ENDFORM. " F_GET_DATA

*&----

*& Form F_CALL_BAPI

*&----

text

*----

--> p1 text
<-- p2 text

*----

FORM F_CALL_BAPI .

CALL FUNCTION 'BAPI_MATERIAL_SAVEDATA'

EXPORTING

HEADDATA = BAPI_HEAD

CLIENTDATA = BAPI_CLIENTDATA

CLIENTDATAX = BAPI_CLIENTDATAX

PLANTDATA = BAPI_PLANTDATA

PLANTDATAX = BAPI_PLANTDATAX

STORAGELOCATIONDATA = BAPI_STORAGELOCATIONDATA

STORAGELOCATIONDATAX = BAPI_STORAGELOCATIONDATAX

SALESDATA = BAPI_SALESDATA

SALESDATAX = BAPI_SALESDATAX

IMPORTING

RETURN = IT_RET

TABLES

MATERIALDESCRIPTION = IT_MAKT

UNITSOFMEASURE =
UNITSOFMEASUREX =
INTERNATIONALARTNOS =
MATERIALLONGTEXT =
TAXCLASSIFICATIONS =
RETURNMESSAGES =
PRTDATA =
PRTDATAX =
EXTENSIONIN =
EXTENSIONINX =

APPEND IT_RET.

ENDFORM. " F_CALL_BAPI

*&----

*& Form F_BAPI_COMMIT

*&----

text

*----

--> p1 text
<-- p2 text

*----

FORM F_BAPI_COMMIT .

CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'

EXPORTING
WAIT =
IMPORTING
RETURN =

ENDFORM. " F_BAPI_COMMIT

Re: Importing Excel data into SAP

Former Member — Tue, 15 Apr 2008 16:24:57 GMT

Use the help facility within SAP. CATT is a transaction (can not

remember ..dont use it very often...I use the drill down to find it).

Basically CATT acts like it is doing the transaction (like a CAM shot)

and builds what looks like an excel spreadsheet when complete that

includes all the elements required to complete the transaction like the

MM01 or MM02. Then all you have to do is align the spreadsheet you have

with the columns CATT provides by appending and run the CATT. Data will

be loaded.

Re: Importing Excel data into SAP

former_member125661 — Tue, 15 Apr 2008 16:57:27 GMT

First bring the excel file into internal table using the code below. The internal table needs to have same structure as excel file. Once they are in the internal table, u can loop at it and do whatever updates u want to do using BAPI or BDC or ALE/INBOUND.

REPORT  zupload_excel_to_itab.

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE  rlgrap-filename.

TYPES: BEGIN OF t_datatab,
      col1(30)    TYPE c,
      col2(30)    TYPE c,
      col3(30)    TYPE c,
      END OF t_datatab.
DATA: it_datatab type standard table of t_datatab,
      wa_datatab type t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

* At selection screen
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      field_name = 'P_FILE'
    IMPORTING
      file_name  = p_file.


***********************************************************************
*START-OF-SELECTION.
START-OF-SELECTION.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR        =
      i_line_header            =  'X'
      i_tab_raw_data           =  it_raw       " WORK TABLE
      i_filename               =  p_file
    TABLES
      i_tab_converted_data     = it_datatab[]    "ACTUAL DATA
   EXCEPTIONS
      conversion_failed        = 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.


***********************************************************************
* END-OF-SELECTION.
END-OF-SELECTION.

Re: Importing Excel data into SAP

Former Member — Tue, 15 Apr 2008 23:32:10 GMT

Hi Neelam,

SAP upload: if you have hundreds of materials to create or to modify, the solutions available are:

1.Build a program in ABAP.

2.Use SAP tool called LSMW (Legacy Systems Migration Workbench

3.Use one internal test tool called CATT (Computer Aided Test Tool).

To download from SAP to Excel:

1.Many SAP reports have direct download function to EXCEL.

SAP Query and SAP QuickViewer are excellent tools for Downloading (BvdR AO).

2.Transaction SE16N allows you to access any sap table and download result to Excel

SAP RFC functions can be called directly from Excel / VBA.

3. Use transaction BAPI in SAP to find your business object and study the function.

4. If you know the SAP table the RFC RFC_READ_TABLE offers a SQL query of the table

Trouble is .. you can not use joins here. you have to put the join in as a query in the SAP database and use the query in RFC_READ_TABLE

To upload data from Excel into SAP:

Some of the Business functions (BAPI's / RFC unctions) offer data upload

The general tool in SAP for data upload is the Batch input / call transactions

In the batch input file you specify screen by screen and field by field the input to SAP. SAP has a recorder to create the sample file. Just run trans SHDB and within this trans you run a case of the changes you want to do. On exit of the trans you will have a batch input file that you can download and from excel manipulate the file before you call the RFC function RFC_CALL_TRANSACTION.