exxel sheet data uploading in sap

Former Member · ‎04-17-2006

r/sir,

im stuck up with a problem.plz help me out if u can.The problem is i want to store the data which is availabe in a excel sheet into sap system through call transaction method of bdc.Plz let me know the technique of uploading the data from a excel sheet into sap system.

vinod_gunaware2 · ‎04-17-2006

Hi

USe function module

<b>ALSM_EXCEL_TO_INTERNAL_TABLE</b> to upload into excel file.

report z.

parameter p_file like rlgrap-filename.

data: w_text type natxt, "message

this internal table should have the same structure
as *.xls file to be uploaded. E.g.:

begin of it_data occurs 0,

s1(10), "text field 1

s2(20), "text field 2

n1 type i, "integer number field

end of it_data.

start-of-selection.

perform f_excel_upload tables it_data

using p_file

changing w_text.

write: / 'Result =', w_text.

end-of-selection.

loop at it_data.

write: / it_data-s1, it_data-s2, it_data-n1.

endloop.

form f_excel_upload tables p_download

using value(p_filename) like rlgrap-filename

changing p_text type natxt.

data: w_start_col type i value '1',

w_start_row type i value '1',

w_end_col type i value '3',

w_end_row type i value '5',

w_rows like sy-fdpos,

w_cols like sy-fdpos,

w_curr_row like sy-fdpos,

w_curr_col like sy-fdpos,

w_index type i,

w_value(99) type c.

data: h_appl like obj_record,

h_work like obj_record,

h_cell like obj_record.

field-symbols: <fs>.

start excel

if h_appl-header = space or h_appl-handle = -1.

create object h_appl 'excel.application'.

if sy-subrc <> 0.

p_text = 'Start Excel Error:'.

write sy-subrc to p_text+19 left-justified.

message i002(sy) with sy-msgli.

endif.

set property of h_appl 'visible' = 0.

endif.

open file

call method of h_appl 'workbooks' = h_work.

call method of h_work 'open' exporting #1 = p_filename.

get data cell by cell

w_rows = w_end_row - w_start_row + 1.

w_cols = w_end_col - w_start_col + 1.

w_curr_row = w_start_row.

do w_rows times.

w_curr_col = w_start_col.

w_index = 1.

do w_cols times.

call method of h_appl 'cells' = h_cell

exporting #1 = w_curr_row

#2 = w_curr_col.

get property of h_cell 'value' = w_value.

assign component w_index of structure p_download to <fs>.

if sy-subrc = 0.

move : w_value to <fs>.

unassign <fs>.

endif.

add 1 to: w_curr_col, w_index.

enddo.

add 1 to w_curr_row.

append p_download.

clear p_download.

enddo.

release excel

call method of h_appl 'quit'.

free object h_appl.

h_appl-handle = -1.

if p_download[] is initial.

p_text = 'No Data Uploaded.'.

else.

describe table p_download lines w_rows.

p_text = 'Uploaded Rows:'.

write w_rows to p_text+15 left-justified.

endif.

endform.

end of source code *

regards

vinod

vinod_gunaware2 · ‎04-17-2006

<b>RH_START_EXCEL_WITH_DATA</b> starts Excel with the contents of an internal table. This function finds Excel in the desktop registry. It also uses a local PC working directory to save the file (that's what the 'W' value for data path flag does). Very transparent to user!

report z.

parameter p_file like rlgrap-filename.

data: w_text type natxt, "message

this internal table should have the same structure
as *.xls file to be uploaded. E.g.:

begin of it_data occurs 0,

s1(10), "text field 1

s2(20), "text field 2

n1 type i, "integer number field

end of it_data.

start-of-selection.

perform f_excel_upload tables it_data

using p_file

changing w_text.

write: / 'Result =', w_text.

end-of-selection.

loop at it_data.

write: / it_data-s1, it_data-s2, it_data-n1.

endloop.

form f_excel_upload tables p_download

using value(p_filename) like rlgrap-filename

changing p_text type natxt.

data: it_intern type kcde_cells occurs 0 with header line,

w_index type i,

w_start_col type i value '1',

w_start_row type i value '1',

w_end_col type i value '256',

w_end_row type i value '65536',

w_rows like sy-fdpos.

field-symbols: <fs>.

call function 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = p_filename

i_begin_col = w_start_col

i_begin_row = w_start_row

i_end_col = w_end_col

i_end_row = w_end_row

TABLES

intern = it_intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

others = 3.

if sy-subrc <> 0.

p_text = 'Upload Error:'.

write sy-subrc to p_text+14 left-justified.

else.

if it_intern[] is initial.

p_text = 'No Data Uploaded.'.

else.

sort it_intern by row col.

loop at it_intern.

move : it_intern-col to w_index.

assign component w_index of structure p_download to <fs>.

if sy-subrc = 0.

move : it_intern-value to <fs>.

unassign <fs>.

endif.

at end of row.

append p_download.

clear p_download.

endat.

endloop.

describe table p_download lines w_rows.

p_text = 'Uploaded Rows:'.

write w_rows to p_text+15 left-justified.

endif.

endform.

end of source code *

regards

vinod

Former Member · ‎04-17-2006

v_start_col TYPE i VALUE '1',

v_start_row TYPE i VALUE '1',

v_end_col TYPE i VALUE '20',

v_end_row TYPE i VALUE '10000'.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = v_start_col

i_begin_row = v_start_row

i_end_col = v_end_col

i_end_row = v_end_row

TABLES

intern = it_itab

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

former_member188685 · ‎04-17-2006

Hi,

report ZTES_MATUP
       no standard page heading line-size 255.

include bdcrecx1.

parameters: dataset(132) lower case.
***    DO NOT CHANGE - the generated data section - DO NOT CHANGE    ***
*
*   If it is nessesary to change the data section use the rules:
*   1.) Each definition of a field exists of two lines
*   2.) The first line shows exactly the comment
*       '* data element: ' followed with the data element
*       which describes the field.
*       If you don't have a data element use the
*       comment without a data element name
*   3.) The second line shows the fieldname of the
*       structure, the fieldname must consist of
*       a fieldname and optional the character '_' and
*       three numbers and the field length in brackets
*   4.) Each field must be type C.
*
*** Generated data section with specific formatting - DO NOT CHANGE  ***
data: begin of record occurs 0,
* data element: MTART
        MTART_001(004),
* data element: MATNR
        MATNR_002(018),
* data element: XFELD
        KZSEL_01_003(001),
* data element: XFELD
        KZSEL_02_004(001),
* data element: MAKTX
        MAKTX_005(040),
* data element: MEINS
        MEINS_006(003),
* data element: SPART
        SPART_007(002),
* data element: MAKTX
        MAKTX_008(040),
      end of record.

*** End generated data section ***

start-of-selection.
  CALL FUNCTION 'GUI_UPLOAD'
   EXPORTING
     FILENAME                      = 'C:TCODEs.xls'
*     FILETYPE                      = 'ASC'
     has_field_separator           = 'X'
   TABLES
     DATA_TAB                      = record
   EXCEPTIONS
     FILE_OPEN_ERROR               = 1
     FILE_READ_ERROR               = 2
     NO_BATCH                      = 3
     GUI_REFUSE_FILETRANSFER       = 4
     INVALID_TYPE                  = 5
     NO_AUTHORITY                  = 6
     UNKNOWN_ERROR                 = 7
     BAD_DATA_FORMAT               = 8
     HEADER_NOT_ALLOWED            = 9
     SEPARATOR_NOT_ALLOWED         = 10
     HEADER_TOO_LONG               = 11
     UNKNOWN_DP_ERROR              = 12
     ACCESS_DENIED                 = 13
     DP_OUT_OF_MEMORY              = 14
     DISK_FULL                     = 15
     DP_TIMEOUT                    = 16
     OTHERS                        = 17.


  IF SY-SUBRC <> 0.
  
  ENDIF.
perform open_group.

loop at record.

read dataset dataset into record.
if sy-subrc <> 0. exit. endif.

perform bdc_dynpro      using 'SAPLMGMM' '0060'.
perform bdc_field       using 'BDC_CURSOR'
                              'RMMG1-AENNR'.
perform bdc_field       using 'BDC_OKCODE'
                              'AUSW'.
perform bdc_field       using 'RMMG1-MTART'
                              record-MTART_001.
perform bdc_field       using 'RMMG1-MATNR'
                              record-MATNR_002.
perform bdc_dynpro      using 'SAPLMGMM' '0070'.
perform bdc_field       using 'BDC_CURSOR'
                              'MSICHTAUSW-DYTXT(02)'.
perform bdc_field       using 'BDC_OKCODE'
                              '=ENTR'.
perform bdc_field       using 'MSICHTAUSW-KZSEL(01)'
                              record-KZSEL_01_003.
perform bdc_field       using 'MSICHTAUSW-KZSEL(02)'
                              record-KZSEL_02_004.
perform bdc_dynpro      using 'SAPLMGMM' '4004'.
perform bdc_field       using 'BDC_OKCODE'
                              '/00'.
perform bdc_field       using 'MAKT-MAKTX'
                              record-MAKTX_005.
perform bdc_field       using 'BDC_CURSOR'
                              'MARA-SPART'.
perform bdc_field       using 'MARA-MEINS'
                              record-MEINS_006.
perform bdc_field       using 'MARA-SPART'
                              record-SPART_007.
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'
                              record-MAKTX_008.
perform bdc_dynpro      using 'SAPLSPO1' '0300'.
perform bdc_field       using 'BDC_OKCODE'
                              '=YES'.
perform bdc_transaction using 'MM01'.

endloop.

perform close_group.

Regards

vijay

Former Member · ‎04-20-2007

thanks.

Former Member · ‎04-20-2007

thanks.