Excel in Appl Server to iTab in background mode

[Former Member]

Hi,

I need to transfer the content of an excel file in the application server to an internal table while running my program in background. My file is xls, cant use csv. For dataset bin mode, I need to know how to transfer the data to my internal table, already searched on forum but didnt find answer, at least not for background

any idea?

thanks,

Roberto

[amit_khare]

Hi,

check this thread I think it will solve your problem.

Regards,

Amit

Reward all helpful replies.

[Former Member]

no, it didnt, sorry

this link is for application server to excel, my problem is excel to internal table

[amit_khare]

I think you arer looking for this then.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = d_path

i_begin_col = begcol

i_begin_row = begrow

i_end_col = endcol

i_end_row = endrow

tables

intern = it_xls

• 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.

loop at it_xls.

case it_xls-col.

when '1'.

it_mat-matnr = it_xls-value.

when '2'.

it_mat-mtart = it_xls-value.

when '3'.

it_mat-meins = it_xls-value.

endcase.

at end of row.

append it_mat.

endat.

endloop.

Regards,

Amit

[Former Member]

Hi Roberto

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_TAB_DLUF[] "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.

The excel file should match the structure of your internal table including the mandt..

check this

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FNAME
I_BEGIN_COL = 1
I_BEGIN_ROW = 3
I_END_COL = 7
I_END_ROW = 32000
TABLES
INTERN = IT_FILE_UPLOAD
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.

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

There are also a couple of alternatives which use fu

nction modules 'KCD_EXCEL_OLE_TO_INT_CONVERT'

and 'ALSM_EXCEL_TO_INTERNAL_TABLE' but the method in this link is by far the simplest method to be used.

Regards

Naresh

[Former Member]

Hi,

Try this..

DATA: v_string TYPE string.

DATA: v_hex TYPE x VALUE '09'.

DATA: BEGIN OF itab OCCURS 0,

matnr TYPE matnr,

werks TYPE werks_d,

END OF itab.

OPEN DATASET '/tmp/test.xls' FOR INPUT.

CHECK sy-subrc = 0.

DO.

READ DATASET '/tmp/test.xls' INTO v_string.

IF sy-subrc <> 0.

EXIT.

ENDIF.

SPLIT v_string AT v_hex INTO itab-matnr itab-werks.

ENDDO.

CLOSE DATASET '/tmp/test.xls'.

Thanks,

Naren

[rahulkavuri]

hi

A correction, we cannot upload a excel file in the background mode, you can even check Rich Heilmans Answer in this thread

https://forums.sdn.sap.com/click.jspa?searchID=1219990&messageID=2096945

If its foreground mode then

check this program, this is the best I have seen in terms of performance

REPORT Y_UPLOAD

*Data Declaration

*----

---

DATA: ITAB LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

• Has the following format:

• Row number | Colum Number | Value

• ---------------------------------------

• i.e. 1 1 Name1

• 2 1 Joe

TYPES: BEGIN OF T_RECORD,

NAME1 LIKE ITAB-VALUE,

NAME2 LIKE ITAB-VALUE,

• age like itab-value,

END OF T_RECORD.

DATA: IT_RECORD TYPE STANDARD TABLE OF T_RECORD INITIAL SIZE 0,

WA_RECORD TYPE T_RECORD.

DATA: GD_CURRENTROW TYPE I.

*Selection Screen Declaration

*----

---

PARAMETER P_INFILE LIKE RLGRAP-FILENAME.

**********************AT SELECTION-SCREEN ON VALUE-REQUEST

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_INFILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = 'P_INFILE '

IMPORTING

FILE_NAME = P_INFILE.

************************************************************************

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_INFILE

I_BEGIN_COL = '1'

I_BEGIN_ROW = '1' "Do not require headings

I_END_COL = '2'

I_END_ROW = '33'

TABLES

INTERN = ITAB

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

MESSAGE E010(ZZ) WITH TEXT-001. "Problem uploading Excel Spreadsheet

ENDIF.

• Sort table by rows and colums

SORT ITAB BY ROW COL.

• Get first row retrieved

READ TABLE ITAB INDEX 1.

• Set first row retrieved to current row

GD_CURRENTROW = ITAB-ROW.

LOOP AT ITAB.

• Reset values for next row

IF ITAB-ROW NE GD_CURRENTROW.

APPEND WA_RECORD TO IT_RECORD.

CLEAR WA_RECORD.

GD_CURRENTROW = ITAB-ROW.

ENDIF.

CASE ITAB-COL.

WHEN '0001'. "First name

WA_RECORD-NAME1 = ITAB-VALUE.

WHEN '0002'. "Surname

WA_RECORD-NAME2 = ITAB-VALUE.

• when '0003'. "Age

• wa_record-age = itab-value.

ENDCASE.

ENDLOOP.

APPEND WA_RECORD TO IT_RECORD.

*!! Excel data is now contained within the internal table IT_RECORD

• Display report data for illustration purposes

LOOP AT IT_RECORD INTO WA_RECORD.

WRITE:/ SY-VLINE,

(10) WA_RECORD-NAME1, SY-VLINE,

(10) WA_RECORD-NAME2, SY-VLINE.

• (10) wa_record-age, sy-vline.

ENDLOOP.

Message was edited by:

Rahul Kavuri

[Former Member]

@Rahul Kavuri

the link to Rich Heilmans answer is broken

@Narendran

the v_hex cant be x type, any other way?

'TEXT_CONVERT_XLS_TO_SAP' and 'ALSM_EXCEL_TO_INTERNAL_TABLE' didnt work, maybe because after the data in xls there are fields with extra information, diffrent format than in the itab, Ill change it later and once it works or not I'll post here, thanks guys

[Former Member]

Hi Roberto

Use the following code

DATA:

dsn(20) VALUE '/usr/test.xls', " ur file path

rec(80). " internal table line type

OPEN DATASET dsn.

IF sy-subrc = 0.

DO.

READ DATASET dsn INTO rec.

IF sy-subrc <> 0.

EXIT.

ELSE.

WRITE / rec.

APPEND Rec TO it_rec.

ENDIF.

ENDDO.

ENDIF.

CLOSE DATASET dsn.

• rec is the line type of ur internal table

• use IF sy-batch = 'X'. to determin its running on bg,

Hope this will help.

regards

Nipuna

[rahulkavuri]

hi

check this code sample by Srilatha Thirukkovalluri, this is the way we can connect with Presentation Server in Background Mode

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/9831750a-0801-0010-1d9e-f8c64efb...

Otherwise its not possible as far as I know

Dont forget to mark helpful answers

[Former Member]

Hi,

I am in 4.6C..I tested my code..It works good if it is file is tab delimited..

Thanks,

Naren

[Former Member]

I am using v6 here

about the ALSM_EXCEL_TO_INTERNAL_TABLE, I get the upload_ole error all the time, and with TEXT_CONVERT_XLS_TO_SAP,

conversion_failed all the time too...

here is the code, just the load part and data declaration:

**********************************************************************

REPORT zbeto.

TYPE-POOLS: truxs.

TYPES:

BEGIN OF y_cot,

text1(12) TYPE c,

text2(12) TYPE c,

text3(12) TYPE c,

text4(12) TYPE c,

text5(12) TYPE c,

END OF y_cot.

DATA: it_datatab TYPE STANDARD TABLE OF y_cot,

wa_datatab TYPE y_cot,

it_raw TYPE truxs_t_text_data.

DATA:

v_file TYPE rlgrap-filename,

begin_col TYPE i VALUE '1',

begin_row TYPE i VALUE '2',

end_col TYPE i VALUE '5',

end_row TYPE i VALUE '102',

t_ctmp TYPE y_cot OCCURS 0 WITH HEADER LINE,

t_xls TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.

START-OF-SELECTION.

PERFORM f_load_xls.

**********************************************************************

• FORM : f_load_xls

**********************************************************************

FORM f_load_xls.

v_file = '
ZSAPDEV\SAPDEVINTERF$\COTACAO\TESTE.XLS'.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = v_file

i_begin_col = begin_col

i_begin_row = begin_row

i_end_col = end_col

i_end_row = end_row

TABLES

intern = t_xls

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.

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

t_ctmp[] = it_datatab[].

ENDFORM. "f_load_xls

**********************************************************************

both FM arent working, and when I try to execute in background mode, before anything, I recieve the following error:

Message text:

Error during import of clipboard contents

Message class:

ALSMEX

Message no.:

037

Message type:

A

and without the ASLM FM, the TEXT_CONVERT give me the conversion_failed exception...

the dierctories are right, that I am sure, because I just copy&paste it from CG3Z/CG3Y when I uploaded and downloaded to

check the file in the server...

am I missing something?

thanks again,

Roberto Macedo

Message was edited by:

Roberto Macedo

[Former Member]

Like i promised, here is my solution:

I'll use a third party program to convert the xls to csv in the server, I'll work the file, and then convert to xls again... once the development is finish, I give more details...

Thank you guys

Roberto

[Former Member]

I'm using v6

about the 'ALSM_EXCEL_TO_INTERNAL_TABLE', when I run it on line, i get the upload ole error, but thats not a problem cuz it wont run online, but when i try background, the program crashes, with an error "unable to copy from cliboard", and the error is in the function cuz without it, dont crash.

in 'TEXT_CONVERT_XLS_TO_SAP' all the time i get the error that wasnt possible to use the file, but the path is right, it is exactly the same that I use in CG3Z/CG3Y do upload and download the file...

any idea whats wrong? maybe access privileges?

thanks again

Roberto

[Former Member]

Hi,

One more thing...If you are trying to run the program in background..Then if you try to upload from a presentation server...It will not work..

Because the background process will be done in the application server ...The application server will not know which presentation server...

So to answer your question...The FMs will not work in background if you are trying to upload from a presentation server.

Thanks,

Naren.

[Former Member]

Hi Narendran

no, my program runs only in application server, have nothing do to with apresentation server.. the xls file is already in the server, all that i need to do is load the xls in my internal table while running in background mode...

did you take a look in the code? did I miss something?

thanks again,

Roberto