Upload Excel Sheet

[Former Member]

Hi,

I want to upload an excel sheet directly into an internal table without converting it into an *.txt file.

I tried using FM WS_UPLOAD,UPLOAD. GUI_UPLOAD only support ASC and BIN filetype and WS_UPLOAD,UPLAOD supports DAT alongwith ASC and BIN.Is there any FM exclusively for uploading excel sheet ?Also what;s the difference between file types.

Thanks in advance.

Regards,

Akash Modi

Message was edited by: Craig Cmehil

[Former Member · Accepted solution]

Akash, as Serdar said, there is a way of importing data directly from the Excel Worksheet, by using the Ole (Object Linking and Embedding) technique.

From ABAP, you will start Microsoft Excel (it will act as an OLE Server), copy the contents of the sheet in the clipboard, close Excel, upload the clipboard into an internal table on the application server.

A brief example:

FORM IMPORT_EXCEL USING P_P_FILE.

DATA: EXCEL TYPE OLE2_OBJECT,

WORKBOOK TYPE OLE2_OBJECT,

SHEET TYPE OLE2_OBJECT,

CELL_START TYPE OLE2_OBJECT,

CELL_END TYPE OLE2_OBJECT,

RANGE TYPE OLE2_OBJECT.

• Start EXCEL

CREATE OBJECT EXCEL 'excel.application'.

IF SY-SUBRC NE 0.

WRITE TEXT-004. " 'Excel cannot be started'

ELSE.

• p_back is a parameter ('background execution?' type C)

IF P_BACK IS INITIAL.

SET PROPERTY OF EXCEL 'visible' = 1.

ENDIF.

• Open the file (specified in the parameter

• p_p_file LIKE rlgrap-filename

CALL METHOD OF EXCEL 'workbooks' = WORKBOOK.

CALL METHOD OF WORKBOOK 'open' EXPORTING #1 = P_P_FILE.

IF SY-SUBRC NE 0.

WRITE TEXT-005. " 'Cannot open the file'

ELSE.

• Open the first sheet in the workbook

CALL METHOD OF EXCEL 'worksheets' = SHEET EXPORTING #1 = 1.

IF SY-SUBRC NE 0.

WRITE TEXT-006. " 'Cannot select the first sheet'

ELSE.

CALL METHOD OF SHEET 'activate'.

• Select the specified range

• SO_LIN and SO_COL are select-option parameters

• for the start and end row and column

CALL METHOD OF SHEET 'Cells' = CELL_START

EXPORTING #1 = SO_LIN-LOW #2 = SO_COL-LOW.

CALL METHOD OF SHEET 'Cells' = CELL_END

EXPORTING #1 = SO_LIN-HIGH #2 = SO_COL-HIGH.

CALL METHOD OF SHEET 'range' = RANGE

EXPORTING #1 = CELL_START #2 = CELL_END.

CALL METHOD OF RANGE 'select'.

• Copy the range to clipboard

CALL METHOD OF RANGE 'copy'.

CALL FUNCTION 'CONTROL_FLUSH'

EXCEPTIONS

CNTL_SYSTEM_ERROR = 1

CNTL_ERROR = 2

OTHERS = 3.

• Import the range into the internal table

• (its structure should be known)

CALL FUNCTION 'CLPB_IMPORT'

TABLES

DATA_TAB = TBL_EXCEL

EXCEPTIONS

CLPB_ERROR = 1

OTHERS = 2.

IF SY-SUBRC NE 0.

WRITE TEXT-007. " 'Cannot import from clipboard'

ENDIF.

ENDIF.

ENDIF.

• Close EXCEL, without warnings...

SET PROPERTY OF EXCEL 'DisplayAlerts' = 0.

CALL METHOD OF WORKBOOK 'close'.

CALL METHOD OF EXCEL 'quit'.

FREE OBJECT EXCEL.

ENDIF.

• other processing, like

• write the count of imported lines etc...

ENDFORM. " IMPORT

[athavanraja · Accepted solution]

hi all,

There is a standard function which does the job (<b>OLE</b>).

<b>ALSM_EXCEL_TO_INTERNAL_TABLE</b>

This will return the data in a structure like

Row No. Column No. Value

We can then manipulate this and populate our itab depending on the requirement.

Regards

Raja

[Former Member]

hi akash,

try this function module ALSM_EXCEL_TO_INTERNAL_TABLE

hope this helps,

priya.

[Former Member]

hi AKASH,

For upload the excel sheet first convert it in to tab delimitted text file and then use GUI_UPLOAD fm ..

V_FILENAME = P_FILE.

CALL FUNCTION <b>'GUI_UPLOAD'</b>

EXPORTING

FILENAME = V_FILENAME

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = '|'

TABLES

DATA_TAB = ITAB

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.

ENDIF.

Regards,

Santosh

[athavanraja]

on august 2004 , the user has been told to use FM ALSM_EXCEL_TO_INTERNAL_TABLE

and on may 2006 you people come and tell them to use

ALSM_EXCEL_TO_INTERNAL_TABLE

(the same FM, same answer)

Whats the point? what are you trying to achieve?

Regards

Raja

[Former Member]

Enough folks no sense in repeating yourselves.

[gopi_narendra]

hi

u can use it using the function module ALSM_EXCEL_TO_INTERNAL_TABLE which returns values into a structure with row column value

gopi

[Former Member]

Hi,U can upload it using

FM :

'CALL FUNCTION '<b>TEXT_CONVERT_XLS_TO_SAP</b>'

EXPORTING

• I_FIELD_SEPERATOR = 'X'

• I_LINE_HEADER =

i_tab_raw_data = v_data

i_filename = filenam

tables

i_tab_converted_data = record.

• EXCEPTIONS

• CONVERSION_FAILED = 1

• OTHERS = 2

Declarations :

Where filenam is of type rlgrap-filename

and the field record is an Internal Table

and the filenam = 'C:\test01.xls'

[nablan_umar]

Hi Akash,

When you save your Excel, you can always saved as CSV file. This file in Window will be automatically loaded as Excel when you double-click it in Window Explorer. Then when upload using WS_UPLOAD/UPLOAD, use format DAT.

[ssimsekler]

Hi Akash

If it is so much required,

you can use <i>OLE automation techniques</i> to handle your task.

*--Serdar

[omar_dominguez]

There is no way to upload a excel file directly. YOu must save it as csv, dat or other text filer format.