topic Re: upload excel in Application Development and Automation Discussions

upload excel

Former Member — Wed, 30 Nov 2005 12:56:54 GMT

Hi!!

I have to upload an excel file and i can´t transform it in tabulated file and my excel file has cells that have more than one line, they can have paragraphs, i have tried to use the "gui_upload" but when i use the type 'DAT' it returns me the error INVALID_TYPE, and when i put the type 'ASC' it returns me the error BAD_DATA_FORMAT, how can i upload an excel file in XLS format?, or can i use another Fm for doing this?

Thanks in advance

Re: upload excel

Former Member — Wed, 30 Nov 2005 12:57:57 GMT

I can´t use alsm_excel_to_internal_table because of the length of the cells,

Thanks

Re: upload excel

Former Member — Wed, 30 Nov 2005 13:01:12 GMT

Hi Carl,

see the below sample code to do it.


   DATA : dl_begin_col TYPE i VALUE 1,     " Col No
         dl_begin_row TYPE i VALUE 1,     " Row no
         dl_end_col   TYPE i VALUE 8,     " Col No
         dl_end_row   TYPE i VALUE 65536, " Row no
         dl_index TYPE i.                 " Index
  FIELD-SYMBOLS <fs> TYPE ANY.     " Field Symbols
* Call function for Upload
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       EXPORTING
            filename                = p_file
            i_begin_col             = dl_begin_col
            i_begin_row             = dl_begin_row
            i_end_col               = dl_end_col
            i_end_row               = dl_end_row
       TABLES
            intern                  = dt_intern
       EXCEPTIONS
            inconsistent_parameters = 1
            upload_ole              = 2
            OTHERS                  = 3.

  IF sy-subrc <> 0.
*   Error Message - Invalid entry
    MESSAGE i999 WITH 'Error in opening File'(004).
    STOP.
  ENDIF.
  IF dt_intern[] IS INITIAL.

*   Error Message - Invalid entry
    MESSAGE i999 WITH 'No data in the file'(005).
    STOP.
  ELSE.
    SORT dt_intern BY row col.
*   Move the data into the internal table.
    LOOP AT dt_intern.
      MOVE dt_intern-col TO dl_index.
*     Assign component index of structure dt_TRUCK_data to field symbol.
      ASSIGN COMPONENT dl_index OF STRUCTURE dt_users TO <fs>.
      IF sy-subrc = 0.
        MOVE dt_intern-value TO <fs>.
      ENDIF.
      AT END OF row.
        APPEND dt_users.
        CLEAR dt_users.
      ENDAT.
    ENDLOOP.
  ENDIF.

What is the length of the filed you are trying to read.

if it is more than 50 you cant' use this.

Then ignore my answer.

Thanks & Regards,

Siri.

Message was edited by: Srilatha T

Re: upload excel

Former Member — Wed, 30 Nov 2005 13:11:12 GMT

Hi Carl,

Have a look at this link before writing a program for excel upload.

http://www.ezinearticles.com/?Upload-Excel-Data-to-SAP:-7-Best-Practices&id=91258

Refer this program GREXCEL1.

FILA_HELP_UPLOAD_EXCEL_01

FILA_HELP_UPLOAD_EXCEL_03

Regards,

Raj

Message was edited by: Rajasekhar Dinavahi

Re: upload excel

Former Member — Wed, 30 Nov 2005 13:29:43 GMT

My lines can be up to 1000 characters, so i can´t use alm_excel_to_internal_table and i have tried to upload with ws_upload in DAT format and it upload me unreadable characters in my internal table.

Thanks

Re: upload excel

Former Member — Wed, 30 Nov 2005 14:07:09 GMT

Hi Carl,

Try this FM code:

*****

Source code for function module:

type-pools: ole2.

types: ty_d_itabvalue type zalsmex_tabline-value,

internal table containing the excel data

ty_t_itab type zalsmex_tabline occurs 0,

line type of sender table

begin of ty_s_senderline,

line(10000) type c,

end of ty_s_senderline,

sender table

ty_t_sender type ty_s_senderline occurs 0.

constants: gc_esc value '"'.

FUNCTION z06_ole .

*"----

""Local interface:

*" IMPORTING

*" VALUE(FILENAME) LIKE RLGRAP-FILENAME

*" VALUE(I_BEGIN_COL) TYPE I

*" VALUE(I_BEGIN_ROW) TYPE I

*" VALUE(I_END_COL) TYPE I

*" VALUE(I_END_ROW) TYPE I

*" TABLES

*" INTERN STRUCTURE ZALSMEX_TABLINE

*" EXCEPTIONS

*" INCONSISTENT_PARAMETERS

*" UPLOAD_OLE

*"----

DATA: excel_tab TYPE ty_t_sender.

DATA: ld_separator TYPE c.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

range TYPE ole2_object,

worksheet TYPE ole2_object.

DATA: h_cell TYPE ole2_object,

h_cell1 TYPE ole2_object.

DATA:

ld_rc TYPE i.

DEFINE m_message.

case sy-subrc.

when 0.

when 1.

message id sy-msgid type sy-msgty number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

when others. raise upload_ole.

endcase.

END-OF-DEFINITION.

check parameters

IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.

IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.

Get TAB-sign for separation of fields

CLASS cl_abap_char_utilities DEFINITION LOAD.

ld_separator = cl_abap_char_utilities=>horizontal_tab.

open file in Excel

IF application-header = space OR application-handle = -1.

CREATE OBJECT application 'Excel.Application'.

m_message.

ENDIF.

CALL METHOD OF application 'Workbooks' = workbook.

m_message.

CALL METHOD OF workbook 'Open'

EXPORTING

#1 = filename.

m_message.

set property of application 'Visible' = 1.
m_message.

GET PROPERTY OF application 'ACTIVESHEET' = worksheet.

m_message.

mark whole spread sheet

CALL METHOD OF worksheet 'Cells' = h_cell

EXPORTING

#1 = i_begin_row

#2 = i_begin_col.

m_message.

CALL METHOD OF worksheet 'Cells' = h_cell1

EXPORTING

#1 = i_end_row

#2 = i_end_col.

m_message.

CALL METHOD OF worksheet 'RANGE' = range

EXPORTING

#1 = h_cell

#2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

copy marked area (whole spread sheet) into Clippboard

CALL METHOD OF range 'COPY'.

m_message.

read clipboard into ABAP

CALL METHOD cl_gui_frontend_services=>clipboard_import

IMPORTING

data = excel_tab

EXCEPTIONS

cntl_error = 1

ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

IF sy-subrc <> 0.

MESSAGE a037(alsmex).

ENDIF.

PERFORM separated_to_intern_convert TABLES excel_tab intern

USING ld_separator.

clear clipboard

REFRESH excel_tab.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = excel_tab

CHANGING

rc = ld_rc

EXCEPTIONS

cntl_error = 1

ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

quit Excel and free ABAP Object - unfortunately, this does not kill
the Excel process

CALL METHOD OF application 'QUIT'.

m_message.

>>>>> Begin of change note 575877
to kill the Excel process it's necessary to free all used objects

FREE OBJECT h_cell. m_message.

FREE OBJECT h_cell1. m_message.

FREE OBJECT range. m_message.

FREE OBJECT worksheet. m_message.

FREE OBJECT workbook. m_message.

FREE OBJECT application. m_message.

<<<<< End of change note 575877

ENDFUNCTION.

&----

*& Form separated_to_intern_convert

&----

text

----

-->I_TAB text
-->I_INTERN text
-->I_SEPARATORtext

----

FORM separated_to_intern_convert TABLES i_tab TYPE ty_t_sender

i_intern TYPE ty_t_itab

USING i_separator TYPE c.

DATA: l_sic_tabix LIKE sy-tabix,

l_sic_col TYPE kcd_ex_col.

DATA: l_fdpos LIKE sy-fdpos.

REFRESH i_intern.

LOOP AT i_tab.

l_sic_tabix = sy-tabix.

l_sic_col = 0.

WHILE i_tab CA i_separator.

l_fdpos = sy-fdpos.

l_sic_col = l_sic_col + 1.

PERFORM line_to_cell_separat TABLES i_intern

USING i_tab l_sic_tabix l_sic_col

i_separator l_fdpos.

ENDWHILE.

IF i_tab <> space.

CLEAR i_intern.

i_intern-row = l_sic_tabix.

i_intern-col = l_sic_col + 1.

i_intern-value = i_tab.

APPEND i_intern.

ENDIF.

ENDLOOP.

ENDFORM. " SEPARATED_TO_INTERN_CONVERT

----

FORM line_to_cell_separat TABLES i_intern TYPE ty_t_itab

USING i_line

i_row LIKE sy-tabix

ch_cell_col TYPE kcd_ex_col

i_separator TYPE c

i_fdpos LIKE sy-fdpos.

DATA: l_string TYPE ty_s_senderline.

DATA l_sic_int TYPE i.

CLEAR i_intern.

l_sic_int = i_fdpos.

i_intern-row = i_row.

l_string = i_line.

i_intern-col = ch_cell_col.

csv Dateien mit separator in Zelle: --> ;"abc;cd";

IF ( i_separator = ';' OR i_separator = ',' ) AND

l_string(1) = gc_esc.

PERFORM line_to_cell_esc_sep USING l_string

l_sic_int

i_separator

i_intern-value.

ELSE.

IF l_sic_int > 0.

i_intern-value = i_line(l_sic_int).

ENDIF.

IF l_sic_int > 0.

APPEND i_intern.

ENDIF.

l_sic_int = l_sic_int + 1.

i_line = i_line+l_sic_int.

ENDFORM. "line_to_cell_separat

----

FORM line_to_cell_esc_sep USING i_string

i_sic_int TYPE i

i_separator TYPE c

i_intern_value .

DATA: l_int TYPE i,

l_cell_end(2).

FIELD-SYMBOLS: <l_cell>.

l_cell_end = gc_esc.

l_cell_end+1 = i_separator .

IF i_string CS gc_esc.

i_string = i_string+1.

IF i_string CS l_cell_end.

l_int = sy-fdpos.

ASSIGN i_string(l_int) TO <l_cell>.

i_intern_value = <l_cell>.

l_int = l_int + 2.

i_sic_int = l_int.

i_string = i_string+l_int.

ELSEIF i_string CS gc_esc.

letzte Celle

l_int = sy-fdpos.

ASSIGN i_string(l_int) TO <l_cell>.

i_intern_value = <l_cell>.

l_int = l_int + 1.

i_sic_int = l_int.

i_string = i_string+l_int.

l_int = STRLEN( i_string ).

IF l_int > 0 . MESSAGE x001(kx) . ENDIF.

ELSE.

MESSAGE x001(kx) . "was ist mit csv-Format

ENDIF.

ENDFORM. "line_to_cell_esc_sep

Table for function module

INTERN like ZALSMEX_TABLINE

Import parameters for function module

FILENAME like rlgrap-filename

I_BEGIN_COL type i

I_BEGIN_ROW type i

I_END_COL type i

I_END_ROW type i

*****

Alternatively, you can check this link...

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

Regards,

Raj

Re: upload excel

andreas_mann3 — Wed, 30 Nov 2005 14:25:19 GMT

Hi Carlson,

why don't you save it as <b>csv</b>-file ?

Andreas

Re: upload excel

Former Member — Wed, 30 Nov 2005 14:46:38 GMT

Hi!,

I have been watching the methods too, and i can´t be able to find a method that can read so long cell in excel, i am going to convert the file in tabbed separated files or csv,

Thank you very much