upload excel sheet to internal table

[Former Member]

Hi experts,

Please tell me the solution for this uploading problem :-i have created a upload program in which i am using fm ALSM_EXCEL_TO_INTERNAL_TABLE but when i am using it,it is giving exception 2 which is upload_ole.Also the excel is created in open office.org to which i have converted to microsft excel 97/2000/xp(.xls) format.May be my problem is because of this format but how to remove this problem please suggest.

Thanks in advance,

nehavt

[Former Member]

Hi experts,

Please tell me the solution for this uploading problem :-i have created a upload program in which i am using fm ALSM_EXCEL_TO_INTERNAL_TABLE but when i am using it,it is giving exception 2 which is upload_ole.Also the excel is created in open office.org to which i have converted to microsft excel 97/2000/xp(.xls) format.May be my problem is because of this format but how to remove this problem please suggest.

Thanks in advance,

nehavt

[Former Member]

Hi,

try with Function module 'TEXT_CONVERT_XLS_TO_SAP'

code snippet..

REPORT zupload_excel_to_itab.

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE rlgrap-filename.

TYPES: BEGIN OF t_datatab,

col1(30) TYPE c,

col2(30) TYPE c,

col3(30) TYPE c,

END OF t_datatab.

DATA: it_datatab type standard table of t_datatab,

wa_datatab type t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

At selection screen

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = it_datatab[] "ACTUAL DATA

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3.

ENDLOOP

[Former Member]

Hi Kiran,

when i am using fm TEXT_CONVERT_XLS_TO_SAP as suggested by the code snippet it is giving exception that conversion_failed.

[Former Member]

Hi,

Check this thread

Regards,

Selva

[Former Member]

Hi,

Please try the below.

types: begin of l_ty_excel.

include structure alsmex_tabline.

types: end of l_ty_excel.

data: l_wa_excel type l_ty_excel,

l_it_excel type standard table of l_ty_excel.

DATA:l_index TYPE i,

l_index1 TYPE i,

l_end_row TYPE i.

DATA:l_start_col TYPE i VALUE 1,

l_start_row TYPE i VALUE 1,

l_end_col TYPE i VALUE 100.

data: l_bukrs type bukrs,

l_matnr type matnr,

l_pocount type i.

constants: l_c_65536 type i value '65536'.

IF l_end_row IS INITIAL.

l_end_row = l_c_65536.

ENDIF.

FIELD-SYMBOLS : <fs_assign>.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = l_start_col

i_begin_row = l_start_row

i_end_col = l_end_col

i_end_row = l_end_row

TABLES

intern = l_it_excel

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE s003 WITH text-e02.

STOP.

ENDIF.

clear l_pocount.

loop at l_it_excel into l_wa_excel.

clear: l_matnr,

l_bukrs.

if l_wa_excel-col eq 1.

clear: l_index, l_index1.

if l_wa_excel-value+4(4) eq ' '.

move l_wa_excel-value to l_bukrs.

l_pocount = l_pocount + 1.

else.

move l_wa_excel-value to l_matnr.

endif.

endif.

move l_pocount to wa_excel-pocount.

move l_wa_excel-col to l_index.

if l_wa_excel-col eq 1.

if not l_bukrs is initial.

l_index1 = 1.

elseif l_matnr is not initial.

l_index1 = 5.

endif.

endif.

l_index = l_index + l_index1.

• move l_wa_excel-col to l_index.

assign component l_index of structure wa_excel to <fs_assign>.

move l_wa_excel-value to <fs_assign>.

at end of row.

• on change of wa_excel-werks.

if not wa_excel-werks is initial.

*Append the item record

append wa_excel to it_excel.

clear wa_excel.

else.

*Append the header record

append wa_excel to it_excel.

clear wa_excel.

endif.

endat.

endloop.

Regards,

Vijaymadhur.

[Former Member]

Hi Neha,

You said your excel sheet is created from open office... that is why all of these function modules throwing an error when you are try yo upload the data in to internal table... do one thing.. save that excel file as a notepad using tab delimiters option and then upload the data into internal table using GUI_UPLOAD...

hope this wil help you.

Regards,

Kiran