07-23-2008 9:45 AM
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
07-23-2008 10:05 AM
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.
07-23-2008 9:47 AM
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
07-23-2008 9:47 AM
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
07-23-2008 9:55 AM
&----
*& 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.
07-23-2008 10:05 AM
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.
07-23-2008 11:20 AM
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
07-23-2008 11:22 AM
hi use this..
FAA_FILE_UPLOAD_EXCEL ALSM_EXCEL_TO_INTERNAL_TABLE
it is sufficient for ur requirement
07-23-2008 11:31 AM
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
07-23-2008 12:27 PM