2006 Jul 03 4:24 PM
Hi Gurus,
I often used FM ALSM_EXCEL_TO_INTERNAL_TABLE or FM KCD_EXCEL_OLE_TO_INT_CONVERT to read data into an internal table. So I ought to know how things work. But I have a strange problem.
My Excel sheet consists of 5 lines, 22 columns 1 header. So I let the FM start at COL no. 1, ROW no. 2 and end it at COL 22 and ROW 1000.
While debugging I see first three rows go correctly, but after the 4th row the COUNTER in the internal table that represents the current no. of the Column exceeds 22.
It just doesn't get reset to 1 again after reaching the end of a row.
I tried all sorts of options, like varying columns, file, etc. etc.
Version of SAP is 6.20. Anyone has an idea what goes wrong? Regards, Ron.
2006 Jul 03 4:29 PM
Hai
Check the following Code
&----
*& Report ZK_REPORT *
*& *
&----
*& *
*& *
&----
REPORT ZK_REPORT.
internal table declarations
DATA: BEGIN OF ITAB OCCURS 0,
NAME(20) TYPE C,
ADDR(20) TYPE C,
END OF ITAB.
DATA: ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.
DATA: K1 TYPE I VALUE 1,
M1 TYPE I VALUE 1,
K2 TYPE I VALUE 100,
M2 TYPE I VALUE 9999.
****************************************
use FM for uploading data from EXCEL to internal table
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = 'C:\book1.xls'
I_BEGIN_COL = K1
I_BEGIN_ROW = M1
I_END_COL = K2
I_END_ROW = M2
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.
Thanks & regards
Sreenivasulu P
Message was edited by: Sreenivasulu Ponnadi
Message was edited by: Sreenivasulu Ponnadi
2006 Jul 03 4:32 PM
Hi,
Check below code :
it is working fine for me.
Laxman
Hold data from Excel
DATA: i_excel TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.
DATA : BEGIN OF i_intern OCCURS 0,
row TYPE kcd_ex_row_n,
col TYPE kcd_ex_col_n,
value TYPE line,
END OF i_intern.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 11
i_end_row = 9999
TABLES
intern = i_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
ENDIF.
LOOP AT i_excel.
MOVE-CORRESPONDING i_excel TO i_intern.
APPEND i_intern.
CLEAR i_intern.
ENDLOOP.
2006 Jul 03 4:34 PM
You could use the following function module to upload the excel sheet.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_field_seperator = c_x
i_tab_raw_data = wa_rawdata
i_filename = filename
TABLES
i_tab_converted_data = i_excel
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
2006 Jul 04 10:27 AM
Hi Dominic,
your code did work. I still don't know why the other two FM don't work, since I've been using them several time already. Thanks again.
2006 Jul 03 4:34 PM
2006 Jul 04 10:12 AM
Thanks Rich, good to be here!
Anyway, here's my source: the call of the FM goes like
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = rlgrap-filename
i_begin_col = '1'
i_begin_row = '2'
i_end_col = '22'
i_end_row = '1000'
TABLES
intern = it_upload
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.
after that the next routine is like:
SORT it_upload BY row col.
LOOP AT it_upload INTO w_upload.
MOVE : w_upload-col TO l_index.
ASSIGN COMPONENT l_index OF STRUCTURE it_data TO <fs>.
MOVE : w_upload-value TO <fs>.
AT END OF row.
APPEND it_data.
CLEAR it_data.
ENDAT.
ENDLOOP.
ofcourse, IT_UPLOAD is defined at ALSMEX_TABLINE. I haven't tried that TEXT_CONVERT_XLS_TO_SAP FM, I see that's a bit different then the other two (ALSM... and KCD....)
p.s. the others also thanks for their reply. I'm sorry they haven't been of much help. Thanks though!
Message was edited by: Ron Dijkstra
2006 Jul 04 10:13 AM
Hi ron,
1. U want to upload data from EXCEL
into internal table.
2. and u are using ALSM_EXCEL_TO_INTERNAL_TABLE.
3. But We cannot do this direclty !
4. we have to apply some more logic
bcos the FM uploads data of excel
in the intenal table,
CELL BY CELL
9. afTER THAT , we have to convert this cell by cell data,
into our format of internal table.
10. use this code (just copy paste in new program)
(it is tried wit T001 structure data)
(it will AUTOMATICALLY based upon the
fields of internal table,
convert data from cell by cell,
to that of internal table fields)
REPORT abc.
*----
DATA : ex LIKE TABLE OF alsmex_tabline WITH HEADER LINE.
DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.
DATA : cmp LIKE TABLE OF rstrucinfo WITH HEADER LINE.
DATA : col TYPE i.
DATA : col1 TYPE i.
FIELD-SYMBOLS : <fs> .
DATA : fldname(50) TYPE c.
*----
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = 'd:\def.xls'
i_begin_col = 1
i_begin_row = 1
i_end_col = 100
i_end_row = 100
TABLES
intern = ex
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
BREAK-POINT.
*----
CALL FUNCTION 'GET_COMPONENT_LIST'
EXPORTING
program = sy-repid
fieldname = 'T001'
TABLES
components = cmp.
*----
LOOP AT ex.
AT NEW row.
IF sy-tabix <> 1.
APPEND t001.
CLEAR t001.
ENDIF.
ENDAT.
col = ex-col.
col1 = col + 1.
READ TABLE cmp INDEX col.
CONCATENATE 'T001-' cmp-compname INTO fldname.
ASSIGN (fldname) TO <fs>.
<fs> = ex-value.
ENDLOOP.
BREAK-POINT.
regards,
amit m.
2006 Jul 04 12:21 PM
for excel upload, check the below links
http://www.sapdevelopment.co.uk/file/file_upexcel.htm
http://www.sapdevelopment.co.uk/file/file_upexcelalt1.htm
http://www.sapdevelopment.co.uk/file/file_upexcelalt2.htm
regards
srikanth
2006 Jul 04 12:36 PM
Hi Ron,
You use this function module 'EXT_CONVERT_XLS_TO_SAP', it is very useful and also the simple to use.
Thanks,
Dharmesh
2006 Jul 04 1:37 PM
Hi,
See this sample program.
REPORT ZEXCEL .
Structure for Final Internal Table
TYPES: BEGIN OF TY_FINAL,
MATNR LIKE RC29N-MATNR, "Material BOM
WERKS LIKE RC29N-WERKS, "Plant
STLAN LIKE RC29N-STLAN, "BOM Usage
STLAL LIKE RC29N-STLAL, "Alternative BOM
DATUV LIKE RC29N-DATUV, "Valid-From Date
ZTEXT LIKE RC29K-ZTEXT, "BOM text
POSTP LIKE RC29P-POSTP, "Item category (bill of
"material)
IDNRK LIKE RC29P-IDNRK, "BOM component
MENGE(16), "Component quantity
END OF TY_FINAL.
DATA: I_FINAL TYPE STANDARD TABLE OF TY_FINAL WITH HEADER LINE.
data: i_BDCDATA like bdcdata occurs 0 with header line.
DATA: BEGIN OF IEXCEL OCCURS 0.
INCLUDE STRUCTURE ALSMEX_TABLINE.
DATA: END OF IEXCEL.
PARAMETERS: P_FILE LIKE RLGRAP-FILENAME.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
PERFORM SHOW_HELP_FOR_P_FILE.
START-OF-SELECTION.
PERFORM GET_FILE.
PERFORM F_FILL_BDCDATA.
PERFORM F_SESSION.
END-OF-SELECTION.
PERFORM DISPLAY_DATA.
&----
*& Form SHOW_HELP_FOR_P_FILE
&----
FORM SHOW_HELP_FOR_P_FILE.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '
STATIC = ' '
MASK = ' '
CHANGING
FILE_NAME = P_FILE
EXCEPTIONS
MASK_TOO_LONG = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
EXIT.
ENDIF.
ENDFORM. " SHOW_HELP_FOR_P_FILE
&----
*& Form GET_FILE
&----
FORM GET_FILE.
DATA: V_LEN TYPE I.
SHIFT P_FILE RIGHT DELETING TRAILING SPACE.
V_LEN = STRLEN( P_FILE ).
V_LEN = V_LEN - 3.
IF ( P_FILEV_LEN(3) EQ 'xls' OR P_FILEV_LEN(3) EQ 'XLS' ) .
Get all the details from the Excel file
SHIFT P_FILE LEFT DELETING LEADING SPACE.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FILE
I_BEGIN_COL = 1
I_BEGIN_ROW = 1
I_END_COL = 9
I_END_ROW = 100
TABLES
INTERN = IEXCEL
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
SORT IEXCEL BY ROW COL.
LOOP AT IEXCEL.
IF IEXCEL-COL = '0001'.
I_FINAL-MATNR = IEXCEL-VALUE.
ENDIF.
IF IEXCEL-COL = '0002'.
I_FINAL-WERKS = IEXCEL-VALUE.
ENDIF.
IF IEXCEL-COL = '0003'.
I_FINAL-STLAN = IEXCEL-VALUE.
ENDIF.
IF IEXCEL-COL = '0004'.
I_FINAL-STLAL = IEXCEL-VALUE.
ENDIF.
IF IEXCEL-COL = '0005'.
I_FINAL-DATUV = IEXCEL-VALUE.
ENDIF.
IF IEXCEL-COL = '0006'.
I_FINAL-ZTEXT = IEXCEL-VALUE.
ENDIF.
IF IEXCEL-COL = '0007'.
I_FINAL-POSTP = IEXCEL-VALUE.
ENDIF.
IF IEXCEL-COL = '0008'.
I_FINAL-IDNRK = IEXCEL-VALUE.
ENDIF.
IF IEXCEL-COL = '0009'.
I_FINAL-MENGE = IEXCEL-VALUE.
ENDIF.
AT END OF ROW.
APPEND I_FINAL.
CLEAR I_FINAL.
ENDAT.
ENDLOOP.
ENDIF.
ENDFORM. " GET_FILE
&----
*& Form DISPLAY_DATA
&----
text
----
--> p1 text
<-- p2 text
----
FORM DISPLAY_DATA.
LOOP AT I_FINAL.
WRITE: /1(18) I_FINAL-MATNR, "Material BOM
19(4) I_FINAL-WERKS, "Plant
24(1) I_FINAL-STLAN, "BOM Usage
26(2) I_FINAL-STLAL, "Alternative BOM
29(8) I_FINAL-DATUV, "Valid-From Date
40(40) I_FINAL-ZTEXT, "BOM text
81(1) I_FINAL-POSTP, "Item category (bill of
"material)
82(18) I_FINAL-IDNRK, "BOM component
101(13) I_FINAL-MENGE RIGHT-JUSTIFIED. "Component
"quantity
ENDLOOP.
ENDFORM. " DISPLAY_DATA