topic Re: read excel file in Application Development and Automation Discussions

read excel file

Former Member — Mon, 31 Dec 2007 10:00:52 GMT

i need to read an excel file into an internal table. i already used gui_download and ALSM_EXCEL_TO_INTERNAL_TABLE but my problem is not solved as my excel file has 12 field, out of which three fields are for comments and therefore of variable length.

Th above two mentioned FM can read per line onle 4096 characters but in my case it can be more than that also

please help me if you have any solution

Re: read excel file

Former Member — Mon, 31 Dec 2007 10:03:30 GMT

Hi ,

Did you tried this FM

TEXT_CONVERT_XLS_TO_SAP.

Regards Rk

Re: read excel file

JackGraus — Mon, 31 Dec 2007 10:06:28 GMT

What if you copy the code of ALSM_EXCEL_TO_INTERNAL_TABLE into your own program, it's only a limitted number of lines, and change the definition of your data lines to the suze of your excel sheet lines.

Regards Jack

Re: read excel file

Former Member — Mon, 31 Dec 2007 10:07:18 GMT

what does i_tab_raw_data imply here

Re: read excel file

Former Member — Mon, 31 Dec 2007 10:10:12 GMT

then it should of type string i suppose

as all the three fields are of variable length

and hence each field should be treated as string itself

how to solve this

Re: read excel file

Former Member — Mon, 31 Dec 2007 10:13:50 GMT

Hi,

That ITAB is used to store Raw data .

You just create an internal table of type TRUXS_T_TEXT_DATA

and assigh that table to the parameter (You need to declare type-pools TRUXS in your program )

Regards Rk

Re: read excel file

Former Member — Mon, 31 Dec 2007 10:20:00 GMT

Hi Sumesh,

try the following code.

There are also a couple of alternatives which use fucntion modules ‘KCD_EXCEL_OLE_TO_INT_CONVERT’

and ‘ALSM_EXCEL_TO_INTERNAL_TABLE’ but the method below is by far the simplest method to use.

*……………………………………………………..

*: Description :

*: ———– :

*: This is a simple example program to get data from an excel :

*: file and store it in an internal table. :

*: :

*: Author : www.sapdev.co.uk, based on code from Jayanta :

*: :

*: SAP Version : 4.7 :

*:……………………………………………………:

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.

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.

END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3.

ENDLOOP.

Reward if useful.

Thanks and Regards

Shilpi