topic Re: Uploading Excel file in Application Development and Automation Discussions

Uploading Excel file

Former Member — Tue, 01 Jul 2008 06:41:43 GMT

Hi,

In my BDC program, i am trying to upload the data from Excel file ti ITAB using the FM, GUI_UPLOAD. while passing paramters, what should i give for FILETYPE. First of all can we upload data from Excel file using GUI_UPLOAD.

Thanks,

Shyam.

Re: Uploading Excel file

GauthamV — Tue, 01 Jul 2008 06:43:54 GMT

hi shyam,

WELCOME TO SDN.

check this sample program.

report ZGM_BDC_MM01

no standard page heading line-size 255.

data: it_bdcdata type standard table of bdcdata with header line.

data: begin of it_mm01 occurs 0,

mbrsh(1) type c,

mtart(4) type c,

werks(4) type c,

maktx(40) type c,

meins(3) type c,

matkl(9) type c,

bklas(4) type c,

end of it_mm01.

DATA : W_FILE TYPE STRING.

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

PARAMETERs : filename LIKE RLGRAP-FILENAME.

SELECTION-SCREEN END OF BLOCK 1.

ACCEPTING THE FILE FROM USER **************

AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = ' '

IMPORTING

FILE_NAME = filename.

START-OF-SELECTION.

************UPLOADING THE FILE *****************************

START-OF-SELECTION.

W_FILE = filename.

*CALLING THE FUNC GUI_UPLAOD TO COPY THE DATA FROM FLAT FILE TO

INTERNAL TABLE

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = W_FILE

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

tables

data_tab = it_mm01.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

*************Creaton the BDC_Open_Group************

CALL FUNCTION 'BDC_OPEN_GROUP'

EXPORTING

CLIENT = SY-MANDT

GROUP = 'ZMM01'

KEEP = 'X'

USER = SY-UNAME.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

*include bdcrecx1.

start-of-selection.

*perform open_group.

Creation the Fill_Data ****************

loop at it_mm01.

refresh it_bdcdata.

clear it_bdcdata.

perform bdc_dynpro using 'SAPLMGMM' '0060'.

perform bdc_field using 'BDC_CURSOR'

'RMMG1-MTART'.

perform bdc_field using 'BDC_OKCODE'

'=ENTR'.

perform bdc_field using 'RMMG1-MBRSH'

it_mm01-mbrsh.

perform bdc_field using 'RMMG1-MTART'

it_mm01-mtart.

perform bdc_dynpro using 'SAPLMGMM' '0070'.

perform bdc_field using 'BDC_CURSOR'

'MSICHTAUSW-DYTXT(09)'.

perform bdc_field using 'BDC_OKCODE'

'=ENTR'.

perform bdc_field using 'MSICHTAUSW-KZSEL(01)'

'X'.

perform bdc_field using 'MSICHTAUSW-KZSEL(02)'

'X'.

perform bdc_field using 'MSICHTAUSW-KZSEL(09)'

'X'.

perform bdc_dynpro using 'SAPLMGMM' '0080'.

perform bdc_field using 'BDC_CURSOR'

'RMMG1-WERKS'.

perform bdc_field using 'BDC_OKCODE'

'=ENTR'.

perform bdc_field using 'RMMG1-WERKS'

it_mm01-werks.

perform bdc_dynpro using 'SAPLMGMM' '4004'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'MAKT-MAKTX'

it_mm01-maktx.

perform bdc_field using 'BDC_CURSOR'

'MARA-MATKL'.

perform bdc_field using 'MARA-MEINS'

it_mm01-meins.

perform bdc_field using 'MARA-MATKL'

it_mm01-matkl.

perform bdc_dynpro using 'SAPLMGMM' '4004'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'BDC_CURSOR'

'MAKT-MAKTX'.

perform bdc_field using 'MAKT-MAKTX'

it_mm01-maktx.

perform bdc_dynpro using 'SAPLMGMM' '4000'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'MAKT-MAKTX'

it_mm01-maktx.

perform bdc_field using 'MARA-MEINS'

'EA'.

perform bdc_field using 'BDC_CURSOR'

'MBEW-BKLAS'.

perform bdc_field using 'MBEW-BKLAS'

it_mm01-bklas.

perform bdc_field using 'MBEW-VPRSV'

'V'.

perform bdc_field using 'MBEW-PEINH'

'1'.

perform bdc_dynpro using 'SAPLSPO1' '0300'.

perform bdc_field using 'BDC_OKCODE'

'=YES'.

CALL FUNCTION 'BDC_INSERT'

EXPORTING

TCODE = 'MM01'

POST_LOCAL = NOVBLOCAL
PRINTING = NOPRINT
SIMUBATCH = ' '
CTUPARAMS = ' '

TABLES

dynprotab = IT_BDCDATA.

IF sy-subrc = 0.

message 'sesion created' type 'S'.

ENDIF.

endloop.

CALL FUNCTION 'BDC_CLOSE_GROUP'

EXCEPTIONS

NOT_OPEN = 1

QUEUE_ERROR = 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.

----

Start new screen *

----

FORM BDC_DYNPRO USING PROGRAM DYNPRO.

CLEAR IT_BDCDATA.

IT_BDCDATA-PROGRAM = PROGRAM.

IT_BDCDATA-DYNPRO = DYNPRO.

IT_BDCDATA-DYNBEGIN = 'X'.

APPEND IT_BDCDATA.

ENDFORM.

----

Insert field *

----

FORM BDC_FIELD USING FNAM FVAL.

IF FVAL <> NODATA.

CLEAR IT_BDCDATA.

IT_BDCDATA-FNAM = FNAM.

IT_BDCDATA-FVAL = FVAL.

APPEND IT_BDCDATA.

ENDIF.

ENDFORM.

Re: Uploading Excel file

Former Member — Tue, 01 Jul 2008 06:44:23 GMT

Shyam,

TYPE-POOLS: truxs .
DATA: BEGIN OF it_excel OCCURS 0,
  belnr(10),
  xblnr(16),
  bktxt(25),
  bldat(10),
  zuonr(18),
  oac(1),
  dmbtr(16),
  pszah(15),
  wrbtr(16),
  kunnr(10),
  budat(10),
  konto(16),
  bukrs(04),
  waers(05),
  agkon(16),
  prctr(10),
  wrbtr1(16),
  spesw(15),
  kostl(10),
  sgtxt(50),

END OF it_excel.

PARAMETER: pfile LIKE rlgrap-filename OBLIGATORY.

DATA: it_raw TYPE truxs_t_text_data.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR          =
      i_line_header              = 'X'
      i_tab_raw_data             = it_raw
      i_filename                 = pfile
    TABLES
      i_tab_converted_data       = it_excel[]
*   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.

Amit.

Re: Uploading Excel file

Former Member — Tue, 01 Jul 2008 06:44:58 GMT

Hi,

U cant upload Excel file with GUI_UPLOAD but try to use ALSM_EXCEL_TO_INTERNAL_TABLE function module.

File type shoould be "rlgrap-filename "

Refer this demo...

http://www.saptechnical.com/Tips/ABAP/UploadExcel/Prg.htm

Regards.

Edited by: Iyswarya Godi on Jul 1, 2008 8:45 AM

Edited by: Iyswarya Godi on Jul 1, 2008 8:47 AM

Re: Uploading Excel file

Former Member — Tue, 01 Jul 2008 06:45:06 GMT

Hi,

Use this function module ALSM_EXCEL_TO_INTERNAL_TABLE

And also check the beloe link for example.

http://www.sapdev.co.uk/file/file_upexcelalt1.htm

Reward if useful.

Regards,

Swetha.

Re: Uploading Excel file

Former Member — Tue, 01 Jul 2008 06:47:45 GMT

Hi,

try the below code.

In the funtion module give the file path of your file.

DATA:
  t_employee LIKE
    STANDARD TABLE
          OF alsmex_tabline
        WITH DEFAULT KEY.


*"--------------------------------------------------------------------*
* Data declaration of the structure to hold data from table t_employee*
*"--------------------------------------------------------------------*
DATA:
  fs_employee LIKE
         LINE OF t_employee.

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

START-OF-SELECTION.
  PERFORM get_data_from_excel_file.

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

END-OF-SELECTION.
  PERFORM display.


*&---------------------------------------------------------------------*
*&      Form  get_data_from_excel_file                                 *
*&---------------------------------------------------------------------*
* Subroutine To get Data from Excel file and transfering it to internal*
*  table                                                               *
*----------------------------------------------------------------------*
*  There are no interface parameters to be passed to this subroutine.  *
*----------------------------------------------------------------------*
FORM get_data_from_excel_file .


  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename    = 'd:\trainees.xls'
      i_begin_col = 1
      i_begin_row = 1
      i_end_col   = 2
      i_end_row   = 20
    TABLES
      intern      = t_employee.

ENDFORM.                    " get_data_from_excel_file



*&---------------------------------------------------------------------*
*&      Form  display                                                  *
*&---------------------------------------------------------------------*
* Subroutine To Display  Employee Details                              *
*----------------------------------------------------------------------*
*  There are no interface parameters to be passed to this subroutine.  *
*----------------------------------------------------------------------*
FORM display.

  LOOP AT t_employee INTO fs_employee.
    CASE fs_employee-col.
      WHEN '0001'.

        WRITE: /3 fs_employee-value.
      WHEN '0002'.
        WRITE: 10 fs_employee-value  .

    ENDCASE.
  ENDLOOP.

ENDFORM.                                " Display

Best regards,

Brijesh

Re: Uploading Excel file

Subhankar — Tue, 01 Jul 2008 06:54:15 GMT

please do it like this way.

CALL FUNCTION 'Z_ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_f_pc

i_begin_col = '1' <--- Begining column number

i_begin_row = '2' <--- Begining column number

i_end_col = '4' <----Column number

i_end_row = p_e_row <--- Maximum no. of row.

TABLES

intern = i_data

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

Re: Uploading Excel file

Former Member — Tue, 01 Jul 2008 07:00:34 GMT

hi....

When you are uploading data from excel file the file type should be 'ASC' and if you are upoading from text file then the file type should be 'DAT'.

the important parmerts that you hould pass is filetype and the internal table in which you want to fecth your data.

Re: Uploading Excel file

Former Member — Tue, 01 Jul 2008 07:14:04 GMT

Use the function Module:

ALSM_EXCEL_TO_INTERNAL_TABLE

Sample COde Without using Field Symbols:

DATA: BEGIN OF EXCEL OCCURS 0.

INCLUDE STRUCTURE ALSMEX_TABLINE.

DATA: END OF EXCEL.

PARAMETER: FILE TYPE RLGRAP-FILENAME.

at selection-screen on value-request for FILE.

types: begin of TY_DATA,

data element: MATNR

MAT_NO(018),

data element: MAKTX

MAKTX(040),

end of TY_DATA.

DATA: RECORD1 TYPE STANDARD TABLE OF TY_DATA,

RECORD TYPE TY_DATA.

End generated data section ***

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = ' '

IMPORTING

FILE_NAME = FILE

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = FILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 4

I_END_COL = 2

I_END_ROW = 38

TABLES

INTERN = EXCEL

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

loop at excel.

case excel-col.

when '001'.

RECORD-MAT_NO = EXCEL-VALUE.

wHEN '002'.

RECORD-MAKTX = EXCEL-VALUE.

endcase.

at end of row.

append RECORD to RECORD1.

clear RECORD.

endat.

endloop. " LOOP AT EXCEL

+Sample Code Using Field Symbols.+

DATA: L_INTER LIKE TABLE OF ALSMEX_TABLINE WITH HEADER LINE ,

L_INDEX TYPE I.

types: begin of TY_DATA,

MAT_NO(018),

MAKTX(040),

end of TY_DATA.

DATA: RECORD1 TYPE STANDARD TABLE OF TY_DATA,

RECORD TYPE TY_DATA.

FIELD-SYMBOLS <FS>.

parameter P_INFILE LIKE RLGRAP-FILENAME.

at selection-screen on value-request for P_INFILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = ' '

IMPORTING

FILE_NAME = P_INFILE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_INFILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 4

I_END_COL = 2

I_END_ROW = 38

TABLES:

INTERN = L_INTER

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3

SORT L_INTER BY ROW COL.

LOOP AT L_INTER .

MOVE L_INTER-COL TO L_INDEX.

ASSIGN COMPONENT L_INDEX OF STRUCTURE RECORD TO <FS>.

MOVE L_INTER-VALUE TO <FS>.

AT END OF ROW.

APPEND RECORD to RECORD1.

clear record.

ENDAT.

ENDLOOP.

Regards

VIjay

Re: Uploading Excel file

former_member787646 — Tue, 01 Jul 2008 07:23:30 GMT

Hi,

Try the following code.

TYPE-POOLS: truxs .

DATA: it_raw TYPE truxs_t_text_data.

Now use the following function module to load data from excel

file into the internal table.*

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = <excel file variable name>

TABLES

i_tab_converted_data = <internal table name>

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.

Hope it helps you.

Murthy