The program below will copy the data down to a text or binary file and will then read that file to reload the table again. Obviously there are a few caveats;
- Table structures between the two systems must be completely identical.
- Default parameters for things like date formats need to be identical between the two systems.
This is an improvement of a previous program I wrote as this one will handle GUIDs and text in foreign languages as well provided you use the binary format.
It's possible to limit the data that gets downloaded by using the Data selection button after you've selected the table and field names. This will dynamically build a new selection screen where you can select values to limit your data selection. You can also automatically split the file at a predefined record count if you're going to be working with really huge files.
When you're uploading you can decide if you want to skip existing records with the same key fields or simply overwrite them.
*&---------------------------------------------------------------------* *& Report ZZDATA_TRANSFER *&---------------------------------------------------------------------* *& Use this program to transfer data between systems. *& If you have data like unicode text that might get scrambled then *& use binary mode to transfer the data. *& Mark Langenhoven 2014 - 2017 *&---------------------------------------------------------------------* report zzdata_transfer. *--- Data declarations ------------------------------------------------ tables: dd03l. type-pools:rsds. data: begin of finalrec, fieldname like dd03l-fieldname, desc like dd04t-ddtext, end of finalrec. data: it_final like finalrec occurs 0 with header line, lt_dd03l like dd03l occurs 0 with header line, ls_dd03l like dd03l, ls_dd04t like dd04t, it_return like ddshretval occurs 0 with header line. data: gv_where type rsds_twhere. *--- Selection screen ------------------------------------------------- "Up or down direction selection-screen begin of block dir with frame title dir. selection-screen begin of line. parameters p_down radiobutton group grp1 default 'X' user-command dib. selection-screen comment 3(30) pdown. selection-screen end of line. selection-screen begin of line. parameters p_up radiobutton group grp1. selection-screen comment 3(30) pup. selection-screen end of line. selection-screen end of block dir. "ASCII or Binary file type selection-screen begin of block typ with frame title typ. selection-screen begin of line. parameters p_asc radiobutton group grp2 default 'X' user-command asc. selection-screen comment 3(15) pasc. selection-screen end of line. selection-screen begin of line. parameters p_bin radiobutton group grp2. selection-screen comment 3(15) pbin. selection-screen end of line. selection-screen end of block typ. "File name selection-screen begin of block fil with frame title fil. selection-screen begin of line. selection-screen comment 1(15) pfile. parameters p_file like rlgrap-filename. selection-screen end of line. "Add a header line to the output selection-screen begin of line. parameters p_head as checkbox default 'X'. selection-screen comment 3(20) phead. selection-screen end of line. "Split file at NNN number of records selection-screen begin of line. selection-screen comment 1(25) pbrk. parameters p_brk type i default '50000'. selection-screen end of line. selection-screen end of block fil. "Table and field names selection-screen begin of block tab with frame title tab. selection-screen begin of line. selection-screen comment 1(18) ptab. parameters p_tab like dd03l-tabname. selection-screen end of line. selection-screen begin of line. selection-screen comment 1(15) pfield. select-options: s_field for dd03l-fieldname no intervals. selection-screen end of line. selection-screen: pushbutton 1(18) p_btn user-command clk. selection-screen end of block tab. "Skip or overwrite uploaded data selection-screen begin of block upd with frame title upd. selection-screen begin of line. parameters p_skip radiobutton group upg default 'X'. selection-screen comment 3(28) pskip. selection-screen end of line. selection-screen begin of line. parameters p_over radiobutton group upg. selection-screen comment 3(28) pover. selection-screen end of line. selection-screen end of block upd. initialization. pdown = 'Transfer data down to a file'. pup = 'Transfer data up from a file'. pasc = 'ASCII text file'. pbin = 'Binary file'. pfile = 'File name'. ptab = 'Table name'. pfield = 'Field selection'. p_btn = 'Data selection'. pskip = 'Skip existing records'. pover = 'Overwrite existing records'. pbrk = 'Break files into records'. phead = 'Header line'. dir = 'Direction'. typ = 'File type'. fil = 'File information'. tab = 'Table information'. upd = 'Update method'. "Grab any fieldnames the user wants to select by at selection-screen on value-request for s_field-low. "Build a custom search help because the dropdown on dd03l is terrible refresh it_final. select tabname fieldname keyflag rollname from dd03l into corresponding fields of table lt_dd03l where tabname = p_tab order by position. "Ignore includes delete lt_dd03l where fieldname cs '.INCL'. loop at lt_dd03l. clear it_final. it_final-fieldname = lt_dd03l-fieldname. select single ddtext from dd04t into it_final-desc where rollname = lt_dd03l-rollname and ddlanguage = sy-langu. append it_final. endloop. call function 'F4IF_INT_TABLE_VALUE_REQUEST' exporting retfield = 'FIELDNAME' value_org = 'S' tables value_tab = it_final return_tab = it_return exceptions parameter_error = 1 no_values_found = 2 others = 3. if sy-subrc <> 0. * Implement suitable error handling here endif. loop at it_return. s_field-low = it_return-fieldval. s_field-sign = 'I'. s_field-option = 'EQ'. append s_field. endloop. sort s_field. delete adjacent duplicates from s_field. at selection-screen on value-request for p_file. "Pop up a file selector for the user to choose a file data: objfile type ref to cl_gui_frontend_services. data: lv_rc type i, ls_files type line of filetable, lt_files type filetable. check sy-batch is initial. create object objfile. call method cl_gui_frontend_services=>file_open_dialog exporting window_title = 'Select file' default_extension = 'TXT' changing file_table = lt_files rc = lv_rc exceptions file_open_dialog_failed = 1 cntl_error = 2 error_no_gui = 3 not_supported_by_gui = 4 others = 5. check sy-subrc = 0. read table lt_files into ls_files index 1. check sy-subrc = 0. p_file = ls_files-filename. at selection-screen. if sy-ucomm = 'CLK'. if s_field[] is initial. message s000(38) with 'Select fields first'. exit. endif. perform restrict_data. endif. "Check if everything is filled prior to running if sy-ucomm = 'ONLI'. if p_file is initial. message e000(38) with 'Supply the filename'. exit. endif. if p_tab is initial. message e000(38) with 'Supply the table name'. exit. endif. select single * from dd03l into ls_dd03l where tabname = p_tab. if sy-subrc <> 0. message e000(38) with 'Invalid table name' p_tab. exit. endif. endif. at selection-screen output. if p_up = abap_true. "Show the upload parameters loop at screen. if screen-name cs 'OVER' or screen-name cs 'SKIP'. screen-invisible = 0. modify screen. endif. if screen-name cs 'BTN' or screen-name cs 'FIELD' or screen-name cs 'BRK'. screen-invisible = 1. screen-input = 0. modify screen. endif. endloop. else. "Hide the upload parameters loop at screen. if screen-name cs 'OVER' or screen-name cs 'SKIP'. screen-invisible = 1. modify screen. endif. if screen-name cs 'BTN' or screen-name cs 'FIELD' or screen-name cs 'BRK'. screen-invisible = 0. modify screen. endif. endloop. endif. "No header record allowed on binary files if p_bin = abap_true. loop at screen. if screen-name cs 'HEAD'. screen-active = 0. modify screen. endif. endloop. endif. "Needed because SAP messes around with our stuff after the fact sort s_field. delete adjacent duplicates from s_field. *=== Main program ===================================================== start-of-selection. if p_down = abap_true. perform download. else. if p_tab(1) <> 'Z'. message e000(38) with 'Only Z tables are allowed'. exit. endif. Build your authorization check here. perform upload. endif. *&---------------------------------------------------------------------* *& Form RESTRICT_DATA *&---------------------------------------------------------------------* * Use the user's selection to restrict which data will be downloaded *----------------------------------------------------------------------* form restrict_data. data: lv_title like sy-title, lv_expr type rsds_texpr, lt_tables like rsdstabs occurs 0 with header line, lt_fields like rsdsfields occurs 0 with header line, lv_selid like rsdynsel-selid, lv_actnum like sy-tfill. refresh: lt_tables, lt_fields. clear: gv_where. lt_tables-prim_tab = p_tab. append lt_tables. loop at s_field. lt_fields-tablename = p_tab. lt_fields-fieldname = s_field-low. append lt_fields. endloop. call function 'FREE_SELECTIONS_INIT' exporting kind = 'T' expressions = lv_expr importing selection_id = lv_selid number_of_active_fields = lv_actnum tables tables_tab = lt_tables fields_tab = lt_fields exceptions fields_incomplete = 1 fields_no_join = 2 field_not_found = 3 no_tables = 4 table_not_found = 5 expression_not_supported = 6 incorrect_expression = 7 illegal_kind = 8 area_not_found = 9 inconsistent_area = 10 kind_f_no_fields_left = 11 kind_f_no_fields = 12 too_many_fields = 13 dup_field = 14 field_no_type = 15 field_ill_type = 16 dup_event_field = 17 node_not_in_ldb = 18 area_no_field = 19 others = 20. if sy-subrc <> 0. * Implement suitable error handling here endif. concatenate 'Restrict data selections for' p_tab into lv_title separated by space. call function 'FREE_SELECTIONS_DIALOG' exporting selection_id = lv_selid title = lv_title tree_visible = ' ' importing where_clauses = gv_where number_of_active_fields = lv_actnum tables fields_tab = lt_fields exceptions internal_error = 1 no_action = 2 selid_not_found = 3 illegal_status = 4 others = 5. if sy-subrc <> 0. clear: gv_where. endif. endform. *&---------------------------------------------------------------------* *& Form DOWNLOAD *&---------------------------------------------------------------------* * Download data to a file *----------------------------------------------------------------------* form download. data: lv_file type string, lv_ftype type char10, lv_sep type char01. data: ls_where type rsds_where, lt_wheretab type rsds_where_tab, ls_wheretab type rsdswhere, lv_where type string. data: begin of namerec, fieldname like dd03l-fieldname, end of namerec. data: lt_dyntab type ref to data, lt_dyntab2 type ref to data, ls_dynwa type ref to data, lt_cat type table of lvc_s_fcat, lt_fields like dd03l occurs 0 with header line, lt_fieldnames like namerec occurs 0 with header line, wa_fields like dd03l, wa_cat type lvc_s_fcat, lv_cnt type i, lv_sel type i, lv_tabix type i, lv_filecnt(3) type n. field-symbols: <table> type table, <load> type table, <wa>. select * from dd03l into table lt_fields where tabname = p_tab. sort lt_fields by position. delete lt_fields where fieldname cp '.INCLU*'. loop at lt_fields into wa_fields. lv_cnt = lv_cnt + 1. wa_cat-tabname = p_tab. wa_cat-fieldname = wa_fields-fieldname. wa_cat-col_pos = lv_cnt. wa_cat-inttype = wa_fields-inttype. wa_cat-datatype = wa_fields-datatype. wa_cat-intlen = wa_fields-intlen. wa_cat-seltext = wa_fields-fieldname. wa_cat-decimals = wa_fields-decimals. wa_cat-ref_field = wa_fields-fieldname. wa_cat-ref_table = p_tab. append wa_cat to lt_cat. clear wa_cat. endloop. call method cl_alv_table_create=>create_dynamic_table exporting it_fieldcatalog = lt_cat importing ep_table = lt_dyntab exceptions generate_subpool_dir_full = 1 others = 2. if sy-subrc <> 0. message e000(38) with 'Error creating dynamic table'. exit. endif. assign lt_dyntab->* to <table>. call method cl_alv_table_create=>create_dynamic_table exporting it_fieldcatalog = lt_cat importing ep_table = lt_dyntab2 exceptions generate_subpool_dir_full = 1 others = 2. if sy-subrc <> 0. message e000(38) with 'Error creating dynamic table'. exit. endif. assign lt_dyntab2->* to <load>. "Create a work area for us to loop through create data ls_dynwa like line of <table>. assign ls_dynwa->* to <wa>. "Select the actual data to download if gv_where is not initial. read table gv_where into ls_where index 1. lt_wheretab = ls_where-where_tab. if lt_wheretab[] is not initial. clear lv_where. loop at lt_wheretab into ls_wheretab. concatenate lv_where ls_wheretab-line into lv_where separated by space. endloop. select * from (p_tab) into table <table> where (lv_where). endif. else. select * from (p_tab) into table <table>. endif. describe table <table>. if sy-tfill > 0. write: / 'Selected ', sy-tfill, 'records'. else. write: / 'No data selected'. exit. endif. lv_sel = sy-tfill. lv_filecnt = 1. loop at <table> into <wa>. lv_tabix = sy-tabix. append <wa> to <load>. check lv_tabix mod p_brk = 0 or lv_tabix = lv_sel. if lv_sel > p_brk and p_brk > 0. perform build_filename using lv_filecnt changing lv_file. lv_filecnt = lv_filecnt + 1. else. lv_file = p_file. endif. if p_asc = abap_true. lv_ftype = 'ASC'. lv_sep = 'X'. else. lv_ftype = 'BIN'. clear: lv_sep. endif. refresh lt_fieldnames. if p_head = abap_true and p_asc = abap_true. loop at lt_fields. lt_fieldnames = lt_fields-fieldname. append lt_fieldnames. endloop. endif. call function 'GUI_DOWNLOAD' exporting filename = lv_file filetype = lv_ftype write_field_separator = lv_sep tables data_tab = <load> fieldnames = lt_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. write: / 'Error creating file', sy-subrc. else. write: / 'Downloaded file:', lv_file. endif. refresh <load>. endloop. endform. *&---------------------------------------------------------------------* *& Form UPLOAD *&---------------------------------------------------------------------* * Upload the data from a file *----------------------------------------------------------------------* form upload. data: lv_file type string, lv_ftype type char10, lv_sep type char01, lv_rc type i. data: lt_dyntab type ref to data, ls_dynwa type ref to data, ls_dynwa2 type ref to data, lt_cat type table of lvc_s_fcat, lt_fields like dd03l occurs 0 with header line, wa_fields like dd03l, wa_cat type lvc_s_fcat, lv_cnt type i, lv_mandt, lv_where type string, lv_tmp type string. field-symbols: <table> type table, <wa>, <field>, <skip>. lv_file = p_file. if p_asc = abap_true. lv_ftype = 'ASC'. lv_sep = 'X'. else. lv_ftype = 'BIN'. clear lv_sep. endif. select * from dd03l into table lt_fields where tabname = p_tab. sort lt_fields by position. delete lt_fields where fieldname cp '.INCLU*'. loop at lt_fields into wa_fields. lv_cnt = lv_cnt + 1. wa_cat-tabname = p_tab. wa_cat-fieldname = wa_fields-fieldname. wa_cat-col_pos = lv_cnt. wa_cat-inttype = wa_fields-inttype. wa_cat-datatype = wa_fields-datatype. wa_cat-intlen = wa_fields-intlen. wa_cat-seltext = wa_fields-fieldname. wa_cat-decimals = wa_fields-decimals. wa_cat-ref_field = wa_fields-fieldname. wa_cat-ref_table = p_tab. append wa_cat to lt_cat. clear wa_cat. endloop. call method cl_alv_table_create=>create_dynamic_table exporting it_fieldcatalog = lt_cat importing ep_table = lt_dyntab exceptions generate_subpool_dir_full = 1 others = 2. if sy-subrc <> 0. message e000(38) with 'Error creating dynamic table'. exit. endif. assign lt_dyntab->* to <table>. "Create a work area for us to loop through create data ls_dynwa like line of <table>. assign ls_dynwa->* to <wa>. create data ls_dynwa2 like line of <table>. assign ls_dynwa2->* to <skip>. if p_asc = abap_true and p_head = abap_true. perform remove_header changing lv_file. endif. call function 'GUI_UPLOAD' exporting filename = lv_file filetype = lv_ftype has_field_separator = lv_sep tables data_tab = <table> 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. write: / 'Upload file error', sy-subrc. exit. endif. "Delete the temporary file if p_asc = abap_true and p_head = abap_true. call method cl_gui_frontend_services=>file_delete exporting filename = lv_file changing rc = lv_rc exceptions file_delete_failed = 1 cntl_error = 2 error_no_gui = 3 file_not_found = 4 access_denied = 5 unknown_error = 6 not_supported_by_gui = 7 wrong_parameter = 8 others = 9. if sy-subrc <> 0. * Implement suitable error handling here endif. endif. read table lt_fields with key fieldname = 'MANDT'. if sy-subrc = 0. lv_mandt = abap_true. else. lv_mandt = abap_false. endif. clear lv_cnt. loop at <table> into <wa>. "Map to the current client if lv_mandt = abap_true. assign component 'MANDT' of structure <wa> to <field>. <field> = sy-mandt. endif. "See if we want to skip existing records if p_skip = abap_true. "Build up a dynamic where clause to select with clear lv_where. loop at lt_fields where keyflag = abap_true. check lt_fields-fieldname <> 'MANDT'. assign component lt_fields-fieldname of structure <wa> to <field>. concatenate '''' <field> '''' into lv_tmp. concatenate lv_where 'AND' lt_fields-fieldname '=' lv_tmp into lv_where separated by space. endloop. "Remove the unneeded AND shift lv_where left by 4 places. select single * from (p_tab) into <skip> where (lv_where). if sy-subrc = 0. continue. endif. endif. modify (p_tab) from <wa>. lv_cnt = lv_cnt + 1. endloop. commit work. write: / 'Updated', lv_cnt , 'records'. endform. *&---------------------------------------------------------------------* *& Form BUILD_FILENAME *&---------------------------------------------------------------------* * Build up a filename for the file split *----------------------------------------------------------------------* form build_filename using pv_cnt changing pv_file. data: lv_1 type string, lv_2 type string. pv_file = p_file. split pv_file at '.' into lv_1 lv_2. concatenate lv_1 pv_cnt '.' lv_2 into pv_file. endform. *&---------------------------------------------------------------------* *& Form REMOVE_HEADER *&---------------------------------------------------------------------* * Remove the header record from the file *----------------------------------------------------------------------* form remove_header changing pv_file. data: lt_dummy type table of string, lt_path type string occurs 0 with header line. call method cl_gui_frontend_services=>gui_upload exporting filename = pv_file changing data_tab = lt_dummy 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 not_supported_by_gui = 17 error_no_gui = 18 others = 19. if sy-subrc <> 0. * Implement suitable error handling here endif. check lt_dummy is not initial. delete lt_dummy index 1. split pv_file at '\' into table lt_path. describe table lt_path. delete lt_path index sy-tfill. clear pv_file. loop at lt_path. concatenate pv_file '\' lt_path into pv_file. endloop. shift pv_file left. concatenate pv_file '\tempheader.txt' into pv_file. call method cl_gui_frontend_services=>gui_download exporting filename = pv_file changing data_tab = lt_dummy 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 not_supported_by_gui = 22 error_no_gui = 23 others = 24. if sy-subrc <> 0. * Implement suitable error handling here endif. endform.
No comments:
Post a Comment