topic Re: upload the data from excel? in Application Development and Automation Discussions

upload the data from excel?

Former Member — Fri, 21 Apr 2006 09:32:31 GMT

Guys,

I want to upload the data from EXCEL to internal table.

which FM i have to use.

regards,

vijay

Re: upload the data from excel?

Former Member — Fri, 21 Apr 2006 09:33:29 GMT

hi Vijay,

Use <b>GUI_UPLOAD</b> FM

data : filename type string,

fl_type type char10 value 'DAT'.

FILENAME = 'C:\TEST.xls'.

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

EXPORTING

FILENAME = filename

FILETYPE = fl_type

HAS_FIELD_SEPARATOR = 'X'

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

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.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Regards,

Santosh

Re: upload the data from excel?

Former Member — Fri, 21 Apr 2006 09:34:22 GMT

Hi vijay,

use the FM ALSM_EXCEL_TO_INTERNAL_TABLE

Re: upload the data from excel?

Former Member — Fri, 21 Apr 2006 09:35:50 GMT

You can also use WS_UPLOAD, UPLOAD, ALSM_EXCEL_TO_INTERNAL_TABLE, etc.,

Re: upload the data from excel?

Former Member — Fri, 21 Apr 2006 09:36:22 GMT

Hi Vijay,

Please check the following function module:-

ALSM_EXCEL_TO_INTERNAL_TABLE

EECRM_GET_EXCELDATA_INTO_STRUC

Regards,

Sameena

Re: upload the data from excel?

Former Member — Fri, 21 Apr 2006 09:36:34 GMT

hi Vijay,

a sample..

data: itab like alsmex_tabline occurs 0 with header line.

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.

regards

satesh

Re: upload the data from excel?

Former Member — Fri, 21 Apr 2006 09:37:43 GMT

Hi,

You can save the xls file as Tab Delimited Text file

(*.txt) and then use GUI_UPLOAD function module.

You can also use CL_GUI_FRONTEND_SERVICES class.

Regards,

Shashank

Re: upload the data from excel?

Former Member — Fri, 21 Apr 2006 09:40:28 GMT

Hi,

Refer this code:

REPORT ZFIR0008 MESSAGE-ID ZFI01.

----

TABLE DECLARATION *

----

TABLES:

ZFI002," Decision approval rights for WBS and I-order plus Special Rights for CCtr

PRPS, "WBS (Work Breakdown Structure) Element Master Data

T001, "Company Codes

VBWF09,"People with Release Authorization for Document Parking

USR01. "User master record (runtime data).

----

INTERNAL TABLE DECLARATION *

----

DATA : BEGIN OF GT_DATA OCCURS 0.

INCLUDE STRUCTURE ZFI002.

DATA :END OF GT_DATA.

DATA : BEGIN OF GT_ERROR OCCURS 0,

BUKRS TYPE BUKRS,

ZFCOB TYPE ZZFCOB,

OBJID TYPE ZZFOBJID,

HWBIS TYPE HWBIS_WFFI,

FLAG TYPE I,

END OF GT_ERROR.

DATA : GT_FILE TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

----

CONSTANTS DECLARATION *

----

CONSTANTS: C_WBS(3) TYPE C VALUE 'WBS',

C_FOBJ(2) TYPE C VALUE 'US',

C_ROW(4) TYPE C VALUE '0001'.

----

VARIABLE DECLARATION *

----

DATA : GV_TEXT(100),

GV_INDEX LIKE SY-TABIX.

----

SELECTION SCREEN *

----

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-000.

PARAMETERS :P_FILE LIKE RLGRAP-FILENAME OBLIGATORY. "Excel file name

SELECTION-SCREEN END OF BLOCK B1.

----

AT SELECTION-SCREEN *

----

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

*To browse and get the file name

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = 'c:\ '

IMPORTING

FILE_NAME = P_FILE.

----

START OF SELECTION *

----

START-OF-SELECTION.

To upload the file from Presentation server

PERFORM UPLOAD_EXCEL.

Updation of Database Table

PERFORM READ_DATA.

&----

*& Form upload_excel

&----

text

FORM UPLOAD_EXCEL .

REFRESH GT_FILE.

REFRESH GT_DATA.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 4

I_END_ROW = 65536

TABLES

INTERN = GT_FILE

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

CASE SY-SUBRC.

WHEN 1.

MESSAGE E049.

WHEN 2.

MESSAGE E050.

WHEN OTHERS.

Do Nothing

ENDCASE.

SORT GT_FILE BY ROW COL.

ENDFORM. " upload_excel

&----

*& Form read_data

&----

text

FORM READ_DATA .

DATA: LV_COMMA TYPE C VALUE ',',

LV_NULL TYPE C VALUE '',

LV_DOT TYPE C VALUE '.'.

CLEAR GT_FILE.

CLEAR GT_DATA.

CLEAR GT_ERROR.

IF GT_FILE[] IS NOT INITIAL.

LOOP AT GT_FILE.

IF GT_FILE-ROW GT C_ROW.

CASE GT_FILE-COL.

WHEN '0001'.

SELECT SINGLE BUKRS

FROM T001

INTO GT_DATA-BUKRS

WHERE BUKRS = GT_FILE-VALUE.

IF SY-SUBRC <> 0.

MOVE GT_FILE-VALUE TO GT_ERROR-BUKRS.

IF GT_ERROR-FLAG IS INITIAL.

GT_ERROR-FLAG = 1.

ELSE.

GT_ERROR-FLAG = 5.

ENDIF.

WHEN '0002'.

SELECT SINGLE PSPNR

FROM PRPS

INTO GT_DATA-ZFCOB

WHERE POSKI EQ GT_FILE-VALUE.

IF SY-SUBRC <> 0.

MOVE GT_FILE-VALUE TO GT_ERROR-ZFCOB.

IF GT_ERROR-FLAG IS INITIAL.

GT_ERROR-FLAG = 2.

ELSE.

GT_ERROR-FLAG = 5.

ENDIF.

WHEN '0003'.

SELECT SINGLE BNAME

FROM USR01

INTO GT_DATA-OBJID

WHERE BNAME = GT_FILE-VALUE.

IF SY-SUBRC <> 0.

MOVE GT_FILE-VALUE TO GT_ERROR-OBJID.

IF GT_ERROR-FLAG IS INITIAL.

GT_ERROR-FLAG = 3.

ELSE.

GT_ERROR-FLAG = 5.

ENDIF.

WHEN '0004'.

REPLACE LV_COMMA IN GT_FILE-VALUE WITH LV_NULL.

REPLACE LV_DOT IN GT_FILE-VALUE WITH LV_NULL.

SELECT SINGLE HWBIS

FROM VBWF09

INTO GT_DATA-HWBIS

WHERE WFVAR = GT_DATA-BUKRS

AND HWBIS = GT_FILE-VALUE.

IF SY-SUBRC <> 0.

MOVE GT_FILE-VALUE TO GT_ERROR-HWBIS.

IF GT_ERROR-FLAG IS INITIAL.

GT_ERROR-FLAG = 4.

ELSE.

GT_ERROR-FLAG = 5.

ENDIF.

ENDCASE.

AT END OF ROW.

IF GT_ERROR-FLAG IS NOT INITIAL.

IF GT_DATA-BUKRS IS NOT INITIAL.

MOVE GT_DATA-BUKRS TO GT_ERROR-BUKRS.

ENDIF.

IF GT_DATA-ZFCOB IS NOT INITIAL.

GV_INDEX = SY-TABIX - 2.

READ TABLE GT_FILE INDEX GV_INDEX.

MOVE GT_FILE-VALUE TO GT_ERROR-ZFCOB.

ENDIF.

IF GT_DATA-OBJID IS NOT INITIAL.

MOVE GT_DATA-OBJID TO GT_ERROR-OBJID.

ENDIF.

IF GT_DATA-HWBIS IS NOT INITIAL.

MOVE GT_DATA-HWBIS TO GT_ERROR-HWBIS.

ENDIF.

APPEND GT_ERROR.

CLEAR GT_ERROR.

CLEAR GT_DATA.

ELSE.

MOVE C_WBS TO GT_DATA-ZFCOT.

MOVE C_FOBJ TO GT_DATA-ZFOBJ.

APPEND GT_DATA.

CLEAR GT_DATA.

CLEAR GT_FILE.

ENDIF.

ENDAT.

ENDIF.

ENDLOOP.

ELSE.

MESSAGE S045(ZFI01) .

LEAVE LIST-PROCESSING.

ENDIF.

IF GT_DATA[] IS NOT INITIAL.

Modify the custom table ZFI002 .

MODIFY ZFI002 FROM TABLE GT_DATA.

IF SY-SUBRC NE 0.

If the DB modification encounters an error, throw appropriate message.

ROLLBACK WORK.

MESSAGE S046(ZFI01). "Unable to insert values into the table

ELSE.

COMMIT WORK.

MESSAGE S047(ZFI01). "Records Updated.

ENDIF.

ELSE.

MESSAGE S048(ZFI01).

ENDIF.

IF GT_ERROR[] IS NOT INITIAL.

PERFORM DISPLAY_ERROR_RECORDS.

ENDIF.

ENDFORM. " read_data

&----

*& Form display_error_records

&----

Displaying Error records

FORM DISPLAY_ERROR_RECORDS .

WRITE : / TEXT-009.

ULINE /1(14).

WRITE :/1 TEXT-011.

SKIP.

WRITE :/3 TEXT-005, 10 TEXT-001, 20 TEXT-002,45 TEXT-003 , 63 TEXT-004 , 73 TEXT-013.

ULINE .

LOOP AT GT_ERROR .

CASE GT_ERROR-FLAG.

WHEN 1.

GV_TEXT = TEXT-006.

WHEN 2.

GV_TEXT = TEXT-007.

WHEN 3.

GV_TEXT = TEXT-010.

WHEN 4.

GV_TEXT = TEXT-008.

WHEN 5.

GV_TEXT = TEXT-012.

WHEN OTHERS.

Do nothing.

ENDCASE.

WRITE 😕 SY-TABIX LEFT-JUSTIFIED UNDER TEXT-005 ,GT_ERROR-BUKRS UNDER TEXT-001,

GT_ERROR-ZFCOB UNDER TEXT-002,GT_ERROR-HWBIS UNDER TEXT-003,

GT_ERROR-OBJID UNDER TEXT-004,GV_TEXT UNDER TEXT-013.

ENDLOOP.

ENDFORM. " display_error_records[/code]

Regards,

Gayathri

Re: upload the data from excel?

Former Member — Fri, 21 Apr 2006 10:21:48 GMT

Hi,

GUI_UPLOAD is the suitable FM for Uploading excel file to Internal Table.

Regards,

Neptune.M