topic Re: reading excel file in server in Application Development and Automation Discussions

reading excel file in server

Former Member — Wed, 21 Nov 2007 10:38:36 GMT

Has anyone tried reading data from excel file in server side to internal table.

advice pls.

--Bala

Re: reading excel file in server

former_member188829 — Wed, 21 Nov 2007 10:40:51 GMT

Hi,

Use the Function Module

ALSM_EXCEL_TO_INTERNAL_TABLE

Re: reading excel file in server

Former Member — Wed, 21 Nov 2007 10:41:57 GMT

SEE THIS LINK

http://www.sapdev.co.uk/file/filehome.htm

----

Topic: File Manager for UNIX environment *
*
Description: With internal system command you can get access to *
the UNIX. The program list files in the Unix *
environment and by using at line selecttion you are*
able to browse datasets. *
*
Authorization: None, be careful you have unlimited access to Unix.*
Check that you are able to use the internal system *
command. *
*
Parameters: Filename and path. *
*
Change of Check that you are able to use the internal system *
release command. *
*
Release: 4.6b *
*

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

REPORT ZUNIXFILES MESSAGE-ID Z1

NO STANDARD PAGE HEADING

LINE-COUNT 65

LINE-SIZE 132.

----

Global Variables. *

----

DATA: BEGIN OF TABL OCCURS 0,

LINE(2000),

END OF TABL.

DATA: PARCOM_LOC LIKE RS37A-LINE.

DATA: SIZE(10) TYPE C,

LINE LIKE TABL-LINE,

SUM(16) TYPE C.

DATA: FILE LIKE RS37A-LINE.

DATA: PATH(255) TYPE C.

----

Parameters. *

----

SELECTION-SCREEN SKIP 2.

PARAMETERS: PARCOM LIKE RS37A-LINE.

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

*AT SELECTION-SCREEN.

AT SELECTION-SCREEN.

CONDENSE: PARCOM NO-GAPS.

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

*TOP-OF-PAGE.

TOP-OF-PAGE.

PERFORM WRITE_PAGE_HEADING.

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

*INITIALIZATION.

INITIALIZATION.

PARCOM = '/usr/sap/*'. "Only default value

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

*START-OF-SELECTION.

START-OF-SELECTION.

PERFORM MAKE_LOC.

PERFORM COLLECT_DATA.

PERFORM FILE_LIST.

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

*AT LINE-SELECTION.

AT LINE-SELECTION.

CLEAR FILE.

FILE = SY-LISEL+55.

REPLACE '|' WITH SPACE INTO FILE.

IF SY-LISEL+1(1) = 'd'.

PATH = FILE.

PATH+200 = '/*'.

CONDENSE PATH NO-GAPS.

SUBMIT ZUNIXFILES

WITH PARCOM = PATH

AND RETURN.

ENDIF.

SUBMIT ZBROWSEFILE "Call the browse program

WITH PARCOM = FILE

AND RETURN.

----

FORM MAKE_LOC. *

----

FORM MAKE_LOC.

CLEAR: PARCOM_LOC.

PARCOM_LOC(7) = 'ls -ld '.

PARCOM_LOC+7(45) = PARCOM.

ENDFORM.

----

FORM COLLECT_DATA. *

----

FORM COLLECT_DATA.

REFRESH TABL.

CALL 'SYSTEM' ID 'COMMAND' FIELD PARCOM_LOC

ID 'TAB' FIELD TABL-SYS.

ENDFORM.

----

FORM FILE_LIST. *

----

FORM FILE_LIST.

FORMAT RESET.

CLEAR SUM.

LOOP AT TABL.

CLEAR LINE.

LINE = TABL-LINE.

WRITE /2 LINE.

PERFORM VERTICAL_LINES.

SUM = SUM + SIZE.

ENDLOOP.

WRITE:/1(132) SY-ULINE.

ENDFORM.

----

FORM WRITE_PAGE_HEADING. *

----

FORM WRITE_PAGE_HEADING.

WRITE:/1(132) SY-ULINE.

PERFORM VERTICAL_LINES.

FORMAT INTENSIFIED.

WRITE: /2(131) SPACE COLOR 1.

WRITE: 2 SY-DATUM COLOR 1.

WRITE: 35 'File Manager' COLOR 1.

PERFORM VERTICAL_LINES.

WRITE:/1(132) SY-ULINE.

PERFORM VERTICAL_LINES.

FORMAT INTENSIFIED.

WRITE: /2(131) SPACE COLOR 3.

PERFORM VERTICAL_LINES.

FORMAT RESET.

WRITE:/1(132) SY-ULINE.

ENDFORM.

----

FORM VERTICAL_LINES. *

----

FORM VERTICAL_LINES.

PERFORM WRITE_VLINE USING ' ' 1.

PERFORM WRITE_VLINE USING ' ' 132.

ENDFORM.

----

WRITE_VLINE. *

----

FORM WRITE_VLINE USING MODE POS.

IF MODE NE 'T'.

FORMAT INTENSIFIED.

ENDIF.

POSITION POS.

WRITE: SY-VLINE.

IF MODE NE 'T'.

FORMAT RESET.

ENDIF.

ENDFORM.

Re: reading excel file in server

Former Member — Wed, 21 Nov 2007 10:42:50 GMT

Use FM GUI_DOWNLOAD or even better

FM ALSM_EXCEL_TO_INTERNAL_TABLE

Pass the path of the file to these FMs n ull get data in an internal table

Hope dis helps..

Reward if it does

Re: reading excel file in server

Former Member — Wed, 21 Nov 2007 10:44:33 GMT

Hi,

Check this thread..

https://forums.sdn.sap.com/click.jspa?searchID=6837178&messageID=3447522

Regards,

Omkar.

Re: reading excel file in server

former_member386202 — Wed, 21 Nov 2007 10:46:41 GMT

Hi,

Use FM " ALSM_EXCEL_TO_INTERNAL_TABLE ".

Reward Poiunts...

Regards,

Prashant

Re: reading excel file in server

Former Member — Wed, 21 Nov 2007 11:38:30 GMT

Try this.

to upload an excel file into internal table use these function modules :

to get the name of excel file from system use :

TYPES : BEGIN OF STRUCT,

TEXT(1000_ TYPE C,

END OF STRUCT.

data : v_row type i,

v_col type i.

DATA: IFILE LIKE RLGRAP-FILENAME.

DATA : ITAB_TEXT TYPE STANDARD TABLE OF STRUCT WITH HEADER LINE,

itab_upld type standard table of ALSMEX_TABLINE with header line.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

PROGRAM_NAME = SYST-REPID

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME =
STATIC = ' X'
MASK = '*.txt'

CHANGING

FILE_NAME = IFILE

EXCEPTIONS
MASK_TOO_LONG = 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.

To upload the data form excel file use this funcn module :

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = IFILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 47

I_END_ROW = 65536

TABLES

INTERN = ITAB_UPLD

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.

*Sort table itab_upld by rows and columns.

SORT ITAB_UPLD[] BY ROW COL.

APPEND ITAB_TEXT.

LOOP AT ITAB_UPLD.

AT NEW ROW.

MOVE ITAB_UPLD-VALUE TO ITAB_TEXT.

V_ROW = V_ROW + 1.

V_COL = 1.

IF SY-TABIX NE 1.

APPEND ITAB_TEXT.

ENDIF.

ENDAT.

hope this helps.

Reward if useful

Re: reading excel file in server

Former Member — Wed, 21 Nov 2007 11:47:12 GMT

<b>HI

File transfer from frontend PC to an internal backend table.

A format conversion is possible. The functions are similar to the module WS_UPLOAD, however the data provider is used for the upload and not GMUX.

Example

CALL FUNCTION 'GUI_UPLOAD'

exporting

filetype = 'BIN'

filename = 'C:\DOWNLOAD.BIN'

tables

data_tab = itab.

loads the contents of the file 'C:\DOWNLOAD.BIN' on the frontend PC into the internal backend table itab. The transferred data is not converted.

CALL FUNCTION 'GUI_UPLOAD'

exporting

filetype = 'ASC'

filename = 'C:\DOWNLOAD.TXT'

tables

data_tab = itab.

loads the contents of the file 'C:\DOWNLOAD.TXT' on the frontend PC line by line into the internal backend table itab. The transferred data is converted.

Or else

You can try with this fm also TEXT_CONVERT_XLS_TO_SAP

TYPE-POOLS truxs.

DATA: Begin of i_articles occurs 0,

matnr like mara-matnr,

flag(6) type c,

End of i_articles.

DATA: v_file type string,

i_temp type truxs_t_text_data.

selection-screen begin of block b1.

parameters: fl_file like rlgrap-filename lower case.

selection-screen end of block b1.

FORM UploadData.

v_file = fl_file.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = v_file

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

HEADER_LENGTH = 0
READ_BY_LINE = 'X'

DAT_MODE = ' '

IMPORTING
FILELENGTH =
HEADER =

TABLES

DATA_TAB = i_temp

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.

ENDIF.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR = 'X'

I_LINE_HEADER =

I_TAB_RAW_DATA = i_temp

I_FILENAME = fl_file

TABLES

I_TAB_CONVERTED_DATA = i_articles

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.

ENDFORM. " UploadData

Check my previous replies here u can get answer

Regards

Santosh K.</b>

Re: reading excel file in server

former_member312987 — Thu, 06 Dec 2007 09:10:24 GMT

You could try the function HR_PK_EXCEL_TO_INTERNAL_TABLE.

Best Regards

RMB

Re: reading excel file in server

Former Member — Thu, 06 Dec 2007 09:28:54 GMT

I am getting an error, say function module does not exist.

Kindly give more info.

Re: reading excel file in server

Former Member — Thu, 06 Dec 2007 09:32:01 GMT

Hi balaji,

Just try this program.

In this program i used the text document,

You can use the excel file also.

REPORT ZGUPLOAD_AL11 NO STANDARD PAGE HEADING LINE-SIZE 255.

TYPES : BEGIN OF STR,

ZAPARTNO(5),

ZLOCATION(20),

ZTOTALNOFLATS(4),

ZFLATSFILLED(4),

ZPHONENO(10),

END OF STR.

DATA : IT_TAB TYPE STANDARD TABLE OF STR,

WA_TAB TYPE STR,

V_FPATH TYPE STRING,

V_PATH(72) TYPE C VALUE 'F:\usr\sap\put\uplfile.dat',

RECORD TYPE C.

PARAMETERS FILENAME TYPE IBIPPARMS-PATH OBLIGATORY.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR FILENAME .

PERFORM GETFILENAME USING FILENAME .

V_FPATH = FILENAME .

START-OF-SELECTION.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD

EXPORTING

FILENAME = V_FPATH

FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = SPACE
HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = SPACE
CODEPAGE = SPACE
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
VIRUS_SCAN_PROFILE =
IMPORTING
FILELENGTH =
HEADER =

CHANGING

DATA_TAB = IT_TAB

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
NOT_SUPPORTED_BY_GUI = 17
ERROR_NO_GUI = 18
others = 19

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

OPEN DATASET V_PATH FOR OUTPUT IN BINARY MODE .

IF SY-SUBRC = 0.

LOOP AT IT_TAB INTO WA_TAB .

TRANSFER WA_TAB TO V_PATH.

ENDLOOP.

ENDIF.

CLOSE DATASET V_PATH.

FORM GETFILENAME USING FILENAME .

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG

DYNPRO_NUMBER = SYST-DYNNR

IMPORTING

FILE_NAME = FILENAME .

ENDFORM.

END-OF-SELECTION.

WRITE:/'FILE IS CREATED IN THE APPLICATION SERVER'.

Re: reading excel file in server

Former Member — Thu, 06 Dec 2007 09:51:15 GMT

It throws error.

Pls try with excel and reply.