Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

EXCEL to internal table Function Module

Former Member

‎2007 Aug 08 8:02 AM

0 Likes
15,621

Hi all,

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

Thanks in advance.

11 REPLIES 11
Read only

Former Member

‎2007 Aug 08 8:04 AM

0 Likes
2,700

hi,

use

<b>"ALSM_EXCEL_TO_INTERNAL_TABLE"</b>

<b>How to use,.. see below coding part</b>

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

Read only

Former Member

‎2007 Aug 08 8:04 AM

0 Likes
2,700

Hi

it is

<b>ALSM_EXCEL_TO_INTERNAL_TABLE</b>

<b>Reward points for useful Answers</b>

Regards

Anji

Read only

Former Member

‎2007 Aug 08 8:05 AM

0 Likes
2,700

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

Read only

Former Member

‎2007 Aug 08 8:05 AM

0 Likes
2,700

hi

ALSM_EXCEL_TO_INTERNAL_TABLE

Check the above mentione FM.

Thanks,

Deepti

Read only

Former Member

‎2007 Aug 08 8:06 AM

0 Likes
2,700

Convert excel into tabbed delimited txt file n use GUI_UPLOAD.

Sameer

Read only

Former Member

‎2007 Aug 08 8:06 AM

0 Likes
2,700

alsm_upload_excel

alsm_excel_to_internal_table

text_convert_xls_to_sap "it converts excel format to sap

Read only

Former Member

‎2007 Aug 08 8:09 AM

0 Likes
2,700

Hi

Its 1. ALSM_EXCEL_TO_INTERNAL_TABLE

2. TEXT_CONVERT_XLS_TO_SAP

<b>Reward points for useful Answers</b>

Regards

Ashish

Read only

Former Member

‎2007 Aug 08 8:09 AM

0 Likes
2,700

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

Read only

Former Member

‎2007 Aug 08 9:15 AM

0 Likes
2,700

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

Read only

Former Member

‎2007 Aug 08 9:37 AM

0 Likes
2,700

Hi,

There are also a couple of alternatives which use fucntion modules <b>'ALSM_EXCEL_TO_INTERNAL_TABLE'</b> 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

Read only

Former Member

‎2007 Aug 08 9:43 AM

0 Likes
2,700

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