topic Re: Inserting data from excel sheet in Application Development and Automation Discussions

Inserting data from excel sheet

Former Member — Fri, 05 Sep 2008 11:15:04 GMT

Hi freinds ,

Is there any method to upload data into a table from an external source .[excel sheet,notepad ,ascii file].

I am practising abap in the netweaver available at free download from this site .I just want to know that if create tables identical to standard sap tables [mara . makt ,vbap] i.e zmara ,zmakt ,zvbap .

Is there anymethod to upload data into them through excel sheet or notepad .

I had collected the data of these tables from sap ides in a excel sheet.

Re: Inserting data from excel sheet

Former Member — Fri, 05 Sep 2008 11:17:33 GMT

Yes you can .use FM GUI_UPLOAD to upload the file from presentation layer the use Update statement to update the table.

Re: Inserting data from excel sheet

Former Member — Fri, 05 Sep 2008 11:33:48 GMT

Hi,

You can use function modules

GUI_UPLOAD

ALSM_EXCEL_TO_INTERNAL_TABLE -- upload from excel file

regards

padma

Re: Inserting data from excel sheet

Former Member — Fri, 05 Sep 2008 11:35:21 GMT

Hi lokesh,

You can insert data from an excel file into an internal table.

First upload the data from an excel file into an internal table.

Then use this internal table to insert data in database table.

Have a look at this sample code, I've an excel file named flight in my D drive. This file has data of sflight table. I'm uploading it into my internal table.


REPORT  z_file_upload.

DATA:
  BEGIN OF fs_flight,
    carrid   LIKE sflight-carrid,
    connid   LIKE sflight-connid,
    fldate   LIKE sflight-fldate,
    price    LIKE sflight-price,
    currency LIKE sflight-currency,
  END OF fs_flight.

DATA:
  t_flight LIKE
     TABLE OF
           fs_flight.




CALL FUNCTION 'GUI_UPLOAD'
  EXPORTING
    filename  = 'D:\flight.xls'
   filetype                      = 'ASC'
   has_field_separator           = 'X'
*   HEADER_LENGTH                 = 0
*   READ_BY_LINE                  = 'X'
*   DAT_MODE                      = ' '
*   CODEPAGE                      = ' '
*   IGNORE_CERR                   = ABAP_TRUE
*   REPLACEMENT                   = '#'
* IMPORTING
*   FILELENGTH                    =
*   HEADER                        =
  TABLES
    data_tab                      = t_flight
 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 EQ 0.
  MESSAGE 'UPLOADING SUCCESSFUL' TYPE 'S'.
ENDIF.          .
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 t_flight INTO fs_flight.
  WRITE: / fs_flight-carrid,
           fs_flight-connid,
           fs_flight-fldate,
           fs_flight-price,
           fs_flight-currency.
ENDLOOP.

Regards

Abhijeet

Re: Inserting data from excel sheet

Former Member — Fri, 05 Sep 2008 11:35:51 GMT

Hi,

Here is the Complete report which uploads a text file from desktop to your internal table and then to SAP server.

REPORT Y_FROM_PRE_SRV_TO_SAP.

DATA:

BEGIN OF FS_SPFLI3,

LINE(1023) TYPE C,

END OF FS_SPFLI3.

DATA:

T_SPFLI3 LIKE

STANDARD TABLE

OF FS_SPFLI3.

DATA:

W_FILE2 TYPE STRING VALUE 'SPFLI_FILE2'.

*SELECT *

INTO TABLE T_SPFLI1

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'D:/SPFLI6.TXT'

FILETYPE = 'ASC'

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

TABLES

DATA_TAB = T_SPFLI3

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.

FROM SPFLI.

OPEN DATASET W_FILE2 FOR OUTPUT

IN TEXT MODE ENCODING DEFAULT.

LOOP AT T_SPFLI3 INTO FS_SPFLI3.

TRANSFER FS_SPFLI3-LINE TO W_FILE2.

ENDLOOP.

Regards,

Rama.

Re: Inserting data from excel sheet

Former Member — Sat, 06 Sep 2008 14:06:53 GMT

thanks friends for all your support .