topic Re: excel data into database in Application Development and Automation Discussions

excel data into database

Former Member — Thu, 10 Apr 2008 08:02:20 GMT

hai ,

please help me to find out how can i convert excel file and store the data into data base

Re: excel data into database

Former Member — Thu, 10 Apr 2008 08:14:55 GMT

Hi,

report  ztims_partsmupd.

tables:zparts_mattgt,mara.
data:begin of it_parts occurs 0,
       date(10),
       regio(3),
       kunnr(10),
       matnr_i(18),
       desc(40),
       kwmeng_i(16),
      end of it_parts.

call function 'UPLOAD'
  exporting

    filename = 'C:\'
    filetype = 'DAT'
  tables
    data_tab = it_parts.

loop at it_parts.
  if it_parts-date+3(2)  = '04' or
     it_parts-date+3(2)  = '05' or
     it_parts-date+3(2)  = '06'.
    zparts_mattgt-quarter = '1'.
  elseif it_parts-date+3(2) = '07' or
         it_parts-date+3(2)  ='08'.
    zparts_mattgt-quarter = '2'.
  elseif it_parts-date+3(2) = '09' or
         it_parts-date+3(2) = '10' or
         it_parts-date+3(2) = '11' or
         it_parts-date+3(2) = '12'.
    zparts_mattgt-quarter = '3'.
  elseif it_parts-date+3(2) = '01' or
         it_parts-date+3(2) = '02' or
         it_parts-date+3(2) = '03'.
    zparts_mattgt-quarter = '4'.
  endif.

  if it_parts-kunnr ca sy-abcde.
  else.
    unpack it_parts-kunnr to it_parts-kunnr.
  endif.
  if it_parts-matnr_i ca sy-abcde.
  else.
    unpack it_parts-matnr_i to it_parts-matnr_i.
  endif.
  select single * from mara where matnr = it_parts-matnr_i.
  zparts_mattgt-zyear   = it_parts-date+6(4).
  zparts_mattgt-zmonth  = it_parts-date+3(2).
  zparts_mattgt-kunnr   = it_parts-kunnr.
  zparts_mattgt-zpartno  = it_parts-matnr_i.
  zparts_mattgt-qnty    = it_parts-kwmeng_i.
  zparts_mattgt-regio   = it_parts-regio.
  zparts_mattgt-ernam   = sy-uname.
  zparts_mattgt-erdat   = sy-datum.
  zparts_mattgt-meins   = mara-meins.
  insert zparts_mattgt.
  clear:it_parts,zparts_mattgt,mara.
endloop.

Reward if usefull

Re: excel data into database

Former Member — Thu, 10 Apr 2008 08:24:58 GMT

Hi...

execute this..




REPORT ZVATPHASE2_UPLOAD .

TYPE-POOLS: TRUXS.

TABLES: J_1IMOVEND.

DATA: I_TEXT_DATA       TYPE TRUXS_T_TEXT_DATA,
      V_FILENAME_STRING TYPE STRING,
      FILE_NAME         LIKE IBIPPARMS-PATH,
      WF_LINES(6).

*DATA : BEGIN OF ITAB1 OCCURS 0.
*          INCLUDE TYPE J_1IMOVEND.
*DATA : END OF ITAB1.
DATA : BEGIN OF ITAB1 OCCURS 0,
          LIFNR     LIKE J_1IMOVEND-LIFNR,
          J_1ILSTNO LIKE J_1IMOVEND-J_1ILSTNO,
       END OF ITAB1.

*SELECTION-SCREEN BEGIN  OF  BLOCK B1 WITH FRAME TITLE TEXT-001.
*  PARAMETERS: P_FILE LIKE RLGRAP-FILENAME.
*SELECTION-SCREEN END     OF   BLOCK B1 .
*
*AT SELECTION-SCREEN.

CALL FUNCTION 'F4_FILENAME'
 EXPORTING
   PROGRAM_NAME        = SYST-CPROG
   DYNPRO_NUMBER       = SYST-DYNNR
 IMPORTING
   FILE_NAME           = FILE_NAME.


*START-OF-SELECTION.
IF NOT FILE_NAME IS INITIAL.
  V_FILENAME_STRING = FILE_NAME.

  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      FILENAME                      = V_FILENAME_STRING
      FILETYPE                      = 'ASC'
      HAS_FIELD_SEPARATOR           = 'X'
      DAT_MODE                      = ''
    TABLES
      DATA_TAB                      = I_TEXT_DATA.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      I_FIELD_SEPERATOR          = 'X'
*     I_LINE_HEADER              =
      I_TAB_RAW_DATA             = I_TEXT_DATA
      I_FILENAME                 = FILE_NAME
    TABLES
      I_TAB_CONVERTED_DATA       = ITAB1
    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.

ELSE.
  PERFORM CALLERR USING 'Enter a Valid File Name, Thank You'.
ENDIF.


*&*&
DATA : BEGIN OF ITAB OCCURS 0.
          INCLUDE TYPE J_1IMOVEND.
DATA : END OF ITAB.

SELECT * FROM J_1IMOVEND INTO TABLE ITAB.

*LOOP AT ITAB.
*  READ

LOOP AT  ITAB1.
 UPDATE J_1IMOVEND SET J_1ILSTNO = ITAB1-J_1ILSTNO
                   WHERE LIFNR EQ ITAB1-LIFNR.
ENDLOOP.

CLEAR : WF_LINES.
DESCRIBE TABLE ITAB1 LINES WF_LINES.

WRITE : WF_LINES, 'Records Updated Successfully'.

*&---------------------------------------------------------------------*
*&      Form  CALLERR
*&---------------------------------------------------------------------*
*  CALLING FUN MOD FOR GIVING STATUS MESSAGE & ERER MSG
*----------------------------------------------------------------------*
  FORM CALLERR USING TEXT1.

    CALL FUNCTION 'FC_POPUP_ERR_WARN_MESSAGE'
         EXPORTING
              POPUP_TITLE  = 'VAT Report Phase II'
              IS_ERROR     = 'X'
              MESSAGE_TEXT = TEXT1
              START_COLUMN = 30
              START_ROW    = 8.
    STOP.

  ENDFORM.                    " CALLERR

With Rgds,

S.Barani

Re: excel data into database

Former Member — Thu, 10 Apr 2008 08:35:52 GMT

Hi ,

First is you need to upload that excell data into an internal table through function module 'ALSM_EXCELL_TO_INTERNAL_TABLE' then you can read this data to work area and modify the database tables .

Regards,

Naveen Kumar M S

Re: excel data into database

Former Member — Thu, 10 Apr 2008 09:18:30 GMT

Hi,

Check the following code:

Upload data direct from excel.xls file to SAP

REPORT ZEXCELUPLOAD.

PARAMETERS: filename LIKE rlgrap-filename MEMORY ID M01,

begcol TYPE i DEFAULT 1 NO-DISPLAY,

begrow TYPE i DEFAULT 1 NO-DISPLAY,

endcol TYPE i DEFAULT 100 NO-DISPLAY,

endrow TYPE i DEFAULT 32000 NO-DISPLAY.

Tick don't append header

PARAMETERS: kzheader AS CHECKBOX.

DATA: BEGIN OF intern OCCURS 0.

INCLUDE STRUCTURE alsmex_tabline.

DATA: END OF intern.

DATA: BEGIN OF intern1 OCCURS 0.

INCLUDE STRUCTURE alsmex_tabline.

DATA: END OF intern1.

DATA: BEGIN OF t_col OCCURS 0,

col LIKE alsmex_tabline-col,

size TYPE i.

DATA: END OF t_col.

DATA: zwlen TYPE i,

zwlines TYPE i.

DATA: BEGIN OF fieldnames OCCURS 3,

title(60),

table(6),

field(10),

kz(1),

END OF fieldnames.

No of columns

DATA: BEGIN OF data_tab OCCURS 0,

value_0001(50),

value_0002(50),

value_0003(50),

value_0004(50),

value_0005(50),

value_0006(50),

value_0007(50),

value_0008(50),

value_0009(50),

value_0010(50),

value_0011(50),

value_0012(50),

value_0013(50),

value_0014(50),

value_0015(50),

value_0016(50),

value_0017(50),

value_0018(50),

value_0019(50),

value_0020(50),

value_0021(50),

value_0022(50),

value_0023(50),

value_0024(50),

value_0025(50),

value_0026(50),

value_0027(50),

value_0028(50),

value_0029(50),

value_0030(50),

value_0031(50),

value_0032(50),

value_0033(50),

value_0034(50),

value_0035(50),

value_0036(50),

value_0037(50),

value_0038(50),

value_0039(50),

value_0040(50),

value_0041(50),

value_0042(50),

value_0043(50),

value_0044(50),

value_0045(50),

value_0046(50),

value_0047(50),

value_0048(50),

value_0049(50),

value_0050(50),

value_0051(50),

value_0052(50),

value_0053(50),

value_0054(50),

value_0055(50),

value_0056(50),

value_0057(50),

value_0058(50),

value_0059(50),

value_0060(50),

value_0061(50),

value_0062(50),

value_0063(50),

value_0064(50),

value_0065(50),

value_0066(50),

value_0067(50),

value_0068(50),

value_0069(50),

value_0070(50),

value_0071(50),

value_0072(50),

value_0073(50),

value_0074(50),

value_0075(50),

value_0076(50),

value_0077(50),

value_0078(50),

value_0079(50),

value_0080(50),

value_0081(50),

value_0082(50),

value_0083(50),

value_0084(50),

value_0085(50),

value_0086(50),

value_0087(50),

value_0088(50),

value_0089(50),

value_0090(50),

value_0091(50),

value_0092(50),

value_0093(50),

value_0094(50),

value_0095(50),

value_0096(50),

value_0097(50),

value_0098(50),

value_0099(50),

value_0100(50).

DATA: END OF data_tab.

DATA: tind(4) TYPE n.

DATA: zwfeld(19).

FIELD-SYMBOLS: <fs1>.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

mask = '*.xls'

static = 'X'

CHANGING

file_name = filename.

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = begcol

i_begin_row = begrow

i_end_col = endcol

i_end_row = endrow

TABLES

intern = intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

WRITE:/ 'Upload Error ', SY-SUBRC.

ENDIF.

END-OF-SELECTION.

LOOP AT intern.

intern1 = intern.

CLEAR intern1-row.

APPEND intern1.

ENDLOOP.

SORT intern1 BY col.

LOOP AT intern1.

AT NEW col.

t_col-col = intern1-col.

APPEND t_col.

ENDAT.

zwlen = strlen( intern1-value ).

READ TABLE t_col WITH KEY col = intern1-col.

IF sy-subrc EQ 0.

IF zwlen > t_col-size.

t_col-size = zwlen.

Internal Table, Current Row Index

MODIFY t_col INDEX sy-tabix.

ENDIF.

ENDLOOP.

DESCRIBE TABLE t_col LINES zwlines.

SORT intern BY row col.

IF kzheader = 'X'.

LOOP AT intern.

fieldnames-title = intern-value.

APPEND fieldnames.

AT END OF row.

EXIT.

ENDAT.

ENDLOOP.

ELSE.

DO zwlines TIMES.

WRITE sy-index TO fieldnames-title.

APPEND fieldnames.

ENDDO.

ENDIF.

SORT intern BY row col.

LOOP AT intern.

IF kzheader = 'X'

AND intern-row = 1.

CONTINUE.

ENDIF.

tind = intern-col.

CONCATENATE 'DATA_TAB-VALUE_' tind INTO zwfeld.

ASSIGN (zwfeld) TO <fs1>.

<fs1> = intern-value.

AT END OF row.

APPEND data_tab.

CLEAR data_tab.

ENDAT.

ENDLOOP.

CALL FUNCTION 'DISPLAY_BASIC_LIST'

EXPORTING

file_name = filename

TABLES

data_tab = data_tab

fieldname_tab = fieldnames.

End of Program

Regards,

Bhaskar