How to upload an excel file using ABAP.

[Former Member]

Hi,

Can anyone please help me in understanding how to upload an excel file using ABAP.

Thanks!!

[former_member195698]

http://diocio.wordpress.com/2007/02/12/sap-upload-excel-document-into-internal-table/

check the link

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.

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

*START OF SELECTION

call function ‘ALSM_EXCEL_TO_INTERNAL_TABLE’

exporting

filename = p_infile

i_begin_col = ‘1′

i_begin_row = ‘2′ “Do not require headings

i_end_col = ‘14′

i_end_row = ‘31′

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&#8242;. “First name

wa_record-name1 = itab-value.

when ‘0002&#8242;. “Surname

wa_record-name2 = itab-value.

when ‘0003&#8242;. “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.

[former_member386202]

Hi,

USe FM ALSM_EXCEL_TO_INTERNAL_TABLE

Regards,

Prashant

[Former Member]

Hi,

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.

You can also refer to this link

[Former Member]

Hi WASIM The below CODE should help you with ur query,

TYPE-POOLS: truxs.

DATA: i_text_data TYPE truxs_t_text_data,

v_filename_string TYPE string.

DATA: BEGIN OF itab OCCURS 0,

Name(30),

Phone(15),

Fax(500).

DATA: END OF itab.

PARAMETERS: p_file LIKE rlgrap-filename.

START-OF-SELECTION.

v_filename_string = p_file.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = v_filename_string

filetype = 'ASC'

has_field_separator = 'X'

• HEADER_LENGTH = 0

• READ_BY_LINE = 'X'

dat_mode = ''

• IMPORTING

• FILELENGTH =

• HEADER =

TABLES

data_tab = i_text_data

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_text_data

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.

this is a sample code that uploads a excel file using GUI_UPLOAD, but uses another function module to convert that uploaded data into an internal table..

<b>Kindly Reward points if you found the reply helpful</b>.

Cheers,

CHAITANYA.

[former_member188829]

Hi,

check this link.

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

[Former Member]

Hi

see this program in this

EXCEL TO INTERNAL TABLE AND THEN TO APPLICATION SERVER

REPORT  ZSD_EXCEL_INT_APP.

parameter: file_nm type localfile.

types : begin of it_tab1,
        f1(20),
        f2(40),
        f3(20),
       end of it_tab1.


data : it_tab type table of ALSMEX_TABLINE with header line,
       file type rlgrap-filename.

data : it_tab2 type it_tab1 occurs 1,
       wa_tab2 type it_tab1,
       w_message(100)  TYPE c.


at selection-screen on value-request for file_nm.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
 EXPORTING
*   PROGRAM_NAME        = SYST-REPID
*   DYNPRO_NUMBER       = SYST-DYNNR
*   FIELD_NAME          = ' '
   STATIC              = 'X'
*   MASK                = ' '
  CHANGING
   file_name           = file_nm
 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.


start-of-selection.

refresh it_tab2[].clear wa_tab2.

file = file_nm.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                      = file
    i_begin_col                   = '1'
    i_begin_row                   =  '1'
    i_end_col                     = '10'
    i_end_row                     = '35'
  tables
    intern                        = it_tab
 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_tab.

  case it_tab-col.

   when '002'.

    wa_tab2-f1 = it_tab-value.

   when '004'.

    wa_tab2-f2 = it_tab-value.

  when '008'.

    wa_tab2-f3 = it_tab-value.

 endcase.

 at end of row.

  append wa_tab2 to it_tab2.
 clear wa_tab2.
  endat.

endloop.

data : p_file TYPE  rlgrap-filename value 'TEST3.txt'.


OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
*--- Display error messages if any.
  IF sy-subrc NE 0.
    MESSAGE e001(zsd_mes).
    EXIT.
  ELSE.

*---Data is downloaded to the application server file path
    LOOP AT it_tab2 INTO wa_tab2.
      TRANSFER wa_tab2 TO p_file.
    ENDLOOP.
  ENDIF.

*--Close the Application server file (Mandatory).
  CLOSE DATASET p_file.

 loop at it_tab2 into wa_tab2.

  write : / wa_tab2-f1,wa_tab2-f2,wa_tab2-f3.

 endloop.

[Former Member]

By using the standard function ALSM_EXCEL_TO_INTERNAL_TABLE, you can import most Excel documents into an SAP internal table. Please note that this code is set up for spreadsheets up to 9999 rows. If you need more, use a 'DO' statement to loop through the Excel file in 9999 row segments until all is retreived

The code runs on 4.6B.

Code

DATA:

DATA LIKE ALSMEX_TABLINE OCCURS 100,

"ALSM_EXCEL_TO_INTERNAL_TABLE structure

BCOL TYPE I VALUE 1,

"This can be any Column in your

" spreadsheet where you want to start

" importing data

BROW TYPE I VALUE 1,

"This can be any Row in your

" spreadsheet where you want to start

" importing data

ECOL TYPE I VALUE 9,

"This can be any Column in your

" spreadsheet where you want to stop

" importing data

EROW TYPE I VALUE 9999.

"This can be any Row in your

" spreadsheet where you want to stop

" importing data

"There is little hurt to enter a larger

"number for the upper limit of the rows

"or columns. It may take a few extra

"seconds to import, but only those

"fields who's values are not equal

"to ' ', space, will be imported.

DATA: UPFILE(128) VALUE 'C:/'.

"You should provide the user a popup

" window to allow them the option to

" select a file from their PC or network

"Call to import Excel document

CALL FUNCTION

'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = UPFILE

I_BEGIN_COL = BCOL

I_BEGIN_ROW = BROW

I_END_COL = ECOL

I_END_ROW = EROW

TABLES

INTERN = DATA

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.

Award points if found useful.

Cheers,

Chandra Sekhar.