topic Re: uploading excel sheet in Application Development and Automation Discussions

uploading excel sheet

Former Member — Thu, 23 Apr 2009 11:23:41 GMT

hi friends

problem in uploading.

i have used KCD_EXCEL_OLE_TO_INT_CONVERT functional module .

do we need upload functional module even if we use above functional module.

thanks&regards

deepika.

Re: uploading excel sheet

Former Member — Thu, 23 Apr 2009 12:10:43 GMT

Hi Deepika,

Try with the below function module.

ALSM_EXCEL_TO_INTERNAL_TABLE

Refer the sample code below.


CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
        EXPORTING
          filename                = file_name (file name with complete path)
          i_begin_col             = 1
          i_begin_row             = 2
          i_end_col               = 10
          i_end_row               = 65536
        TABLES
          intern                  = itab_name
        EXCEPTIONS
          inconsistent_parameters = 1
          upload_ole                     = 2
          OTHERS                       = 3.

      IF sy-subrc <> 0.

          Appropriate message.

      ENDIF.

Re: uploading excel sheet

Former Member — Thu, 23 Apr 2009 12:33:59 GMT

hi ,

no need to use upload FM . KCD_EXCEL_OLE_TO_INT_CONVERT it is enough .. u can proceed .

~linganna

Re: uploading excel sheet

Former Member — Thu, 23 Apr 2009 16:23:32 GMT

Hi,

you can use either "KCD_EXCEL_OLE_TO_INT_CONVERT " FM or "ALSM_EXCEL_TO_INTERNAL_TABLE" to upload the excel into internal table.

What kind of err you are getting?

Re: uploading excel sheet

Former Member — Thu, 23 Apr 2009 16:39:04 GMT

Hi,

Check this Code..

 DATA l_count TYPE sy-tabix.

   CONSTANTS: lc_begin_col TYPE i VALUE '1',
              lc_begin_row TYPE i VALUE '2',
              lc_end_col   TYPE i VALUE '2',
              lc_end_row   TYPE i VALUE '3000'.

* Begin of CALK912848 - Carlos Werberich - 16Sep08
  CLEAR p_i_excel_data. REFRESH p_i_excel_data.
* End   of CALK912848 - Carlos Werberich - 16Sep08

* Function module to read excel file and convert it into internal table
   CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
     EXPORTING
       filename                = p_p_file
       i_begin_col             = lc_begin_col
       i_begin_row             = lc_begin_row
       i_end_col               = lc_end_col
       i_end_row               = lc_end_row
     TABLES
       intern                  = i_data
     EXCEPTIONS
       inconsistent_parameters = 1
       upload_ole              = 2
       OTHERS                  = 3.
* Error in file upload
   IF sy-subrc NE 0 .
     MESSAGE text-006 TYPE 'E'.
     EXIT.
   ENDIF.
   IF i_data[] IS INITIAL .
     MESSAGE text-007 TYPE 'E'.
     EXIT.
   ELSE.
     SORT i_data BY row col .
* Loop to fill data in Internal Table
     LOOP AT i_data .
       MOVE i_data-col TO l_count .
       ASSIGN COMPONENT l_count OF STRUCTURE p_i_excel_data TO <fs_source> .
       MOVE i_data-value TO <fs_source> .
       AT END OF row .
* Append data into internal table
         APPEND p_i_excel_data.
         CLEAR p_i_excel_data.
       ENDAT .
     ENDLOOP .
   ENDIF .

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 05:11:10 GMT

code is syntactically correct when i execute data is not uploading..and i have given mode as ' E'.

when i execute it is asking plant value to enter and when i presss enter it goes to next screen and it is asking manually to enter values....

i think data should uploaded automatically right...

what is wrong in it?

any one suggest me kindly.

what might be the reason?

thanks&regards

deepika.

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 05:38:47 GMT

hi,

use this sample code and according to u r program..

REPORT Zsample.

TYPE-POOLS TRUXS.

data: gi_lfa1 like lfa1 occurs 0 with header line.

data ITAB LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA IT_RAW TYPE TRUXS_T_TEXT_DATA.

DATA: begin of GI_EXEL OCCURS 0,

lifnr like lfb1-lifnr, "Vendor

bukrs like lfb1-bukrs, "Company Code

ekorg like lfm1-ekorg, "Purch. Organization

end of gi_exl.

DATA IT_RAW TYPE TRUXS_T_TEXT_DATA.

DATA : ITAB LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA ROW LIKE ALSMEX_TABLINE-ROW.

INITIALIZATION.

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

SELECT-OPTIONS : RECORDS FOR COUNT.

PARAMETER : PFNAME1 LIKE RLGRAP-FILENAME OBLIGATORY.

SELECTION-SCREEN:SKIP.

SELECTION-SCREEN : END OF BLOCK B1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR PFNAME1.

PERFORM SEARCH1.

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = PFNAME1

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 54

I_END_ROW = 65000

TABLES

INTERN = ITAB

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.

ROW = 1.

LOOP AT ITAB.

IF ITAB-ROW <> ROW.

APPEND GI_EXEL.

CLEAR GI_EXEL.

ENDIF.

CASE ITAB-COL.

WHEN '1'.

GI_EXEL-lifnr = itab-value.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = gi_exel-lifnr

IMPORTING

output = gi_exel-lifnr.

WHEN '2'.

GI_EXEL-bukrs = itab-value.

WHEN '3'.

GI_EXEL-ekorg = itab-value.

ENDCASE.

ROW = ITAB-ROW.

ENDLOOP.

APPEND GI_EXEL.

CLEAR GI_EXEL.

IF NOT GI_EXEL[] IS INITIAL.

SORT GI_EXEL BY LIFNR bukrs ekorg ktokk.

DELETE ADJACENT DUPLICATES FROM GI_EXEL COMPARING LIFNR bukrs ekorg

ktokk .

FORM SEARCH1.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

STATIC = 'X'

CHANGING

FILE_NAME = PFNAME1.

ENDFORM. " search

hope it will help u .

~linganna

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 06:06:26 GMT

after writing the above code.

loop at GI_EXEL.

perform bdc_dynpro using 'SAPLMGMM' '0060'.

perform bdc_field using 'BDC_CURSOR'

'RMMG1-MTART'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'RMMG1-MATNR'

GI_EXEL-MATNR.

perform bdc_field using 'RMMG1-MBRSH'

GI_EXEL-MBRSH.

perform bdc_field using 'RMMG1-MTART'

GI_EXEL-MTART.

............

..........

followed by generated code from recording.

but it is throwing an error.statement is not accesible.

thanks&regards

deepika.

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 07:05:54 GMT

hi,

put break point on loop at gi_exel . check the data is populating correctly .

and did used perform open_group i think if u debugg carrfuly u can get the result .

let me know if any issue.

~linganna

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 07:21:33 GMT

no batch input data for screen 'SAPLMGMM' '0080'?

getting error

what does it mean?

thanks&regards

deeepika.

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 08:02:50 GMT

Hi deepika,

perform bdc_dynpro using 'SAPLMGMM' '0060'.

perform bdc_field using 'BDC_CURSOR'

'RMMG1-MTART'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'RMMG1-MATNR'

GI_EXEL-MATNR.

perform bdc_field using 'RMMG1-MBRSH'

GI_EXEL-MBRSH.

perform bdc_field using 'RMMG1-MTART'

GI_EXEL-MTART.

that means that in the sceen 'SAPLMGMM' '0060'

in any of the fields MATNR MBRSH' MTART there is no value in the itab.

chack that all the entries are uploaded correcftly in the itab.

if not check in A mode where these fields are getting the data or not

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 08:33:04 GMT

No batch input data for screen SAPLMGMM 0080

when i execute im getting error in this way?

what is wrong with this ?

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 09:02:18 GMT

hi,

post u r code then we will solve ur problem .....

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 10:02:43 GMT

FM:ALSM_EXCEL_TO_INTERNAL_TABLE* will serve the purpose.

*----------------------------------------------------------------------*
* Type decelaration to pass Customer data. *
*----------------------------------------------------------------------*
TYPES: BEGIN OF type_s_tab,
           matnr(18) TYPE C,
           mbrsh(1) TYPE C,
           mtart(4) TYPE C,
           werks(4) TYPE C,
           lgort(4) TYPE C,
           maktx(40) TYPE C,
           meins(3) type c,
           matkl(9) type c,
           ekgrp(3) TYPE C,
           bklas(4) TYPE C,
           vprsv(1) TYPE C,
           peinh(6) TYPE C,
           verpr(5) TYPE C,
     END OF type_s_tab.
* *----------------------------------------------------------------------*
* * Field string decelaration to pass Customer data. *
* *----------------------------------------------------------------------*
 DATA: fs_tab TYPE type_s_tab.
* *----------------------------------------------------------------------*
* * Internal table decelaration to pass Customer data. * *----------------------------------------------------------------------*
 DATA: t_tab LIKE STANDARD TABLE OF fs_tab.
* *----------------------------------------------------------------------*
* * BDC UPLOAD TABLE *
* *----------------------------------------------------------------------*
 DATA: t_bdc LIKE STANDARD TABLE OF bdcdata WITH HEADER LINE.
* *----------------------------------------------------------------------*
* * BDC MESSAGE TABLE . * *----------------------------------------------------------------------*
 DATA: t_messages LIKE STANDARD TABLE OF bdcmsgcoll WITH HEADER LINE,
 w_msg(72) TYPE c.






 " Message text *----------------------------------------------------------------------*
* * START-OF-SELECTION. * *----------------------------------------------------------------------*
 START-OF-SELECTION.
 PERFORM file_upload.
 LOOP AT t_tab INTO fs_tab.


perform bdc_dynpro      using 'SAPLMGMM' '0060'.
perform bdc_field       using 'BDC_CURSOR'
                              'RMMG1-MTART'.
perform bdc_field       using 'BDC_OKCODE'
                              '/00'.
perform bdc_field       using 'RMMG1-MATNR'
                              FS_TAB-MATNR.
perform bdc_field       using 'RMMG1-MBRSH'
                             FS_TAB-MBRSH.
perform bdc_field       using 'RMMG1-MTART'
                              FS_TAB-MTART.
perform bdc_dynpro      using 'SAPLMGMM' '0070'.
perform bdc_field       using 'BDC_CURSOR'
                              'MSICHTAUSW-DYTXT(09)'.
perform bdc_field       using 'BDC_OKCODE'
                              '/00'.
perform bdc_field       using 'MSICHTAUSW-KZSEL(01)'
                              'X'.
perform bdc_field       using 'MSICHTAUSW-KZSEL(09)'
                              'X'.
perform bdc_dynpro      using 'SAPLMGMM' '0070'.
perform bdc_field       using 'BDC_CURSOR'
                              'MSICHTAUSW-DYTXT(01)'.
perform bdc_field       using 'BDC_OKCODE'
                              '/00'.
perform bdc_dynpro      using 'SAPLMGMM' '0070'.
perform bdc_field       using 'BDC_CURSOR'
                              'MSICHTAUSW-DYTXT(01)'.
perform bdc_field       using 'BDC_OKCODE'
                              '/00'.
perform bdc_dynpro      using 'SAPLMGMM' '0070'.
perform bdc_field       using 'BDC_CURSOR'
                              'MSICHTAUSW-DYTXT(13)'.
perform bdc_field       using 'BDC_OKCODE'
                              '=ENTR'.
perform bdc_field       using 'MSICHTAUSW-KZSEL(08)'
                              'X'.
perform bdc_field       using 'MSICHTAUSW-KZSEL(13)'
                              'X'.
perform bdc_dynpro      using 'SAPLMGMM' '0080'.
perform bdc_field       using 'BDC_CURSOR'
                              'RMMG1-LGORT'.
perform bdc_field       using 'BDC_OKCODE'
                              '=ENTR'.
perform bdc_field       using 'RMMG1-WERKS'
                              FS_TAB-WERKS.
perform bdc_field       using 'RMMG1-LGORT'
                             FS_TAB-LGORT.
perform bdc_dynpro      using 'SAPLMGMM' '4004'.
perform bdc_field       using 'BDC_OKCODE'
                              '/00'.
perform bdc_field       using 'MAKT-MAKTX'
                              FS_TAB-MAKTX.
perform bdc_field       using 'BDC_CURSOR'
                              'MARA-MATKL'.
perform bdc_field       using 'MARA-MEINS'
                             FS_TAB-MEINS.
perform bdc_field       using 'MARA-MATKL'
                              FS_TAB-MATKL.
perform bdc_dynpro      using 'SAPLMGMM' '4000'.
perform bdc_field       using 'BDC_OKCODE'
                              '/00'.
perform bdc_field       using 'MAKT-MAKTX'
                              FS_TAB-MAKTX.
perform bdc_field       using 'BDC_CURSOR'
                              'MARC-EKGRP'.
perform bdc_field       using 'MARA-MEINS'
                             FS_TAB-MEINS.
perform bdc_field       using 'MARC-EKGRP'
                             FS_TAB-EKGRP.
perform bdc_field       using 'MARA-MATKL'
                              FS_TAB-MATKL.
perform bdc_dynpro      using 'SAPLMGMM' '4000'.
perform bdc_field       using 'BDC_OKCODE'
                              '/00'.
perform bdc_field       using 'BDC_CURSOR'
                              'MAKT-MAKTX'.
perform bdc_field       using 'MAKT-MAKTX'
                             FS_TAB-MAKTX.
perform bdc_field       using 'MARA-MEINS'
                             FS_TAB-MEINS.
*perform bdc_field       using 'MARA-IPRKZ'
*                              record-IPRKZ_019.
perform bdc_dynpro      using 'SAPLMGMM' '4000'.
perform bdc_field       using 'BDC_OKCODE'
                              '=BU'.
perform bdc_field       using 'MAKT-MAKTX'
                          FS_TAB-MAKTX.
perform bdc_field       using 'MARA-MEINS'
                             FS_TAB-MEINS.
perform bdc_field       using 'BDC_CURSOR'
                              'MBEW-VERPR'.
perform bdc_field       using 'MBEW-BKLAS'
                            FS_TAB-BKLAS.
perform bdc_field       using 'MBEW-VPRSV'
                              FS_TAB-VPRSV.
perform bdc_field       using 'MBEW-PEINH'
                              FS_TAB-PEINH.
perform bdc_field       using 'MBEW-VERPR'
                             FS_TAB-VERPR.
*perform bdc_transaction using 'MM01'.




 CALL TRANSACTION 'MM01' USING t_bdc MODE 'N' messages into t_messages.
 refresh t_bdc.
 clear t_bdc.


 ENDLOOP.
* " LOOP AT T_TAB INTO FS_TAB.
 LOOP AT t_messages.
 CALL FUNCTION 'FORMAT_MESSAGE'
 EXPORTING id = sy-msgid
 lang = '-D'
 no = sy-msgno
 v1 = sy-msgv1
 v2 = sy-msgv2
 v3 = sy-msgv3
 v4 = sy-msgv4
 IMPORTING msg = w_msg
 EXCEPTIONS not_found = 1
 OTHERS = 2.
 IF sy-subrc EQ 0.
 WRITE : / w_msg.
 ENDIF. "IF SY-SUBRC NE  0.




 ENDLOOP.
 " LOOP AT T_MESSAGE
* *----------------------------------------------------------------------*
* * Start new screen *
*----------------------------------------------------------------------*
 FORM bdc_dynpro USING program dynpro.
 CLEAR t_bdc.
 t_bdc-program = program.
 t_bdc-dynpro = dynpro.
 t_bdc-dynbegin = 'X'.
 APPEND t_bdc.
 ENDFORM. " bdc_dynpro
* *----------------------------------------------------------------------*
* insert field *
* *----------------------------------------------------------------------*
 FORM bdc_field USING fnam fval.
 CLEAR t_bdc.
 t_bdc-fnam = fnam.
 t_bdc-fval = fval.
 APPEND t_bdc.
 ENDFORM. " bdc_field
* *&---------------------------------------------------------------------*
* form FILE_UPLOAD.
** *&---------------------------------------------------------------------*
* *
* SUBROUTINE TO UPLOAD DAATA INTO INTERNAL TABLE FROM LOCAL FILE
** *----------------------------------------------------------------------*
* * NO INTERFACE PARAMETERS PASSED
* *----------------------------------------------------------------------*
 FORM file_upload .
* *Local table to upload data-------------------------------------------*
 DATA: lw_tab LIKE STANDARD TABLE OF alsmex_tabline WITH HEADER LINE.

 CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
 EXPORTING
 filename = 'D:\BDC.XLS'
 i_begin_col = 1
 i_begin_row = 1
 i_end_col = 13
 i_end_row = 48
 TABLES intern = lw_tab
 EXCEPTIONS
 inconsistent_parameters = 1
 upload_ole = 2 OTHERS = 3.
 CASE sy-subrc.
 WHEN 1. MESSAGE 'Inconsistent parameters'(001) TYPE 'S'.
 LEAVE LIST-PROCESSING.
 WHEN 2. MESSAGE 'File download failed'(002) TYPE 'S'.
 LEAVE LIST-PROCESSING.
 ENDCASE. " CASE SY-SUBRC.


 LOOP AT lw_tab.
 CASE lw_tab-col.
 WHEN 1.
 fs_tab-MATNR = lw_tab-value.
 WHEN 2.
 fs_tab-MBRSH = lw_tab-value.
 WHEN 3.
 fs_tab-MTART = lw_tab-value.
 WHEN 4.
 fs_tab-WERKS = lw_tab-value.
 WHEN 5.
 fs_tab-LGORT = lw_tab-value.
 WHEN 6.
 fs_tab-MAKTX = lw_tab-value.
 WHEN 7.
 fs_tab-MEINS = lw_tab-value.

 WHEN 8.
 fs_tab-MATKL = lw_tab-value.
 WHEN 9.

 fs_tab-EKGRP = lw_tab-value.
 WHEN 10.
 fs_tab-BKLAS = lw_tab-value.
 WHEN 11.
 fs_tab-VPRSV = lw_tab-value.
 WHEN 12.
 FS_TAB-PEINH = lw_tab-value.
 WHEN 13.
 FS_TAB-VERPR = lw_tab-value.

 APPEND fs_tab TO t_tab.
 CLEAR fs_tab.
 ENDCASE. " CASE LW_TAB-COL.
 ENDLOOP. " LW_TAB
 ENDFORM. " FILE_UPLOAD

Re: uploading excel sheet

Former Member — Fri, 24 Apr 2009 12:13:28 GMT

some of mandatory fileds values are missing ,wehn you process the session, try to check which are the values are missing your internal table...

Re: uploading excel sheet

Former Member — Sat, 25 Apr 2009 04:49:05 GMT

i have checked out the fields declared in internal table it is clear.

i have done mant times .

i guess some coding problem.

thanks&regards

deepika.