09-19-2006 6:23 PM
Hello Friends,
Are there any FM to read data from an Excel File to an Internal table.
I have a customized Z_Table and i have some data releated to those fields in Excel file. have to Update the table from the excel sheet.
Any advice,
Shejal Shetty.
09-19-2006 6:27 PM
http://www.sapdevelopment.co.uk/file/file_upexcel.htm
here they used TEXT_CONVERT_XLS_TO_SAP function module to fetch values directly from the EXCEL file to your internal table.
check this link.very useful one
here is the small piece of code for the same FM.
REPORT zupload_excel_to_itab.
TYPE-POOLS: truxs.
PARAMETERS: p_file TYPE rlgrap-filename.
TYPES: BEGIN OF t_datatab,
col1(30) TYPE c,
col2(30) TYPE c,
col3(30) TYPE c,
END OF t_datatab.
DATA: it_datatab type standard table of t_datatab,
wa_datatab type t_datatab.
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.
START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
* I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_raw " WORK TABLE
i_filename = p_file
TABLES
i_tab_converted_data = it_datatab[] "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.
***********************************************************************
* END-OF-SELECTION.
END-OF-SELECTION.
LOOP AT it_datatab INTO wa_datatab.
WRITE:/ wa_datatab-col1,
wa_datatab-col2,
wa_datatab-col3.
ENDLOOP.
Message was edited by: Srikanth Kidambi
09-19-2006 6:24 PM
09-19-2006 6:25 PM
Use the FM <b>GUI_UPLOAD</b>
Check the below code:
REPORT z_upload_muncpcode.
PARAMETERS : p_fname LIKE rlgrap-filename.
TYPES: BEGIN OF ty_munc,
land1 TYPE tzone-land1,
zone1 TYPE tzone-zone1,
vtext TYPE tzont-vtext,
END OF ty_munc.
DATA: i_munc TYPE STANDARD TABLE OF ty_munc,
i_tzone TYPE STANDARD TABLE OF tzone,
i_tzont TYPE STANDARD TABLE OF tzont,
wa_munc TYPE ty_munc,
wa_tzone TYPE tzone,
wa_tzont TYPE tzont.
CONSTANTS: c_path TYPE char20 VALUE 'C:\',
c_mask TYPE char9 VALUE ',*.*,*.*.',
c_mode TYPE char1 VALUE 'O',
c_filetype TYPE char10 VALUE 'ASC',
c_x TYPE char01 VALUE 'X'.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fname.
*-- Browse Presentation Server
PERFORM f4_presentation_file.
START-OF-SELECTION..
*-- Read presentation server file
PERFORM f1003_pre_file.
LOOP AT i_munc INTO wa_munc.
wa_tzone-mandt = wa_tzont-mandt = sy-mandt.
wa_tzone-land1 = wa_tzont-land1 = wa_munc-land1.
wa_tzone-zone1 = wa_tzont-zone1 = wa_munc-zone1.
wa_tzont-spras = sy-langu.
wa_tzont-vtext = wa_munc-vtext.
APPEND wa_tzont TO i_tzont.
APPEND wa_tzone TO i_tzone.
CLEAR: wa_munc, wa_tzont, wa_tzone.
ENDLOOP.
END-OF-SELECTION.
Modify Table TZONT
PERFORM enqueue_table USING text-001.
MODIFY tzont FROM TABLE i_tzont.
PERFORM dequeue_table USING text-001.
Modify Table TZONE
PERFORM enqueue_table USING text-002.
MODIFY tzone FROM TABLE i_tzone.
PERFORM dequeue_table USING text-002.
WRITE: 'Tables TZONE & TZONT are updated'.
*&----
*& Form f4_presentation_file
*&----
*& F4 Help for presentation server
*&----
FORM f4_presentation_file .
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_path = c_path
mask = c_mask
mode = c_mode
title = text-001
IMPORTING
filename = p_fname
EXCEPTIONS
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
OTHERS = 5.
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. " f4_presentation_file
*&----
*& Form f1003_pre_file
*&----
*& Upload File
*&----
FORM f1003_pre_file .
DATA: lcl_filename TYPE string.
lcl_filename = p_fname.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = lcl_filename
filetype = c_filetype
has_field_separator = c_x
TABLES
data_tab = i_munc
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.
EXIT.
ENDIF.
ENDFORM. " f1003_pre_file
&----
*& Form enqueue_table
&----
*& Enqueue Table
&----
FORM enqueue_table USING p_tabname.
CALL FUNCTION 'ENQUEUE_E_TABLE'
EXPORTING
tabname = p_tabname
EXCEPTIONS
foreign_lock = 1
system_failure = 2
OTHERS = 3.
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. " enqueue_table
&----
*& Form dequeue_table
&----
*& Dequeue Table
&----
FORM dequeue_table USING p_tabname.
CALL FUNCTION 'DEQUEUE_E_TABLE'
EXPORTING
tabname = p_tabname.
ENDFORM. " dequeue_table
Regards,
Prakash.
09-19-2006 6:27 PM
http://www.sapdevelopment.co.uk/file/file_upexcel.htm
here they used TEXT_CONVERT_XLS_TO_SAP function module to fetch values directly from the EXCEL file to your internal table.
check this link.very useful one
here is the small piece of code for the same FM.
REPORT zupload_excel_to_itab.
TYPE-POOLS: truxs.
PARAMETERS: p_file TYPE rlgrap-filename.
TYPES: BEGIN OF t_datatab,
col1(30) TYPE c,
col2(30) TYPE c,
col3(30) TYPE c,
END OF t_datatab.
DATA: it_datatab type standard table of t_datatab,
wa_datatab type t_datatab.
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.
START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
* I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_raw " WORK TABLE
i_filename = p_file
TABLES
i_tab_converted_data = it_datatab[] "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.
***********************************************************************
* END-OF-SELECTION.
END-OF-SELECTION.
LOOP AT it_datatab INTO wa_datatab.
WRITE:/ wa_datatab-col1,
wa_datatab-col2,
wa_datatab-col3.
ENDLOOP.
Message was edited by: Srikanth Kidambi
09-19-2006 6:28 PM
09-19-2006 6:31 PM
if you use 'ALSM_EXCEL_TO_INTERNAL_TABLE' function module, you have to split the values based on row and column numbers. to avoid this, we have another function module TEXT_CONVERT_XLS_TO_SAP.
if we give the internal table name to this function module,automatically all the values will be populate to that table. No need of any explicit logic.
Regards
srikanth
09-19-2006 6:41 PM
Thanks Srikanth and everyone.
I will try out and will get back if I have any problems.
Thanks again,
Shejal Shetty.
09-19-2006 6:30 PM
chk this
*&---------------------------------------------------------------------*
*& Report ZK_REPORT *
*& *
*&---------------------------------------------------------------------*
*& *
*& *
*&---------------------------------------------------------------------*
REPORT ZK_REPORT.
* internal table declarations
DATA: BEGIN OF ITAB OCCURS 0,
NAME(20) TYPE C,
ADDR(20) TYPE C,
END OF ITAB.
DATA: ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.
DATA: K1 TYPE I VALUE 1,
M1 TYPE I VALUE 1,
K2 TYPE I VALUE 100,
M2 TYPE I VALUE 9999.
****************************************
* use FM for uploading data from EXCEL to internal table
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = 'C:book1.xls'
I_BEGIN_COL = K1
I_BEGIN_ROW = M1
I_END_COL = K2
I_END_ROW = M2
TABLES
INTERN = ITAB1
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3
.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
LOOP AT ITAB1.
WRITE:/ ITAB1.
ENDLOOP.