topic Re: Extel to Internal Table in Application Development and Automation Discussions

Extel to Internal Table

Former Member — Tue, 19 Sep 2006 17:23:10 GMT

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.

Re: Extel to Internal Table

Former Member — Tue, 19 Sep 2006 17:24:00 GMT

ASLM_EXCEL_TO_INTERNAL_TABLE

Re: Extel to Internal Table

Former Member — Tue, 19 Sep 2006 17:25:41 GMT

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.

Re: Extel to Internal Table

Former Member — Tue, 19 Sep 2006 17:27:38 GMT

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

Re: Extel to Internal Table

Former Member — Tue, 19 Sep 2006 17:28:49 GMT

Thanks for all the Information.

Shejal Shetty.

Re: Extel to Internal Table

Former Member — Tue, 19 Sep 2006 17:30:19 GMT

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.

Re: Extel to Internal Table

Former Member — Tue, 19 Sep 2006 17:31:59 GMT

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

Re: Extel to Internal Table

Former Member — Tue, 19 Sep 2006 17:41:08 GMT

Thanks Srikanth and everyone.

I will try out and will get back if I have any problems.

Thanks again,

Shejal Shetty.