Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Program to upload a Excel file into Z table

Former Member

Hello Experts,

Does anyone have a program to upload an Excel sheet into the

Z table..?

Actually I have 40 thousand records in a excel.

I want to upload them into table.

and in future if someone wants the records to be modified or deleted they can do that in a excel and uplaod again.

Please let me know if yu have this program.

Thanks,

Varun

1 ACCEPTED SOLUTION

Former Member
0 Kudos

REPORT ZEXCEL_UPLOAD.

TYPE-POOLS truxs.

TABLES : zscarr.* Selection screen

PARAMETER p_file TYPE rlgrap-filename DEFAULT 'C:\TEST.xls'.

TYPES:

BEGIN OF t_tab,

carrid TYPE zscarr-carrid,

seats TYPE zscarr-total_seats,

END OF t_tab.DATA :

t_upload TYPE STANDARD TABLE OF t_tab,

wa_upload TYPE t_tab,

it_type TYPE truxs_t_text_data.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. CALL FUNCTION 'F4_FILENAME'

EXPORTING

  • PROGRAM_NAME = SYST-CPROG

  • DYNPRO_NUMBER = SYST-DYNNR

field_name = 'P_FILE'

IMPORTING

file_name = p_file.START-OF-SELECTION.

  • Uploading the data in the file into internal table

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

  • I_FIELD_SEPERATOR =

  • I_LINE_HEADER = 'X'

i_tab_raw_data = it_type

i_filename = p_file

TABLES

i_tab_converted_data = t_upload[]

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc NE 0.

MESSAGE ID sy-msgid

TYPE sy-msgty

NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

END-OF-SELECTION.

  • Uploading the data into the database table

LOOP AT T_UPLOAD INTO WA_UPLOAD.

ZSCARR-CARRID = WA_UPLOAD-CARRID.

ZSCARR-TOTAL_SEATS = WA_UPLOAD-SEATS.

MODIFY ZSCARR.

ENDLOOP.

http://www.saptechnical.com/Tips/ABAP/UploadExcel/Prg.htm

8 REPLIES 8

Former Member

hiiii

use following code to upload a data from excel file

*Upload data from Excel sheet to internal table.
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_pfile
      i_begin_col             = 1
      i_begin_row             = 2
      i_end_col               = 13
      i_end_row               = 8
    TABLES
      intern                  = it_excel
    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.


*Populate data to internal tables and structures

  SORT it_excel BY row col.

  LOOP AT it_excel INTO ls_excel.

    CASE ls_excel-col.
      WHEN 1.
        ls_data-rec_no = ls_excel-value.
        IF ls_data-rec_no NE w_docno.
          WRITE:
            'You Have Entered wrong Document Number'.
        ELSE.
          WRITE:
            'Document Number Same'.
        ENDIF.                           " IF ls_data-rec_no NE w_docno.
      WHEN 2.
        ls_data-doc_type = ls_excel-value.
      WHEN 3.
        ls_data-doc_part = ls_excel-value.
      WHEN 4.
        ls_data-doc_ver = ls_excel-value.
      WHEN 5.
        ls_data-application = ls_excel-value.
      WHEN 6.
        ls_data-data_car = ls_excel-value.
      WHEN 7.
        ls_data-file_path = ls_excel-value.

     ENDCASE.
ENDLOOP.

after getting data into internal table..you can send it to ztable..using INSERT INTO table statement...

another option is you can directly upload data by using LSMW...its very fast and easy way to upload data directly from text file to any table.

regards

twinkal

Edited by: twinkal patel on Jul 23, 2008 10:47 AM

Former Member

Hi Varun,


REPORT  zupload_excel_to_itab.

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE  rlgrap-filename.

TYPES: BEGIN OF t_datatab,
      col1(30)    TYPE c,
      col2(30)    TYPE c,
      col3(30)    TYPE c,
      END OF t_datatab.
DATA: it_datatab type standard table of t_datatab,
      wa_datatab type t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

* At selection screen
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      field_name = 'P_FILE'
    IMPORTING
      file_name  = p_file.


***********************************************************************
*START-OF-SELECTION.
START-OF-SELECTION.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR        =
      i_line_header            =  'X'
      i_tab_raw_data           =  it_raw       " WORK TABLE
      i_filename               =  p_file
    TABLES
      i_tab_converted_data     = it_datatab[]    "ACTUAL DATA
   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.


***********************************************************************
* END-OF-SELECTION.
END-OF-SELECTION.
  LOOP AT it_datatab INTO wa_datatab.
    WRITE:/ wa_datatab-col1,
            wa_datatab-col2,
            wa_datatab-col3.
  ENDLOOP.

[Link|http://sap.ittoolbox.com/code/archives.asp?i=10&d=3126&a=s]

Regards,

Sandeep

Former Member

&----


*& Report ZTESTPROG003

*&

&----


*&

*&

&----


REPORT ZTESTPROG003.

TYPES:

BEGIN OF ty_upload,

matnr like mara-matnr,

meins like mara-meins,

mtart like mara-mtart,

mbrsh like mara-mbrsh,

END OF ty_upload.

DATA it_upload TYPE STANDARD TABLE OF ty_upload WITH header line.

DATA wa_upload TYPE ty_upload.

DATA: itab TYPE STANDARD TABLE OF alsmex_tabline WITH header line.

  • DATA itab TYPE STANDARD TABLE OF ty_upload WITH header line.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = 'C:\Documents and Settings\venkatapp\Desktop\venkat.xls'

i_begin_col = 1

i_begin_row = 1

i_end_col = 4

i_end_row = 65535

TABLES

intern = itab.

if not itab[] is initial.

loop at itab .

case itab-col.

when '0001'.

it_upload-matnr = itab-value.

when '0002'.

it_upload-meins = itab-value.

when '0003'.

it_upload-mtart = itab-value.

when '0004'.

it_upload-mbrsh = itab-value.

append it_upload.

clear it_upload.

clear itab.

endcase.

endloop.

endif.

loop at it_upload.

ztable-matnr = it_upload-matnr .

ztable-meins = it_upload-meins.

insert ztable .

endloop.

Former Member
0 Kudos

REPORT ZEXCEL_UPLOAD.

TYPE-POOLS truxs.

TABLES : zscarr.* Selection screen

PARAMETER p_file TYPE rlgrap-filename DEFAULT 'C:\TEST.xls'.

TYPES:

BEGIN OF t_tab,

carrid TYPE zscarr-carrid,

seats TYPE zscarr-total_seats,

END OF t_tab.DATA :

t_upload TYPE STANDARD TABLE OF t_tab,

wa_upload TYPE t_tab,

it_type TYPE truxs_t_text_data.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. CALL FUNCTION 'F4_FILENAME'

EXPORTING

  • PROGRAM_NAME = SYST-CPROG

  • DYNPRO_NUMBER = SYST-DYNNR

field_name = 'P_FILE'

IMPORTING

file_name = p_file.START-OF-SELECTION.

  • Uploading the data in the file into internal table

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

  • I_FIELD_SEPERATOR =

  • I_LINE_HEADER = 'X'

i_tab_raw_data = it_type

i_filename = p_file

TABLES

i_tab_converted_data = t_upload[]

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc NE 0.

MESSAGE ID sy-msgid

TYPE sy-msgty

NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

END-OF-SELECTION.

  • Uploading the data into the database table

LOOP AT T_UPLOAD INTO WA_UPLOAD.

ZSCARR-CARRID = WA_UPLOAD-CARRID.

ZSCARR-TOTAL_SEATS = WA_UPLOAD-SEATS.

MODIFY ZSCARR.

ENDLOOP.

http://www.saptechnical.com/Tips/ABAP/UploadExcel/Prg.htm

0 Kudos

Hello All,

In my system,,those FM's are not there.

please tell me how to procced.

and these are also not there..

FAA_FILE_UPLOAD_EXCEL ALSM_EXCEL_TO_INTERNAL_TABLE

F4_FILENAME

0 Kudos

hi use this..

FAA_FILE_UPLOAD_EXCEL ALSM_EXCEL_TO_INTERNAL_TABLE

it is sufficient for ur requirement

0 Kudos

Above suggested FM TEXT_CONVERT_XLS_TO_SAP should work fine or try using GUI_UPLOAD

OR try this.

DATA: lt_zirmdx TYPE TABLE OF zirmdx WITH HEADER LINE.

  DATA lv_filename LIKE rlgrap-filename.
  DATA lv_pathname LIKE rlgrap-filename.

  DATA: BEGIN OF lt_field_tab OCCURS 10,
          fieldname(30),
        END OF lt_field_tab.

  DATA lt_ntab LIKE dfies OCCURS 0 WITH HEADER LINE.

  REFRESH : lt_field_tab, lt_ntab.
  CLEAR: lv_filename, lv_pathname.

  CALL FUNCTION 'WS_ULDL_PATH'
       IMPORTING
            upload_path = lv_pathname.

  CONCATENATE lv_pathname '*.XLS' INTO lv_filename.

  CALL FUNCTION 'UPLOAD'
       EXPORTING
            filename                = lv_filename
            filetype                = 'DAT'
            filemask_mask           = '*.XLS'
            filemask_text           = '*.XLS'
            filetype_no_change      = 'X'
            filetype_no_show        = 'X'
       TABLES
            data_tab                = lt_zirmdx
       EXCEPTIONS
            conversion_error        = 1
            invalid_table_width     = 2
            invalid_type            = 3
            no_batch                = 4
            unknown_error           = 5
            gui_refuse_filetransfer = 6
            OTHERS                  = 7.

  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 lt_zirmdx.
    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
         EXPORTING
              input  = lt_zirmdx-kunnr
         IMPORTING
              output = lt_zirmdx-kunnr.

    CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT'
         EXPORTING
              input        = lt_zirmdx-matnr
         IMPORTING
              output       = lt_zirmdx-matnr
         EXCEPTIONS
              length_error = 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.

    MODIFY lt_zirmdx TRANSPORTING kunnr matnr.
  ENDLOOP.

Regards,

Shailaja

Hi Varun,

You can use GUI_UPLOAD Function Module.

Please check this link

Best regards,

raam