topic Re: EXCEL to internal table Function Module in Application Development and Automation Discussions

EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 07:02:42 GMT

Hi all,

Please provide me a function module for uploading data from EXCEL into internal table.

Thanks in advance.

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 07:04:35 GMT

hi,

use

"ALSM_EXCEL_TO_INTERNAL_TABLE"

How to use,.. see below coding part

decare table line and read it.

take 2 columns.

DATA : ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE

check this example:

REPORT ZSR_BDC_XL

NO STANDARD PAGE HEADING LINE-SIZE 255.

TABLES : LFA1,RF02K.

DATA : BEGIN OF ITAB OCCURS 0,

LIFNR LIKE RF02K-LIFNR,

KTOKK LIKE RF02K-KTOKK,

NAME1 LIKE LFA1-NAME1,

SORTL LIKE LFA1-SORTL,

LAND1 LIKE LFA1-LAND1,

SPRAS LIKE LFA1-SPRAS,

END OF ITAB.

DATA : ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA : B1 TYPE I VALUE 1,

C1 TYPE I VALUE 1,

B2 TYPE I VALUE 10,

C2 TYPE I VALUE 99.

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = 'C:\Documents and Settings\sudhapa\Desktop\xl.XLS'

I_BEGIN_COL = B1

I_BEGIN_ROW = C1

I_END_COL = B2

I_END_ROW = C2

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.

PERFORM ORGANIZE_UPLOADED_DATA.

FORM ORGANIZE_UPLOADED_DATA .

SORT ITAB1 BY ROW

COL.

LOOP AT ITAB1.

CASE ITAB1-COL.

....................................................

WHEN 1.

ITAB-LIFNR = ITAB1-VALUE.

WHEN 2.

ITAB-KTOKK = ITAB1-VALUE.

WHEN 3.

ITAB-NAME1 = ITAB1-VALUE.

WHEN 4.

ITAB-SORTL = ITAB1-VALUE.

WHEN 5.

ITAB-LAND1 = ITAB1-VALUE.

WHEN 6.

ITAB-SPRAS = ITAB1-VALUE.

....................................................

ENDCASE.

AT END OF ROW.

APPEND ITAB.

CLEAR ITAB.

ENDAT.

ENDLOOP.

ENDFORM. " ORGANIZE_UPLOADED_DATA

rewards if useful,

regards,

Nazeer shaik

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 07:04:51 GMT

it is

ALSM_EXCEL_TO_INTERNAL_TABLE

Reward points for useful Answers

Regards

Anji

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 07:05:28 GMT

Hi,

There are also a couple of alternatives which use fucntion modules 'KCD_EXCEL_OLE_TO_INT_CONVERT'

and 'ALSM_EXCEL_TO_INTERNAL_TABLE' but the method below is by far the simplest method to used

Simple code:

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.

Regards

Sudheer

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 07:05:47 GMT

ALSM_EXCEL_TO_INTERNAL_TABLE

Check the above mentione FM.

Thanks,

Deepti

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 07:06:04 GMT

Convert excel into tabbed delimited txt file n use GUI_UPLOAD.

Sameer

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 07:06:13 GMT

alsm_upload_excel

alsm_excel_to_internal_table

text_convert_xls_to_sap "it converts excel format to sap

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 07:09:20 GMT

Its 1. ALSM_EXCEL_TO_INTERNAL_TABLE

2. TEXT_CONVERT_XLS_TO_SAP

Reward points for useful Answers

Regards

Ashish

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 07:09:24 GMT

Hi,

http://www.sapdevelopment.co.uk/file/file_upexcel.htm

There are also a couple of alternatives which use fu

nction modules 'KCD_EXCEL_OLE_TO_INT_CONVERT'

and 'ALSM_EXCEL_TO_INTERNAL_TABLE' but the method in this link is by far the simplest method to be used.

Regards

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 08:15:49 GMT

Hi,

Use this.

*-----Uploading excel file into internal table

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = p_fname

TABLES

i_tab_converted_data = it_tab[]

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

Thanks,

Anitha

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 08:37:48 GMT

Hi,

There are also a couple of alternatives which use fucntion modules 'ALSM_EXCEL_TO_INTERNAL_TABLE' but the method below is by far the simplest method to used.

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.

Hope this helps.

Reward if helpful.

Regards,

Sipra

Re: EXCEL to internal table Function Module

Former Member — Wed, 08 Aug 2007 08:43:36 GMT

Hi,

check the below example code.



REPORT  zmms_excel_itab                         .


DATA: itab LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.

TYPES: BEGIN OF t_record,
name1 LIKE itab-value,
name2 LIKE itab-value,
age LIKE itab-value,
END OF t_record.
DATA: it_record TYPE STANDARD TABLE OF t_record INITIAL SIZE 0,
wa_record TYPE t_record.
DATA: gd_currentrow TYPE i.

*Selection Screen Declaration
*----------------------------
PARAMETER p_infile LIKE rlgrap-filename.

at selection-screen  ON VALUE-REQUEST FOR p_infile.

CALL FUNCTION 'F4_FILENAME'
 EXPORTING
   PROGRAM_NAME        = SYST-CPROG
   DYNPRO_NUMBER       = SYST-DYNNR
   FIELD_NAME          = 'p_infile'
 IMPORTING
   FILE_NAME           = p_infile.
          .

************************************************************************
START-OF-SELECTION.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                = p_infile
    i_begin_col             = '1'
    i_begin_row             = '2' "Do not require headings
    i_end_col               = '100'
    i_end_row               = '1000'
  TABLES
    intern                  = itab
  EXCEPTIONS
    inconsistent_parameters = 1
    upload_ole              = 2
    OTHERS                  = 3.
IF sy-subrc <> 0.
  MESSAGE e010(zz) WITH text-001. "Problem uploading Excel Spreadsheet
ENDIF.

* Sort table by rows and colums
SORT itab BY row col.

* Get first row retrieved
READ TABLE itab INDEX 1.

* Set first row retrieved to current row
gd_currentrow = itab-row.

LOOP AT itab.
* Reset values for next row
  IF itab-row NE gd_currentrow.
    APPEND wa_record TO it_record.
    CLEAR wa_record.
    gd_currentrow = itab-row.
  ENDIF.

  CASE itab-col.
    WHEN '0001'. "First name
      wa_record-name1 = itab-value.
    WHEN '0002'. "Surname
      wa_record-name2 = itab-value.
    WHEN '0003'. "Age
      wa_record-age = itab-value.
  ENDCASE.
ENDLOOP.
APPEND wa_record TO it_record.
*!! Excel data is now contained within the internal table IT_RECORD

* Display report data for illustration purposes
LOOP AT it_record INTO wa_record.
  WRITE:/ sy-vline,
  (10) wa_record-name1, sy-vline,
  (30) wa_record-name2, sy-vline,
  (80) wa_record-age, sy-vline.
ENDLOOP.

Reward Points if it is Useful.

Thanks,

Manjunath MS