Upload excel file

[Former Member]

hi,

How to upload excel file from Application server into SAP?

[Former Member]

Hi Sudheer,

Ur code is so helpful.

But have one doubt, here we are opening file in text mode...bt my file is in excel format...then also the following code works?

I want to add some more logic while doing changes into the dtable:

"If the same record is existing in dbase table, the i have to modify the record &

If not i have to insert the record.."

Is the above code is included in loop?/ before close dataset?

Regards

siri

[Former Member]

Hi siri,

You should use 'UPLOAD' function module,

Example program.

report ZUDAY_BDC_TCL1 no standard page heading line-size 255.

include bdcrecx1.

TABLES EBAN.

DATA: BEGIN OF ITAB OCCURS 0,

NUMBER(3) TYPE C,

MATNR LIKE EBAN-MATNR,

TXZ01 LIKE EBAN-TXZ01,

MENGE(13) TYPE C,

MEINS LIKE EBAN-MEINS,

EEIND TYPE RM06B-EEIND,

EKGRP LIKE EBAN-EKGRP,

MATKL LIKE EBAN-MATKL,

WERKS(4) TYPE C,

END OF ITAB.

DATA: LVALUE TYPE I,

COUNT TYPE I,

TEMP(20) TYPE C,

CTRL TYPE I,

CTRL1(3) TYPE C,

PAGES(5) TYPE c VALUE '10',

PVALUE(3) TYPE C VALUE 0.

start-of-selection.

PERFORM GETDATA.

SORT ITAB BY NUMBER.

perform open_group.

LOOP AT ITAB.

IF ITAB-NUMBER <> LVALUE.

COUNT = 0.

LOOP AT ITAB WHERE NUMBER = ITAB-NUMBER.

COUNT = COUNT + 1.

ENDLOOP.

perform bdc_dynpro using 'SAPMM06B' '0100'.

perform bdc_field using 'BDC_CURSOR'

'EBAN-BSART'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'EBAN-BSART'

'NB'.

perform bdc_field using 'RM06B-LPEIN'

'T'.

perform bdc_field using 'EBAN-EKGRP'

'008'.

perform bdc_dynpro using 'SAPMM06B' '0106'.

perform bdc_field using 'BDC_CURSOR'

'EBAN-MATNR(01)'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

PAGES = 10.

CTRL = 0.

DO COUNT TIMES.

CTRL = CTRL + 1.

IF CTRL >= 19.

PAGES = PAGES + 10.

ENDIF.

CTRL1 = CTRL.

IF CTRL >= 19.

CTRL1 = '19'.

ENDIF.

IF CTRL >= 10.

PVALUE = ''.

ENDIF.

perform bdc_field using 'RM06B-BNFPO'

PAGES.

Concatenate 'EBAN-MATNR(' PVALUE CTRL1 ')' into Temp.

CONDENSE Temp NO-GAPS.

perform bdc_field using Temp itab-MATNR.

Concatenate 'EBAN-TXZ01(' PVALUE CTRL1 ')' into Temp.

CONDENSE Temp NO-GAPS.

perform bdc_field using Temp itab-TXZ01.

Concatenate 'EBAN-MENGE(' PVALUE CTRL1 ')' into Temp.

CONDENSE Temp NO-GAPS.

perform bdc_field using Temp itab-MENGE.

Concatenate 'EBAN-MEINS(' PVALUE CTRL1 ')' into Temp.

CONDENSE Temp NO-GAPS.

perform bdc_field using Temp itab-MEINS.

Concatenate 'RM06B-EEIND(' PVALUE CTRL1 ')' into Temp.

CONDENSE Temp NO-GAPS.

perform bdc_field using Temp itab-EEIND.

Concatenate 'EBAN-EKGRP(' PVALUE CTRL1 ')' into Temp.

CONDENSE Temp NO-GAPS.

perform bdc_field using Temp itab-EKGRP.

Concatenate 'EBAN-MATKL(' PVALUE CTRL1 ')' into Temp.

CONDENSE Temp NO-GAPS.

perform bdc_field using Temp itab-MATKL.

Concatenate 'EBAN-WERKS(' PVALUE CTRL1 ')' into Temp.

CONDENSE Temp NO-GAPS.

perform bdc_field using Temp itab-WERKS.

perform bdc_dynpro using 'SAPMM06B' '0102'.

perform bdc_field using 'BDC_CURSOR'

'EBAN-MENGE'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'EBAN-TXZ01'

ITAB-TXZ01. "'HD GLAD BOY tank silver'.

perform bdc_field using 'EBAN-MENGE'

ITAB-MENGE. "'500'.

perform bdc_field using 'RM06B-EEIND'

ITAB-EEIND."'27.06.2007'.

perform bdc_field using 'EBAN-EKGRP'

ITAB-EKGRP. "'001'.

perform bdc_field using 'EBAN-BADAT'

'15.06.2007'.

perform bdc_field using 'EBAN-FRGDT'

'26.06.2007'.

perform bdc_field using 'EBAN-PREIS'

' 55.48'.

perform bdc_field using 'EBAN-WAERS'

'GBP'.

perform bdc_field using 'EBAN-PEINH'

'1'.

perform bdc_field using 'EBAN-REPOS'

'X'.

perform bdc_dynpro using 'SAPMM06B' '0106'.

Concatenate 'EBAN-MATNR(' PVALUE CTRL1 ')' into Temp.

CONDENSE Temp NO-GAPS.

perform bdc_field using 'BDC_CURSOR' Temp.

perform bdc_field using 'BDC_OKCODE'

'/00'.

ENDDO.

LVALUE = ITAB-NUMBER.

perform bdc_dynpro using 'SAPMM06B' '0106'.

perform bdc_field using 'BDC_CURSOR'

'RM06B-BNFPO'.

perform bdc_field using 'BDC_OKCODE'

'=BU'.

perform bdc_field using 'RM06B-BNFPO'

'80'.

*AT END OF NUMBER.

perform bdc_transaction using 'ME51'.

REFRESH BDCDATA.

*ENDAT.

ENDIF.

ENDLOOP.

perform close_group.

&----

---

*& Form GETDATA

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM GETDATA .

CALL FUNCTION 'UPLOAD'

EXPORTING

FILENAME = ' '

FILETYPE = ' '

TABLES

DATA_TAB = ITAB

EXCEPTIONS

CONVERSION_ERROR = 1

INVALID_TABLE_WIDTH = 2

INVALID_TYPE = 3

NO_BATCH = 4

UNKNOWN_ERROR = 5

GUI_REFUSE_FILETRANSFER = 6

OTHERS = 7

.

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

IF USEFULL REWARD

[Former Member]

Hi,

Here is the Sample Program to Uplaod from Application Server to SAP, This program will upload TAB delimited file

*&---------------------------------------------------------------------*
*& Report  ZUPLOADTAB                                                  *
*&                                                                     *
*&---------------------------------------------------------------------*
*& Example of Uploading tab delimited file                             *
*&                                                                     *
*&---------------------------------------------------------------------*
REPORT  zuploadtab                    .

PARAMETERS: p_infile  LIKE rlgrap-filename
                        OBLIGATORY DEFAULT  '/usr/sap/'..

DATA: ld_file LIKE rlgrap-filename.

*Internal tabe to store upload data
TYPES: BEGIN OF t_record,
    name1 like pa0002-VORNA,
    name2 like pa0002-name2,
    age   type i,
    END OF t_record.
DATA: it_record TYPE STANDARD TABLE OF t_record INITIAL SIZE 0,
      wa_record TYPE t_record.

*Text version of data table
TYPES: begin of t_uploadtxt,
  name1(10) type c,
  name2(15) type c,
  age(5)  type c,
 end of t_uploadtxt.
DATA: wa_uploadtxt TYPE t_uploadtxt.

*String value to data in initially.
DATA: wa_string(255) type c.

constants: con_tab TYPE x VALUE '09'.

*If you have Unicode check active in program attributes then you will
*need to declare constants as follows:

*class cl_abap_char_utilities definition load.
*constants:
*    con_tab  type c value cl_abap_char_utilities=>HORIZONTAL_TAB.



************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.
ld_file = p_infile.
OPEN DATASET ld_file FOR INPUT IN TEXT MODE ENCODING DEFAULT.
IF sy-subrc NE 0.
ELSE.
  DO.
    CLEAR: wa_string, wa_uploadtxt.
    READ DATASET ld_file INTO wa_string.
    IF sy-subrc NE 0.
      EXIT.
    ELSE.
      SPLIT wa_string AT con_tab INTO wa_uploadtxt-name1
                                      wa_uploadtxt-name2
                                      wa_uploadtxt-age.
      MOVE-CORRESPONDING wa_uploadtxt TO wa_upload.
      APPEND wa_upload TO it_record.
    ENDIF.
  ENDDO.
  CLOSE DATASET ld_file.
ENDIF.


************************************************************************
*END-OF-SELECTION
END-OF-SELECTION.
*!! Text data is now contained within the internal table IT_RECORD

* Display report data for illustration purposes
  loop at it_record into wa_record.
    write:/     sy-vline,
           (10) wa_record-name1, sy-vline,
           (10) wa_record-name2, sy-vline,
           (10) wa_record-age, sy-vline.
  endloop.

Regards

Sudheer

[Former Member]

Hi Sudheer,

Ur code is so helpful.

But have one doubt, here we are opening file in text mode...bt my file is in excel format...then also the following code works?

I want to add some more logic while doing changes into the dtable:

"If the same record is existing in dbase table, the i have to modify the record &

If not i have to insert the record.."

Is the above code is included in loop?/ before close dataset?

Regards

siri

[Former Member]

Hi Siri,

check the following FM:

*&--------------------------------------------------------------------*
*&      Form  UPLOAD_EXCEL_FILE
*&--------------------------------------------------------------------*
*       upload excel spreadsheet into internal table
*---------------------------------------------------------------------*
*      -->P_TABLE    Table to return excel data into
*      -->P_FILE     file name and path
*---------------------------------------------------------------------*
FORM upload_excel_file TABLES   p_table
                       USING    p_file.

  DATA: temp             TYPE truxs_t_text_data,
        t_data           TYPE TABLE OF string.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*   I_FIELD_SEPERATOR          =
*   I_LINE_HEADER              =
      i_tab_raw_data             = temp
      i_filename                 = p_file
    TABLES
      i_tab_converted_data       = p_table
   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.

ENDFORM.                    "UPLOAD_EXCEL

Hope this helps,

Erwan

[Former Member]

hi Siri,

Refer

http://www.sapdevelopment.co.uk/file/file_uploadsap.htm

http://www.sapdevelopment.co.uk/file/file_uptabsap.htm

[Former Member]

HI santhosh,

Here the opening file in text mode...bt my file is in excel format...then also can i use the following code?

I want to add some more logic while doing changes into the dtable:

"If the same record is existing in dbase table, the i have to modify the record &

If not i have to insert the record.."

Is the above code is included in loop?/ before close dataset?