topic Re: Uploading into Excel in Application Development and Automation Discussions

Uploading into Excel

Former Member — Mon, 03 Jul 2006 15:24:58 GMT

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.

Re: Uploading into Excel

Former Member — Mon, 03 Jul 2006 15:29:01 GMT

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

Re: Uploading into Excel

Laxmana_Appana_ — Mon, 03 Jul 2006 15:32:25 GMT

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.

Re: Uploading into Excel

Former Member — Mon, 03 Jul 2006 15:34:05 GMT

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.

Re: Uploading into Excel

RichHeilman — Mon, 03 Jul 2006 15:34:17 GMT

Hi Ron, Welcome to SDN! It may help if we see your code, can you please post it.

Regards,

Rich Heilman

Re: Uploading into Excel

Former Member — Tue, 04 Jul 2006 09:12:02 GMT

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

Re: Uploading into Excel

Former Member — Tue, 04 Jul 2006 09:13:52 GMT

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.

Re: Uploading into Excel

Former Member — Tue, 04 Jul 2006 09:27:41 GMT

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.

Re: Uploading into Excel

Former Member — Tue, 04 Jul 2006 11:21:14 GMT

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

Re: Uploading into Excel

Former Member — Tue, 04 Jul 2006 11:36:43 GMT

Hi Ron,

You use this function module 'EXT_CONVERT_XLS_TO_SAP', it is very useful and also the simple to use.

Thanks,

Dharmesh

Re: Uploading into Excel

Former Member — Tue, 04 Jul 2006 12:37:09 GMT

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