Moving excel data to R/3 table fields

Former Member — Mon, 07 Apr 2008 11:50:18 GMT

Hi,

I have a requirement. I would like to modify some fields with values mentioned in the excel file.

Can anyone provide me with a corresponding function module and a code snippet.

Wish you great time.

Best Regards

Sid

Re: Moving excel data to R/3 table fields

Former Member — Mon, 07 Apr 2008 11:52:28 GMT

hi thr

to upload multiple multitab Excel sheets or Ranges from Front end to SAP

Please refer to

http://www.sap-img.com/abap/abap-object-oriented-spreadsheet-with-unlimited-power.htm

Visit

http://help.sap.com/saphelp_47x200/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm

and

http://help.sap.com/saphelp_47x200/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm

You need some basic idea of range object in excel.

You need to create XLS with named ranges or create ranges dynamically.

This could be a neat way to upload XLS the OO way!

The function Module zjnc_get_range reads 1 range into any Internal

table.

DATA: BEGIN OF it_test OCCURS 0,

vpd LIKE mseg-menge,

vas LIKE mkpf-budat,

vkm LIKE mseg-matnr,

END OF it_test.

CALL FUNCTION 'ZJNC_GET_RANGE'

EXPORTING

rangename = 'test'

itabname = 'IT_TEST[]'

irecname = 'it_test'

spreadsheetintf = spreadsheetintf.

=Work!$A$14:$C$16 is range "test"

Numbers & Character data are no problem BUT dates are.

In Excel default date is mm/dd/yyyy but is dependent on PC's

international setting which is normally default

To Avoid any 5-March 3-May type mix-up, I have designed the FM so that you need to

enter dates as 'dd.Mon.yyyy i.e. in Characters in "Internet Date Format"

FUNCTION zjnc_get_range.

*"----

""Local interface:

*" IMPORTING

*" REFERENCE(RANGENAME) TYPE C

*" REFERENCE(ITABNAME) TYPE C

*" REFERENCE(IRECNAME) TYPE C

*" REFERENCE(SPREADSHEETINTF) TYPE REF TO I_OI_SPREADSHEET

*"----

*" REFERENCE(SPREADSHEETINTF) TYPE REF TO I_OI_SPREADSHEET

*"----

DATA:

stru_ref TYPE REF TO cl_abap_structdescr,

comp_tab TYPE abap_compdescr_tab,

one_comp TYPE abap_compdescr,

one_name TYPE string,

type_ref TYPE REF TO cl_abap_typedescr,

is_ddic TYPE abap_bool,

lt_ddic TYPE dd_x031l_table,

wa_ddic TYPE x031l.

DATA: zjncranges TYPE soi_range_list,

zjnccontents TYPE soi_generic_table,

zjnconerange TYPE soi_range_item,

zjnconeitem TYPE soi_generic_item,

prevrow(4) TYPE n,

nrow(4) TYPE n,

ncolumn(4) TYPE n,

mystring TYPE string,

mydate LIKE sy-datum.

FIELD-SYMBOLS: <fs_type> TYPE ANY,

<fs_table> TYPE STANDARD TABLE,

<fs_line> TYPE ANY.

CONCATENATE '(' sy-cprog ')' itabname INTO mystring.

ASSIGN (mystring) TO <fs_table>.

CONCATENATE '(' sy-cprog ')' irecname INTO mystring.

ASSIGN (mystring) TO <fs_line>.

stru_ref ?= cl_abap_structdescr=>describe_by_data( <fs_line> ).

comp_tab = stru_ref->components.

REFRESH zjncranges.

MOVE rangename TO zjnconerange-name.

APPEND zjnconerange TO zjncranges.

CALL METHOD spreadsheetintf->get_ranges_data

IMPORTING

contents = zjnccontents

error = zjncerror

retcode = zjncretcode

CHANGING

ranges = zjncranges.

MOVE 0 TO prevrow.

LOOP AT zjnccontents INTO zjnconeitem.

MOVE zjnconeitem-row TO nrow.

IF nrow prevrow.

IF prevrow 0.

APPEND <fs_line> TO <fs_table>.

ENDIF.

CLEAR <fs_line>.

MOVE nrow TO prevrow.

ENDIF.

MOVE zjnconeitem-column TO ncolumn.

READ TABLE comp_tab INDEX ncolumn INTO one_comp.

CONCATENATE '(' sy-cprog ')' irecname '-' one_comp-name INTO one_name.

ASSIGN (one_name) TO <fs_type>.

IF one_comp-type_kind 'D'.

MOVE zjnconeitem-value TO <fs_type>.

ELSE.

TRANSLATE zjnconeitem-value TO UPPER CASE.

CALL FUNCTION 'CONVERSION_EXIT_SDATE_INPUT'

EXPORTING

input = zjnconeitem-value

IMPORTING

output = mydate.

MOVE mydate TO <fs_type>.

ENDIF.

ENDLOOP.

IF prevrow 0.

APPEND <fs_line> TO <fs_table>.

ENDIF.

ENDFUNCTION.

SAP has a facility called BDS.

Read

http://www.intelligententerprise.com/channels/applications/feature/archive/schulze.jhtml

Read http://www.sappro.com/download03.cfm?session=

There is ready code of BDS+DOI -- uses CL_BDS_DOCUMENT_SET global class.

If you wish to store a Word/Excel/AutoCad or any other document in SAP,

then you can use the Business Document Service (BDS). I did not - as

Cluster Data Directory is a simple beginning ...

<REMOVED BY MODERATOR>.... and come bak if stil have trouble.

Edited by: Alvaro Tejada Galindo on Apr 7, 2008 4:51 PM

Re: Moving excel data to R/3 table fields

Former Member — Mon, 07 Apr 2008 12:06:00 GMT

Hi,

Donwload first the excel file into internal table by using the following code and correct what you have to correct.

Don't forget the table strcuture must be the same as your excel

TYPE-POOLS: truxs, SLIS.

PARAMETERS: p_file TYPE rlgrap-filename default 'C:\Documents and Settings\Testuser\My Documents\partic_results.xls'.

DATA: lw_partic_results TYPE LINE OF zlsotac_results,

partic_results LIKE zlsotac_results OCCURS 0,

gt_header TYPE TABLE OF slis_listheader WITH HEADER LINE.

DATA: it_raw TYPE truxs_t_text_data.

At selection screen

*AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'
EXPORTING
field_name = 'P_FILE'
IMPORTING
file_name = p_file.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = ' '

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = partic_results[] "ACTUAL DATA

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.

topic Re: Moving excel data to R/3 table fields in Application Development and Automation Discussions

Moving excel data to R/3 table fields

Re: Moving excel data to R/3 table fields

Re: Moving excel data to R/3 table fields