topic Upload excel file in Application Development and Automation Discussions

Upload excel file

Former Member — Mon, 02 Jul 2007 08:01:43 GMT

hi,

How to upload excel file from Application server into SAP?

Re: Upload excel file

Former Member — Mon, 02 Jul 2007 08:03:39 GMT

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

Re: Upload excel file

Former Member — Mon, 02 Jul 2007 08:07:53 GMT

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

Re: Upload excel file

Former Member — Mon, 02 Jul 2007 08:08:27 GMT

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

Re: Upload excel file

Former Member — Mon, 02 Jul 2007 08:15:18 GMT

hi Siri,

Refer

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

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

Re: Upload excel file

Former Member — Mon, 02 Jul 2007 09:48:56 GMT

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

Re: Upload excel file

Former Member — Mon, 02 Jul 2007 10:40:30 GMT

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?