Excel file to internal table in sap

[Former Member]

hi all,

I know about 'ALSM_EXCEL_TO_INTERNAL_TABLE' function module.. from excel file to internal table in sap.

but how to program it..??

reply me soon,

thx,

s.suresh.

[Former Member]

hi,

*..............................................................

*: Description :

*: -

---

:

*: This is a simple example program to get data from an excel :

*: file and store it in an internal table. :

*: :

*:

*: :

*: SAP Version : 4.7 :

*:............................................................:

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 is helpful, Do reward.

[Former Member]

REPORT ZDBLV_UPLOAD_EXCEL_TEST. 
data: begin of itab occurs 0, 
name(20) type c, 
addre(20) type c, 
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 100, 
C2 TYPE I VALUE 9999. 

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' 
EXPORTING 
FILENAME = 'C:\test.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. 

loop at itab1. 
write:/ itab1. 
Endloop.

Reward points if useful.

[amit_khare]

Refer the thread for sample program -

Regards,

Amit

[Former Member]

hi,

&----

---

*& Report UPLOAD_EXCEL *

*& *

&----

---

*& *

*& Upload and excel file into an internal table using the following *

*& function module: ALSM_EXCEL_TO_INTERNAL_TABLE *

&----

---

REPORT UPLOAD_EXCEL no standard page heading.

*Data Declaration

*----

---

data: itab like alsmex_tabline occurs 0 with header line.

• Has the following format:

• Row number | Colum Number | Value

• ---------------------------------------

• i.e. 1 1 Name1

• 2 1 Joe

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.

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

*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 = '14'

i_end_row = '31'

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,

(10) wa_record-name2, sy-vline,

(10) wa_record-age, sy-vline.

endloop.

Hope this is helpful, Do reward.

[Former Member]

How can I read an Excel file from presentation server?

You can use the Function module ALSM_EXCEL_TO_INTERNAL_TABLE to read the Excel file into the internal table of type alsmex_tabline.

From this internal table you can fill the target internal table.

TYPES:

BEGIN OF ty_upload,

field1 TYPE c length 12,

field2 TYPE c length 12,

field3 TYPE c length 12,

END OF ty_upload.

DATA it_upload TYPE STANDARD TABLE OF ty_upload WITH DEFAULT KEY.

DATA wa_upload TYPE ty_upload.

DATA itab TYPE STANDARD TABLE OF alsmex_tabline WITH DEFAULT KEY.

FIELD-SYMBOLS: &lt;wa&gt; type alsmex_tabline.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 3

i_end_row = 65535

TABLES

intern = itab.

LOOP AT itab ASSIGNING &lt;wa&gt;.

CASE &lt;wa&gt;-col.

WHEN '0001'.

wa_upload-field1 = &lt;wa&gt;-value.

WHEN '0002'.

wa_upload-field2 = &lt;wa&gt;-value.

WHEN '0003'.

wa_upload-field3 = &lt;wa&gt;-value.

ENDCASE.

APPEND wa_upload TO it_upload.

CLEAR wa_upload.

ENDLOOP.

[Former Member]

Use the FM ALSM_EXCEL_TO_INTERNAL_TABLE

PARAMETERS:
P_INFL like RLGRAP-FILENAME.

DATA:
BEGIN OF T_DATA1 OCCURS 0,
RESOURCE(25) TYPE C,
DATE(10) TYPE C,
DURATION TYPE P DECIMALS 2,
ACTIVITY(25) TYPE C,
B_NBILL(1) TYPE C,
END OF T_DATA1,

T_DATA TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE,

BEGIN OF T_FINAL OCCURS 0,
RESOURCE(25) TYPE C,
DATE(10) TYPE C,
DURATION(15) TYPE C,
ACTIVITY(25) TYPE C,
B_NBILL(10) TYPE C,

END OF T_FINAL.

DATA : HEADER TYPE XSTRING.


Work Variables Declaration. 
CONSTANTS:
W_Y TYPE C VALUE 'Y',
W_N TYPE C VALUE 'N'.


Work area. 
DATA:
WA_DATA LIKE T_FINAL.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_INFL.

PERFORM GET_FILENAME CHANGING P_INFL.

-----------------------------------------------------------
START-OF-SELECTION.
-----------------------------------------------------------
PERFORM UPLOAD_DATA_FROMEXCEL.


FORM UPLOAD_DATA_FROMEXCEL.


Downloading the data from presentation server 

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = p_infl
I_BEGIN_COL = 1
I_BEGIN_ROW = 2
I_END_COL = 8
I_END_ROW = 1000
TABLES
INTERN = T_DATA
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.

ENDFORM. " upload_data_fromexcel


&---------------------------------------------------------------------
*& Form process_data
&---------------------------------------------------------------------


text 
----------------------------------------------------------------------
FORM PROCESS_DATA .

T_FINAL-RESOURCE = 'Resource'.
T_FINAL-DATE = 'Date'.
T_FINAL-DURATION = 'Duration'.
T_FINAL-ACTIVITY = 'Activity'.
T_FINAL-B_NBILL = 'Billable'.
APPEND T_FINAL.

SORT T_DATA BY ROW COL.

LOOP AT T_DATA.

CASE T_DATA-COL.
WHEN 3.
T_DATA1-RESOURCE = T_DATA-VALUE.
WHEN 4.
T_DATA1-DATE = T_DATA-VALUE.
WHEN 5.
T_DATA1-DURATION = T_DATA-VALUE.
WHEN 6.

t_data1-activity = t_data-value. 
WHEN 7.
T_DATA1-B_NBILL = T_DATA-VALUE.

ENDCASE.

AT END OF ROW.
COLLECT T_DATA1.
ENDAT.
ENDLOOP.

LOOP AT T_DATA1.

T_FINAL-RESOURCE = T_DATA1-RESOURCE.
T_FINAL-DATE = T_DATA1-DATE.
T_FINAL-DURATION = T_DATA1-DURATION.
T_FINAL-ACTIVITY = T_DATA1-ACTIVITY.
T_FINAL-B_NBILL = T_DATA1-B_NBILL.
APPEND T_FINAL.

ENDLOOP.

ENDFORM. " process_data

&---------------------------------------------------------------------
*& Form get_filename
&---------------------------------------------------------------------

FORM GET_FILENAME CHANGING P_FILENAME.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
DEF_FILENAME = SPACE
DEF_PATH = P_FILENAME
MASK = ',. ,..'
MODE = 'O' " O = Open, S = Save

TITLE = BOX_TITLE 
IMPORTING
FILENAME = P_FILENAME
EXCEPTIONS
INV_WINSYS = 1
NO_BATCH = 2
SELECTION_CANCEL = 3
SELECTION_ERROR = 4
OTHERS = 5.
*

CASE SY-SUBRC. 
WHEN 1. 
MESSAGE I999 WITH 
'File selector not available on this windows system'(046). 
WHEN 2. 
MESSAGE E999 WITH 
'Frontend function cannot be executed in background'(047). 
WHEN 3. 
MESSAGE I999 WITH 'Selection was cancelled'(048). 
WHEN 4. 
MESSAGE E999 WITH 'Communication error'(049). 
WHEN 5. 
MESSAGE E999 WITH 'Other error'(050). 
ENDCASE. 

ENDFORM. " get_filename

Reward Points if useful.

[Former Member]

Hello Suresh,

I have created and testing this program using that function module. It is working fine.

REPORT zsample.

DATA: itab TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.

DATA: path TYPE rlgrap-filename.

path = 'C:\DOCUMENTS AND SETTINGS\SSR3KOR\DESKTOP\TEST.XLS'.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
     EXPORTING
          filename    = path
          i_begin_col = 1
          i_begin_row = 1
          i_end_col   = 2
          i_end_row   = 2
     TABLES
          intern      = itab.
*    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 itab.
  WRITE: / itab.
ENDLOOP.

Reward If Helpful.

Regards

--

Sasidhar Reddy Matli.

[Former Member]

hi

Upload and excel file into an internal table using the following

function module: ALSM_EXCEL_TO_INTERNAL_TABLE

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.

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

*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 = '14'

i_end_row = '31'

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,

(10) wa_record-name2, sy-vline,

(10) wa_record-age, sy-vline.