topic Re: How to upload an excel file using ABAP. in Application Development and Automation Discussions

How to upload an excel file using ABAP.

Former Member — Fri, 07 Dec 2007 05:36:32 GMT

Hi,

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

Thanks!!

Re: How to upload an excel file using ABAP.

former_member195698 — Fri, 07 Dec 2007 05:39:30 GMT

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′. “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.

Re: How to upload an excel file using ABAP.

former_member386202 — Fri, 07 Dec 2007 05:39:57 GMT

Hi,

USe FM ALSM_EXCEL_TO_INTERNAL_TABLE

Regards,

Prashant

Re: How to upload an excel file using ABAP.

Former Member — Fri, 07 Dec 2007 05:42:02 GMT

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

Re: How to upload an excel file using ABAP.

Former Member — Fri, 07 Dec 2007 05:43:11 GMT

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.

Re: How to upload an excel file using ABAP.

former_member188829 — Fri, 07 Dec 2007 05:51:26 GMT

Hi,

check this link.

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

Re: How to upload an excel file using ABAP.

Former Member — Fri, 07 Dec 2007 05:52:54 GMT

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.

Re: How to upload an excel file using ABAP.

Former Member — Fri, 07 Dec 2007 07:04:00 GMT

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.