topic Re: doubt in uploading data from excel in Application Development and Automation Discussions

doubt in uploading data from excel

Former Member — Sat, 23 Sep 2006 09:28:46 GMT

hi ,

i have used ALSM_EXCEL_TO_INTERNAL_TABLE to upload data from excel .. but the used does not want to enter row and column ....is there any other function module that does not need row and column while retriving data from excel

thanks in advace

lokesh

Re: doubt in uploading data from excel

Former Member — Sat, 23 Sep 2006 09:51:35 GMT

Hi lokesh,

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. In this FM,

there is one TABLES

INTERN = t_upload1

The definition of this t_upload1

should be as per

INTERN LIKE ALSMEX_TABLINE

and not as per your data.

5. Hence, it is giving error here .

*----

For uploading purpose :

6. There are TWO options.

a) either save the excel to TAB Delimited file,

and use GUI_UPLOAD to upload the data in internal table.

b) use FM for excel purpose.

7. a) is easy and recommended

8. b) there is a FM for it,

but 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: doubt in uploading data from excel

Former Member — Sat, 23 Sep 2006 09:55:05 GMT

Hi,

You can give constant values for row and columns..

this is how i have done refer to following code..

DATA:

ALSM_EXCEL_TO_INTERNAL_TABLE structure

g_t_intern LIKE alsmex_tabline OCCURS 100 WITH HEADER LINE,

Column in your spreadsheet where you want to start
importing data

g_f_start_col TYPE i VALUE 1,

Row in your spreadsheet where you want to start
importing data

g_f_start_row TYPE i VALUE 1,

Column in your spreadsheet where you want to stop
importing data

g_f_end_col TYPE i VALUE 9,

Row in your spreadsheet where you want to stop
importing data

g_f_end_row TYPE i VALUE 9999.

FORM excel_upload .

DATA : l_f_index TYPE i.

FIELD-SYMBOLS : <l_fs> TYPE ANY.

Upload Excel file

IF p_rb2 = 'X'.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_fname2

i_begin_col = g_f_start_col

i_begin_row = g_f_start_row

i_end_col = g_f_end_col

i_end_row = g_f_end_row

TABLES

intern = g_t_intern

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.

Populate data into Internal table

SORT g_t_intern BY row col .

LOOP AT g_t_intern .

MOVE g_t_intern-col TO l_f_index .

ASSIGN COMPONENT l_f_index OF STRUCTURE g_s_upld TO <l_fs> .

MOVE g_t_intern-value TO <l_fs>.

AT END OF row .

APPEND g_s_upld TO g_t_upld.

CLEAR g_s_upld .

ENDAT .

ENDLOOP .

LOOP AT g_t_upld INTO g_s_upld.

Convert the material to internal format.

CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT'

EXPORTING

input = g_s_upld-matnr

IMPORTING

output = g_s_upld-matnr

EXCEPTIONS

length_error = 1

OTHERS = 2.

IF sy-subrc EQ 0.

MODIFY g_t_upld FROM g_s_upld TRANSPORTING matnr.

ENDIF.

ENDLOOP.

ENDFORM. " excel_upload

reward if useful...

Re: doubt in uploading data from excel

Former Member — Sat, 23 Sep 2006 10:45:50 GMT

http://www.sapdevelopment.co.uk/file/file_upexcel.htm

check this link,

its using TEXT_CONVERT_XLS_TO_SAP function module to upload EXCEL file to your internal table.very easy to use this.

Regards

Srikanth

Re: doubt in uploading data from excel

anversha_s — Sat, 23 Sep 2006 17:05:43 GMT

hi lokesh,

try this.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = p_file

i_begin_col = 1

i_begin_row = 2 "no heading

i_end_col = 15

i_end_row = 65000

tables

intern = it_rows

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.

if not it_rows[] is initial.

sort it_rows by row col.

loop at it_rows.

move : it_rows-col to v_index.

assign component v_index of structure it_output to <fs>.

move : it_rows-value to <fs>.

at end of row.

append it_output.

clear it_output.

endat.

endloop.

endif.

rgds,

anver

pls mark all helpful answers

Re: doubt in uploading data from excel

abdul_hakim — Sat, 23 Sep 2006 17:41:35 GMT

you need to specify row and column if u wanna use ALSM_EXCEL_TO_INTERNAL_TABLE fm.

or else you convert the excel file to tab delimited file and upload using GUI_UPLOAD.

Cheers,

Abdul Hakim