reading excel file in server

[Former Member]

Hi

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

advice pls.

--Bala

[Former Member]

Hi

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

advice pls.

--Bala

[former_member188829]

Hi,

Use the Function Module

ALSM_EXCEL_TO_INTERNAL_TABLE

[Former Member]

hi

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.

[Former Member]

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

[Former Member]

Hi,

Check this thread..

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

Regards,

Omkar.

[former_member386202]

Hi,

Use FM " ALSM_EXCEL_TO_INTERNAL_TABLE ".

Reward Poiunts...

Regards,

Prashant

[Former Member]

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

[Former Member]

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

[former_member312987]

Hi

You could try the function HR_PK_EXCEL_TO_INTERNAL_TABLE.

Best Regards

RMB

[Former Member]

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

Kindly give more info.

[Former Member]

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

[Former Member]

It throws error.

Pls try with excel and reply.