topic Re: Data Update/Upload to Multiple tables in Application Development and Automation Discussions

Data Update/Upload to Multiple tables

Former Member — Mon, 26 Feb 2007 08:18:18 GMT

Hi Experts,

We need to update/upload data into Multiple tables which are related to each

other, and the DATA is to be entered in to the corresponding fields depending upon the relationship between tables.

Req: Updates to the table that contains the job details and also the job steps as well. Need to be consistent

Tables : zjobinfo , zjobrun and zjobstep.

These three tables have multiple fields.

Here Jobname is one of the Key fields in these tables.

Around 2000 records are to be updated/uploaded.

Kindly let know how to proceed.

Regards,

Ajaz

Re: Data Update/Upload to Multiple tables

Former Member — Mon, 26 Feb 2007 08:28:58 GMT

R u uploading data from flat file?

If u want to upload data from flat file then use GUI_UPLOAD fm.

Declare internal table with all fields (fields from 3 ztables).

upload the date using fm.

loop the internal table and pass the values to relevant table and update the ztables.

Re: Data Update/Upload to Multiple tables

Former Member — Mon, 26 Feb 2007 08:34:01 GMT

Hi,

you can look into the following code,

in the selection screen, enter the table name(any table for uploading)

and in the next, give the file path.

the data will be uploaded into the respective table, change it, if neseccasery as per your requirement.

TYPE-POOLS: slis.

*--Constants

*--Variables

*--Filed Symbols

FIELD-SYMBOLS <gwa_itab> TYPE ANY.

FIELD-SYMBOLS <gwa_itab1> TYPE ANY.

FIELD-SYMBOLS <gwa_cccc> TYPE c.

FIELD-SYMBOLS <git_itab> TYPE STANDARD TABLE.

FIELD-SYMBOLS <git_itab1> TYPE STANDARD TABLE.

data: git_table like dd03l occurs 0 with header line,

fieldtab TYPE slis_t_fieldcat_alv.

CONSTANTS: lc_tab TYPE string

VALUE cl_abap_char_utilities=>horizontal_tab.

&----

PARAMETERS: p_test as checkbox default 'X'.

PARAMETERS: p_table LIKE rsrd1-tbma_val OBLIGATORY.

SELECTION-SCREEN BEGIN OF BLOCK blk_2 WITH FRAME TITLE text-002.

*-- Block for Download Options

PARAMETERS: p_appser RADIOBUTTON GROUP r1 USER-COMMAND ucom,"Application

p_appli LIKE rlgrap-filename DEFAULT

'/usr/BW/interface/',"Aplication File Name

p_pres RADIOBUTTON GROUP r1 DEFAULT 'X',"Presentation server

p_file type string ,"File name

p_down type string default 'D:\BW Implementation\PS data files\PA\Transaction Table\Final\Error Log for Upload'.

"for presenetation

SELECTION-SCREEN END OF BLOCK blk_2.

&----

START-OF-SELECTION.

END-OF-SELECTION.

*--Get the Table name and structure.

free memory.

PERFORM get_ztable_name.

*--Read the File into internal table and upload

PERFORM get_file_and_upload.

&----

*& Form get_table_name

&----

text

----

--> p1 text
<-- p2 text

----

FORM get_ztable_name .

TYPES: BEGIN OF lty_databuffer,

tabname TYPE tabname,

tabref TYPE REF TO data,

strucref TYPE REF TO data,

END OF lty_databuffer.

STATICS: st_databuffer TYPE HASHED TABLE OF lty_databuffer

WITH UNIQUE KEY tabname.

DATA: l_databuffer TYPE lty_databuffer.

*CLEAR prel_db_tab[].

READ TABLE st_databuffer WITH TABLE KEY tabname = p_table

INTO l_databuffer.

IF NOT sy-subrc IS INITIAL.

l_databuffer-tabname = p_table.

CREATE DATA l_databuffer-tabref TYPE TABLE OF (p_table).

CREATE DATA l_databuffer-strucref TYPE (p_table).

INSERT l_databuffer INTO TABLE st_databuffer.

ENDIF.

ASSIGN l_databuffer-tabref->* TO <git_itab>.

ASSIGN l_databuffer-strucref->* TO <gwa_itab>.

ASSIGN l_databuffer-tabref->* TO <git_itab1>.

ASSIGN l_databuffer-strucref->* TO <gwa_itab1>.

*--Get Number of field in the Table.

SELECT * FROM dd03l INTO CORRESPONDING FIELDS OF TABLE git_table WHERE tabname EQ p_table.

ENDFORM. " get_table_name

&----

*& Form get_file_and_upload

&----

text

----

--> p1 text
<-- p2 text

----

FORM get_file_and_upload .

data: lv_string type string,

gwa_baspartic like ZPS_BASPARTC,

gwa_baspartic1 like ZPS_BASPARTC,

index type i.

IF p_appser = 'X'.

OPEN DATASET p_appli FOR INPUT IN TEXT MODE ENCODING DEFAULT.

DO.

READ DATASET p_appli INTO lv_string.

IF sy-subrc = 0.

if index ge 1.

split lv_string at lc_tab into gwa_baspartic-CLIENT

gwa_baspartic-ZZPS_EMPLID

gwa_baspartic-ZZPS_ENDDA

gwa_baspartic-ZZPS_BEGDA

gwa_baspartic-ZZPS_SCHEID

gwa_baspartic-ZZPS_BENFRCD

gwa_baspartic-ZZPS_EVENID

gwa_baspartic-ZZPS_ADDEFDT

gwa_baspartic-ZZPS_UNINEFDT

gwa_baspartic-ZZPS_UNEFFSQ

gwa_baspartic-ZZPS_EMRCD

gwa_baspartic-ZZPS_JOBEFDT

gwa_baspartic-ZZPS_JOBESEQ

gwa_baspartic-ZZPS_BENFPRG

gwa_baspartic-ZZPS_EVECLS

gwa_baspartic-ZZPS_EVESTS

gwa_baspartic-ZZPS_BASPRSTS

gwa_baspartic-ZZPS_PRSSIND

gwa_baspartic-ZZPS_ADREGCG

gwa_baspartic-ZZPS_UNEGCG

gwa_baspartic-ZZPS_JOBEGCG

gwa_baspartic-ZZPS_BASETDS

gwa_baspartic-ZZPS_BASEVCG

gwa_baspartic-ZZPS_ADELCGDT

gwa_baspartic-ZZPS_UNEGCGDT

gwa_baspartic-ZZPS_JBEGCGDT

gwa_baspartic-ZZPS_BSEDSDT

gwa_baspartic-ZZPS_BSETCGDT

gwa_baspartic-ZZPS_FINZROLL

gwa_baspartic-ZZPS_CRDTROR

gwa_baspartic-ZZPS_ELESORE

gwa_baspartic-ZZPS_EVEMCD

gwa_baspartic-ZZPS_EVENDT

gwa_baspartic-ZZPS_EFSEQ

gwa_baspartic-ZZPS_EVENPRTY

gwa_baspartic-ZZPS_BSACTSR

gwa_baspartic-ZZPS_MUACIN

gwa_baspartic-ZZPS_STATUDT

gwa_baspartic-ZZPS_OPTNYDT

gwa_baspartic-ZZPS_ELERDT

gwa_baspartic-ZZPS_CONTYDT

gwa_baspartic-ZZPS_CRCDT

gwa_baspartic-ZZPS_UNIOCD

gwa_baspartic-ZZPS_SERVDT

gwa_baspartic-ZZPS_COBACTN

gwa_baspartic-ZZPS_ALLSEFSRC

append gwa_baspartic to <git_itab>.

endif.

index = index + 1.

ELSE.

EXIT.

ENDIF.

ENDDO.

CLOSE DATASET p_appli.

ELSEIF p_pres = 'X'.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = p_file

filetype = 'ASC'

has_field_separator = 'X'

HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = ' '
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
CHECK_BOM = ' '
VIRUS_SCAN_PROFILE =
NO_AUTH_CHECK = ' '
IMPORTING
FILELENGTH =
HEADER =

TABLES

data_tab = <git_itab>

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.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

PERFORM create_catalog USING git_table.

IF p_test IS INITIAL.

LOOP AT <git_itab> INTO <gwa_itab>.

INSERT into (p_table) values <gwa_itab>.

IF sy-subrc NE 0.

MOVE-CORRESPONDING <gwa_itab> TO <gwa_itab1>.
APPEND <gwa_itab1> TO <git_itab1>.
CLEAR <gwa_itab1>.

ELSE.

DELETE <git_itab> INDEX sy-tabix.

ENDIF.

commit work.

ENDLOOP.

*insert (p_table) from table <git_itab> accepting duplicate keys.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_INTERFACE_CHECK = ' '
I_BYPASSING_BUFFER = ' '
I_BUFFER_ACTIVE = ' '

i_callback_program = sy-repid

I_CALLBACK_PF_STATUS_SET = ' '
I_CALLBACK_USER_COMMAND = ' '
I_CALLBACK_TOP_OF_PAGE = ' '
I_CALLBACK_HTML_TOP_OF_PAGE = ' '
I_CALLBACK_HTML_END_OF_LIST = ' '
I_STRUCTURE_NAME =
I_BACKGROUND_ID = ' '
I_GRID_TITLE =
I_GRID_SETTINGS =
IS_LAYOUT =

it_fieldcat = fieldtab

IT_EXCLUDING =
IT_SPECIAL_GROUPS =
IT_SORT =
IT_FILTER =
IS_SEL_HIDE =
I_DEFAULT = 'X'
I_SAVE = ' '
IS_VARIANT =
IT_EVENTS =
IT_EVENT_EXIT =
IS_PRINT =
IS_REPREP_ID =
I_SCREEN_START_COLUMN = 0
I_SCREEN_START_LINE = 0
I_SCREEN_END_COLUMN = 0
I_SCREEN_END_LINE = 0
I_HTML_HEIGHT_TOP = 0
I_HTML_HEIGHT_END = 0
IT_ALV_GRAPHICS =
IT_HYPERLINK =
IT_ADD_FIELDCAT =
IT_EXCEPT_QINFO =
IR_SALV_FULLSCREEN_ADAPTER =
IMPORTING
E_EXIT_CAUSED_BY_CALLER =
ES_EXIT_CAUSED_BY_USER =

TABLES

t_outtab = <git_itab>

EXCEPTIONS
PROGRAM_ERROR = 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.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

BIN_FILESIZE =

filename = p_down

FILETYPE = 'ASC'
APPEND = ' '
WRITE_FIELD_SEPARATOR = ' '
HEADER = '00'
TRUNC_TRAILING_BLANKS = ' '
WRITE_LF = 'X'
COL_SELECT = ' '
COL_SELECT_MASK = ' '
DAT_MODE = ' '
CONFIRM_OVERWRITE = ' '
NO_AUTH_CHECK = ' '
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
WRITE_BOM = ' '
TRUNC_TRAILING_BLANKS_EOL = 'X'
WK1_N_FORMAT = ' '
WK1_N_SIZE = ' '
WK1_T_FORMAT = ' '
WK1_T_SIZE = ' '
IMPORTING
FILELENGTH =

tables

data_tab = <git_itab>

FIELDNAMES =
EXCEPTIONS
FILE_WRITE_ERROR = 1
NO_BATCH = 2
GUI_REFUSE_FILETRANSFER = 3
INVALID_TYPE = 4
NO_AUTHORITY = 5
UNKNOWN_ERROR = 6
HEADER_NOT_ALLOWED = 7
SEPARATOR_NOT_ALLOWED = 8
FILESIZE_NOT_ALLOWED = 9
HEADER_TOO_LONG = 10
DP_ERROR_CREATE = 11
DP_ERROR_SEND = 12
DP_ERROR_WRITE = 13
UNKNOWN_DP_ERROR = 14
ACCESS_DENIED = 15
DP_OUT_OF_MEMORY = 16
DISK_FULL = 17
DP_TIMEOUT = 18
FILE_NOT_FOUND = 19
DATAPROVIDER_EXCEPTION = 20
CONTROL_FLUSH_ERROR = 21
OTHERS = 22

IF sy-subrc <> 0.

ENDIF.

ELSE.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_INTERFACE_CHECK = ' '
I_BYPASSING_BUFFER = ' '
I_BUFFER_ACTIVE = ' '

i_callback_program = sy-repid

I_CALLBACK_PF_STATUS_SET = ' '
I_CALLBACK_USER_COMMAND = ' '
I_CALLBACK_TOP_OF_PAGE = ' '
I_CALLBACK_HTML_TOP_OF_PAGE = ' '
I_CALLBACK_HTML_END_OF_LIST = ' '
I_STRUCTURE_NAME =
I_BACKGROUND_ID = ' '
I_GRID_TITLE =
I_GRID_SETTINGS =
IS_LAYOUT =

it_fieldcat = fieldtab

IT_EXCLUDING =
IT_SPECIAL_GROUPS =
IT_SORT =
IT_FILTER =
IS_SEL_HIDE =
I_DEFAULT = 'X'
I_SAVE = ' '
IS_VARIANT =
IT_EVENTS =
IT_EVENT_EXIT =
IS_PRINT =
IS_REPREP_ID =
I_SCREEN_START_COLUMN = 0
I_SCREEN_START_LINE = 0
I_SCREEN_END_COLUMN = 0
I_SCREEN_END_LINE = 0
I_HTML_HEIGHT_TOP = 0
I_HTML_HEIGHT_END = 0
IT_ALV_GRAPHICS =
IT_HYPERLINK =
IT_ADD_FIELDCAT =
IT_EXCEPT_QINFO =
IR_SALV_FULLSCREEN_ADAPTER =
IMPORTING
E_EXIT_CAUSED_BY_CALLER =
ES_EXIT_CAUSED_BY_USER =

TABLES

t_outtab = <git_itab>

EXCEPTIONS
PROGRAM_ERROR = 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. " get_file_and_upload

&----

*& Form create_catalog

&----

text

----

-->P_GIT_TABLE text

----

FORM create_catalog USING p_git_table.

LOOP AT git_table .

PERFORM create_field_catalog USING git_table-fieldname

p_table.

ENDLOOP.

ENDFORM. " create_catalog

&----

*& Form create_field_catalog

&----

text

----

-->P_GWA_TABLE_FIELDNAME text
-->P_P_TABLE text

----

FORM create_field_catalog USING p_gwa_table_fieldname

p_p_table.

DATA: l_fieldcat TYPE slis_fieldcat_alv.

IF p_test EQ 'X'.

CLEAR l_fieldcat.

l_fieldcat-fieldname = p_gwa_table_fieldname.

l_fieldcat-tabname = 'GIT_TAB'.

l_fieldcat-no_out = ' '.

l_fieldcat-seltext_l = p_gwa_table_fieldname.

l_fieldcat-outputlen = 21.

APPEND l_fieldcat TO fieldtab.

ELSE.

CLEAR l_fieldcat.

l_fieldcat-fieldname = p_gwa_table_fieldname.

l_fieldcat-tabname = 'GIT_TAB'.

l_fieldcat-no_out = ' '.

l_fieldcat-seltext_l = p_gwa_table_fieldname.

l_fieldcat-outputlen = 21.

APPEND l_fieldcat TO fieldtab.

ENDIF.

ENDFORM. " create_field_catalog

Re: Data Update/Upload to Multiple tables

Former Member — Mon, 26 Feb 2007 09:02:11 GMT

Hi,

Thanks for the reply,

I have a Single Excel Sheet with the data for the corresponding fields of the 3 Tables.

The upload is from the Presentation Server.

Regards

Ajaz

Re: Data Update/Upload to Multiple tables

Former Member — Mon, 26 Feb 2007 09:54:23 GMT

ok, in this case, convert your excel sheet into txt format,

in excel you have option -SAVE AS - there you save it as text delimited format,

and use the code below to upload thr file.

It works

Cheers

Re: Data Update/Upload to Multiple tables

Former Member — Mon, 26 Feb 2007 10:12:53 GMT

Hi Ajay,

Thanks again for the reply.

I converted the Excel file content to a txt file for a single table, to test the upload.

is it mandatory that all fields of the table are in the input file?

I am getting an exceptional error..

An exception occurred. This exception is dealt with in more detail below

. The exception, which is assigned to the class 'CX_SY_DYN_CALL_ILLEGAL_TYPE',

was neither

caught nor passed along using a RAISING clause, in the procedure

"GET_FILE_AND_UPLOAD" "(FORM)"

Since the caller of the procedure could not have expected this exception

to occur, the running program was terminated.

The reason for the exception is:

The call to the function module "GUI_UPLOAD" is incorrect:

The function module interface allows you to specify only

fields of a particular type under "FILENAME".

The field "P_FILE" specified here is a different

field type.

Kindly let know what could be the reason for the exception....

Regards,

Ajaz