Z Table Update

Former Member · ‎2009 Feb 02

Hello friends,

I have created a new Z Table and know I want to update this from an Excel file which is on my desktop.

Please provide me some sample code for this.

thanq

Former Member · ‎2009 Feb 02

Hi Vamshi,

Hope the below snippet helps you.


data : t_cska like cska occurs 0 with header line,
       wa like line of t_cska.

data : it_excel type alsmex_tabline occurs 0 with header line.

selection-screen begin of block b1 with frame title text-001.
parameter: f_name type rlgrap-filename default 'C:\Documents and Settings\Administrator\Desktop\Project_data\Cost_element_cska.xls'.
parameter : p_begcol type i default 1 no-display,
            p_begrow type i default 2 no-display,
            p_endcol type i default 8 no-display,
            p_endrow type i default 46 no-display.

selection-screen end of block b1.

at selection-screen on value-request for f_name.
perform f_get_file using f_name.

start-of-selection.
perform f_xls_itab using f_name changing it_excel.
perform f_move_data.

*----------------------------------------------------------------------*
form f_get_file  using    p_file_nam.

call function 'KD_GET_FILENAME_ON_F4'
 exporting
   program_name        = syst-repid
   dynpro_number       = syst-dynnr
*   FIELD_NAME          = ' '
*   STATIC              = ' '
*   MASK                = ' '
  changing
    file_name           = f_name
 exceptions
   mask_too_long       = 1
   others              = 2.
endform.                    " f_get_file


form f_xls_itab  using    p_file_nam changing p_it_excel.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  exporting
    filename                      = f_name
    i_begin_col                   = p_begcol
    i_begin_row                   = p_begrow
    i_end_col                     = p_endcol
    i_end_row                     = p_endrow
  tables
    intern                        = it_excel
 exceptions
   inconsistent_parameters       = 1
   upload_ole                    = 2
   others                        = 3.
endform.                    " f_xls_itab


form f_move_data.

data : lv_index type i.
field-symbols <fs>.

* Sorting the internal table
sort it_excel by row col.
clear it_excel.

loop at it_excel.
  move it_excel-col to lv_index.
* Assigning each record to the internal table row.
  assign component lv_index of structure wa to <fs>.

* Assigning the field value to a field symbol
  move it_excel-value to <fs>.

  at end of row.
  append wa to t_cska.
  clear wa.
  endat.

endloop.
endform.                    " f_move_data

once you have uploaded records from excel into tab then it is your wishe you can run an open sql query or a bdc to upload to your Ztable.

try this and let me know if you have concerns.

Regards,

Ranjith N

Former Member · ‎2009 Feb 02

Hi,

Check this one,

tables : zp1041_ns_dealer, kna1.

data : zcust like zp1041_ns_dealer occurs 0 with header line.

data : begin of data_stru occurs 0,

zzcustid(10),

zzdoctyp(50),

zzsoff(50),

cgrp(1),

zzpsno(80),

end of data_stru.

data : err_text(200),

v_filename type string.

************************************************************************

*******Selection screen ***********************************************

************************************************************************

**Added for tickets no: 1000017981

selection-screen begin of block b1 with frame title text-001.

parameters: p_fpath type ibipparms-path .

selection-screen end of block b1.

************************************************************************

Selecting the file from presentation server****************

************************************************************************

**Added for tickets no: 1000017981

at selection-screen on value-request for p_fpath.

call function 'KD_GET_FILENAME_ON_F4'

exporting

program_name = syst-repid

dynpro_number = syst-dynnr

FIELD_NAME = ' '
STATIC = ' '

mask = ',TXT,*.txt,'

changing

file_name = p_fpath

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

start-of-selection.

clear : data_stru.

************************************************************************

*" Upload data from Text File

************************************************************************

perform fetch_data.

************************************************************************

*" Convert Customer code and document types to upper case

************************************************************************

loop at data_stru.

translate data_stru-zzcustid to upper case.

translate data_stru-zzdoctyp to upper case.

endloop.

loop at data_stru.

select single * from kna1

where kunnr = data_stru-zzcustid.

if sy-subrc = 0.

select single * from ZP1041_NS_DEALER
where ZZCUSTID = data_stru-ZZCUSTID.
if sy-subrc <> 0.

move-corresponding data_stru to zcust.

zcust-mandt = sy-mandt. "tickets no: 1000017981

zcust-zzcustgrp = 'N'.

append zcust.

clear zcust. "tickets no: 1000017981

endif.

endif.

endloop.

*break-point. "Tickets no: 1000017981

************************************************************************

******Uploading the data to Ztable************************************

************************************************************************

if not zcust[] is initial. "tickets no: 1000017981

modify zp1041_ns_dealer from table zcust.

if sy-subrc = 0.

commit work.

write / text-003.

loop at zcust.

write / zcust-zzcustid.

endloop.

endif.

else.

message text-004 type 'I'.

endif. "tickets no: 1000017981

&----

*& Form FETCH_DATA

&----

Uploading the data from file to internal table using GUI_UPLOAD

----

--> p1 text
<-- p2 text

----

form fetch_data.

"Commented for tickets no: 1000017981

*CALL FUNCTION 'UPLOAD'

TABLES
DATA_TAB = data_stru
EXCEPTIONS
CONVERSION_ERROR = 1
INVALID_TABLE_WIDTH = 2
INVALID_TYPE = 3
NO_BATCH = 4
UNKNOWN_ERROR = 5
GUI_REFUSE_FILETRANSFER = 6
OTHERS = 7
.

*IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

*ENDIF.

*********************Added for tickets no: 1000017981

v_filename = p_fpath.

call function 'GUI_UPLOAD'

exporting

filename = v_filename

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 = data_stru

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.

endform. " FETCH_DATA

Former Member · ‎2009 Feb 02

Hi,

Kindly go through this sample code below:

&----

*& SELECTION SCREEN F4 HELP

&----

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_path.

CALL FUNCTION 'F4_FILENAME'

EXPORTING
PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '

IMPORTING

file_name = p_path

.

&----

FUNCTION MODULE CALLED TO UPLOAD THE XLS FILE INTO INTERNAL TABLE

&----

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = p_path

TABLES

i_tab_converted_data = it_final "internal table filled with excel sheet contents

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.

READ TABLE it_final INTO wa_final WITH KEY bukrs = p_code.

IF sy-subrc <> 0.

MESSAGE i000 WITH 'this company code does not exists in the flat file uploaded!'.

ENDIF.

*>en you can modify you table from this internal table filled above through modify statement. *

Hope it helps you

Regrds

Mansi

Former Member · ‎2009 Feb 02

hi,

Use ws_upload or gui_upload to get your data from excel sheet .

Get this data into your internal table . create your own types for that.

Then loop on that table & insert the data into your z-table using INSERT statement.

Regards

Mudit

Former Member · ‎2009 Feb 02

Hi Mansi,

can u please brief how u have used txt conversion function module.cos i am not getting what to pass in i_tab_raw_data nad i_tab_converted_data?

Regards,

Rahul

Former Member · ‎2009 Feb 03

Hi,

Simply you need to use one FM that upload the data from the excel sheet to the internal table, that FM is 'TEXT_CONVERT_XLS_TO_SAP' as GUI_UPLOAD FM won't allow you upload the data from excel sheet.

Parameters to be paased

i_tab_raw_data = it_raw "that is of type truxs_t_text_data

i_filename = p_file "should be with extension .xls

TABLES

i_tab_converted_data = itab[] "internal table having same structure as the db table

PS: include TRUXS as TYPE-POOLS

After reading the data into the intrenal table, just add the loop at intrenal table and read the data to the ztable.

Hope this will solve your problem now.

Pooja

Former Member · ‎2009 Feb 03

Hi

You can use this function module


TYPE-POOLS: truxs.
 
DATA: it_raw TYPE truxs_t_text_data.
PARAMETERS: p_file TYPE rlgrap-filename OBLIGATORY,



  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*    I_FIELD_SEPERATOR          = 'X'
     i_line_header              = 'X'
      i_tab_raw_data             = it_raw   " type trux_t_text_data.
      i_filename                 = p_file  " file name
    TABLES
      i_tab_converted_data       = it_final[]  "internal table name
   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.

Hope this will help you.

Thanks

Arun Kayal

Former Member · ‎2009 Feb 03

Hi,

report zexcel_upload.

type-pools truxs.

tables : zscarr.

parameter p_file type rlgrap-filename default 'C:\Documents and Settings\vk49783\Desktop\Dump\excel.xls'.

types: begin of t_tab,

carrid type zscarr-carrid,

seats type zscarr-total_seats,

end of t_tab.

data :

t_upload type standard table of t_tab,

wa_upload type t_tab,

it_type type truxs_t_text_data.

at selection-screen on value-request for p_file.

call function 'F4_FILENAME'

exporting

PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR

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_type

i_filename = p_file

tables

i_tab_converted_data = t_upload[]

EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2

.

if sy-subrc ne 0.

message id sy-msgid

type sy-msgty

number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

endif.

end-of-selection.

loop at t_upload into wa_upload.

zscarr-carrid = wa_upload-carrid.

zscarr-total_seats = wa_upload-seats.

modify zscarr.

endloop.

write : 'Data Inserted in the table zscarr.Check the status.'.

Regards

Md.MahaboobKhan

Former Member · ‎2009 Feb 09

Hi Vamshi,

Try doing this ....


parameters: p_file type ibipparms-path.
data: w_filename type ibipparms-path.

*to selct file by giving path in the parameter*
CALL FUNCTION 'F4_FILENAME' 
* EXPORTING
*   PROGRAM_NAME        = SYST-CPROG
*   DYNPRO_NUMBER       = SYST-DYNNR
*   FIELD_NAME          = ' '
 IMPORTING
   FILE_NAME           = w_filename
          .
p_file = w_filename

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
  EXPORTING
*   I_FIELD_SEPERATOR          =
*   I_LINE_HEADER              =
    i_tab_raw_data             =
    i_filename                 =
  tables
    i_tab_converted_data       = i_tab
* 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.

modify ztable from itab.

check sy-subrc.

Regards,

Mdi.Deeba

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Z Table Update