loading data from excel file

[Former Member]

hi experts

how to load the data from-excel to sap using bdc method

[Former Member]

First read data from excel to internal table using FM ALSM_EXCEL_TO_INTERNAL_TABLE and then BDC process is same as usual.

Reward points if useful.

[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]

Use the FM.

ALSM_EXCEL_TO_INTERNAL_TABLE.

Check thsi thread for sample code.

ALso use the GUI_UPLOAD function module to load the data from the excel to an internal table and then you can modify the database table from this internal table.

Check this thread for sample program for bdc-uploading excel sheet