Sunday, March 6, 2011

Function module for Excel to internal table in Abap-ALSM_EXCEL_TO_INTERNAL_TABLE


ALSM_EXCEL_TO_INTERNAL_TABLE has been a useful utility function module given in SAP R/3 and most of the SAP systems.It helps us in uploading the MS excel data into an internal table.The function module has been found to be compatible with new versions of Microsoft excel as well.

The most parameters of the function module are :
1)FILENAME : The file name with the exact path specified.
2)I_BEGIN_COL : The beginning of the column.Number to be specified.If given 1, the heading would be captured.
3)I_BEGIN_ROW: : The beginning of the row.Number to be specified.If given 1, the heading would be captured
3)I_END_COL : The End number of the column to be specified.
4) I_END_ROW : The End number of the column to be specified.
5)Intern: The internal table in which the contents of excel would be stored.


One of the significant problems by using this function module is that data is arranged in the order of row ,column, row column instead of row,row,,row,row

For example:
If contents in excel are:
SAP 12
Abap 23
Learn 31
Programming 14
It would be uploaded to the internal table as
SAP
12
Abap
23
Learn
31
Programming
14
For avoiding this hassle, we can make use of a simple logic and an excellent use of sy-tabix.
The sample program here should serve the purpose:



PARAMETERS: p_file TYPE rlgrap-filename.

TYPES: BEGIN OF t_itab2,
newval(50) TYPE c,
oldval(50) TYPE c,
end of t_itab2.

data : itab1 like standard table of ALSMEX_TABLINE with header line.
data: itab2 type standard table of t_itab2 with header line.
data: gs_itab1 type ALSMEX_tabline.
* At selection screen
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
field_name = 'filename'
IMPORTING
file_name = p_file.
if p_file is not initial.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = p_file
I_BEGIN_COL = '1'
I_BEGIN_ROW = '1'
I_END_COL = '2'
I_END_ROW = '1050'
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.
endif.
break-point.

data: var type i.
data : res type i.

loop at itab1 into gs_itab1.

var = sy-tabix.

res = var MOD 2.
If res = 0.
move gs_itab1-value to itab2-newval.
else.
move gs_itab1-value to itab2-oldval.
endif.
at end of row.
append itab2.
endat.
endloop.

No comments:

Post a Comment