topic Re: Read from Excel-files? in Application Development and Automation Discussions

Read from Excel-files?

Former Member — Fri, 06 Oct 2006 08:56:36 GMT

Hi,

Does anybody know if it is possible to read from Excel-files with ABAP? I would like to read a given cell in an excel-file.

Re: Read from Excel-files?

Former Member — Fri, 06 Oct 2006 09:54:47 GMT

Use the FM GUI_UPLOAD.

Check the sample code:

&----

*& Report Z_UPLOAD_MUNCPCODE *

*& *

&----

*& *

&----

REPORT z_upload_muncpcode.

PARAMETERS : p_fname LIKE rlgrap-filename.

TYPES: BEGIN OF ty_munc,

land1 TYPE tzone-land1,

zone1 TYPE tzone-zone1,

vtext TYPE tzont-vtext,

END OF ty_munc.

DATA: i_munc TYPE STANDARD TABLE OF ty_munc,

i_tzone TYPE STANDARD TABLE OF tzone,

i_tzont TYPE STANDARD TABLE OF tzont,

wa_munc TYPE ty_munc,

wa_tzone TYPE tzone,

wa_tzont TYPE tzont.

CONSTANTS: c_path TYPE char20 VALUE 'C:\',

c_mask TYPE char9 VALUE ',*.*,*.*.',

c_mode TYPE char1 VALUE 'O',

c_filetype TYPE char10 VALUE 'ASC',

c_x TYPE char01 VALUE 'X'.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fname.

*-- Browse Presentation Server

PERFORM f4_presentation_file.

START-OF-SELECTION..

*-- Read presentation server file

PERFORM f1003_pre_file.

LOOP AT i_munc INTO wa_munc.

wa_tzone-mandt = wa_tzont-mandt = sy-mandt.

wa_tzone-land1 = wa_tzont-land1 = wa_munc-land1.

wa_tzone-zone1 = wa_tzont-zone1 = wa_munc-zone1.

wa_tzont-spras = sy-langu.

wa_tzont-vtext = wa_munc-vtext.

APPEND wa_tzont TO i_tzont.

APPEND wa_tzone TO i_tzone.

CLEAR: wa_munc, wa_tzont, wa_tzone.

ENDLOOP.

END-OF-SELECTION.

~~Modify Table TZONT~~

PERFORM enqueue_table USING text-001.

MODIFY tzont FROM TABLE i_tzont.

PERFORM dequeue_table USING text-001.

~~Modify Table TZONE~~

PERFORM enqueue_table USING text-002.

MODIFY tzone FROM TABLE i_tzone.

PERFORM dequeue_table USING text-002.

WRITE: 'Tables TZONE & TZONT are updated'.

*&----

*& Form f4_presentation_file

*&----

*& F4 Help for presentation server

*&----

FORM f4_presentation_file .

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

def_path = c_path

mask = c_mask

mode = c_mode

title = text-001

IMPORTING

filename = p_fname

EXCEPTIONS

inv_winsys = 1

no_batch = 2

selection_cancel = 3

selection_error = 4

OTHERS = 5.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

ENDFORM. " f4_presentation_file

*&----

*& Form f1003_pre_file

*&----

*& Upload File

*&----

FORM f1003_pre_file .

DATA: lcl_filename TYPE string.

lcl_filename = p_fname.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = lcl_filename

filetype = c_filetype

has_field_separator = c_x

TABLES

data_tab = i_munc

EXCEPTIONS

file_open_error = 1

file_read_error = 2

no_batch = 3

gui_refuse_filetransfer = 4

invalid_type = 5

no_authority = 6

unknown_error = 7

bad_data_format = 8

header_not_allowed = 9

separator_not_allowed = 10

header_too_long = 11

unknown_dp_error = 12

access_denied = 13

dp_out_of_memory = 14

disk_full = 15

dp_timeout = 16

OTHERS = 17.

IF sy-subrc <> 0.

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

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

EXIT.

ENDIF.

ENDFORM. " f1003_pre_file

&----

*& Form enqueue_table

&----

*& Enqueue Table

&----

FORM enqueue_table USING p_tabname.

CALL FUNCTION 'ENQUEUE_E_TABLE'

EXPORTING

tabname = p_tabname

EXCEPTIONS

foreign_lock = 1

system_failure = 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.

ENDFORM. " enqueue_table

&----

*& Form dequeue_table

&----

*& Dequeue Table

&----

FORM dequeue_table USING p_tabname.

CALL FUNCTION 'DEQUEUE_E_TABLE'

EXPORTING

tabname = p_tabname.

ENDFORM. " dequeue_table

You can also use FM ALSM_EXCEL_TO_INTERNAL_TABLE

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.

MESSAGE e000(zo_spa) WITH text-063."Invalid File

ENDIF.

Regards,

Prakash.

Re: Read from Excel-files?

anversha_s — Fri, 06 Oct 2006 09:55:05 GMT

hi,

look this.

p_file -> urexcel file name

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = p_file

i_begin_col = 1

i_begin_row = 1

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.

or

look this one.

TYPE-POOLS truxs.

tables : ztable.

types: begin of t_tab,

col1(5) type c,

col2(5) type c,

col3(5) type c,

end of t_tab.

data : itab type standard table of t_tab,

wa type t_tab.

data it_type type truxs_t_text_data.

parameter p_file type rlgrap-filename.

data ttab type tabname.

at selection-screen on value-request for p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR

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_type

i_filename = p_file

tables

i_tab_converted_data = itab[]

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 itab into wa.

ztable-col1 = wa-col1.

ztable-col2 = wa-col2.

ztable-col3 = wa-col3.

modify ztable.

endloop.

rgds

anver

if hlped mark points

Message was edited by: Anversha s

Re: Read from Excel-files?

Former Member — Fri, 06 Oct 2006 11:27:35 GMT

Guys,

How come that the 'ALSM_EXCEL_TO_INTERNAL_TABLE' FM is not available in SE37? Should we put in some patches?

Re: Read from Excel-files?

Former Member — Fri, 06 Oct 2006 11:45:05 GMT

Which version are u using.

If this fm (ALSM_EXCEL_TO_INTERNAL_TABLE) not there use GUI_UPLOAD FM.

Regards,

Prakash.

Re: Read from Excel-files?

Former Member — Fri, 06 Oct 2006 12:10:05 GMT

Thanks, we have software components:

- SAP_ABA release 7 level 8,

- SAP_BASIS 7 level 8,

- ST-PI 2005_01_700 level 2.

Is it however possible to run GUI_UPLOAD with files on the application server and in background (since we need to do this?

Re: Read from Excel-files?

Former Member — Fri, 06 Oct 2006 12:31:16 GMT

GUI_UPLOAD is used to upload values from Presentation server and not from Application server. Also doesn't work in Background.

For uploading the data from application u have to use the Open dataset & READ Dataset.