07-26-2005 7:53 AM
Hi all,
iam uploading excel file using FM:'GUI_UPLOAD', what r the perameters should be followed.
07-26-2005 7:57 AM
Hi,
File transfer from frontend PC to an internal backend table.
A format conversion is possible. The functions are similar to the module WS_UPLOAD, however the data provider is used for the upload and not GMUX.
Example
CALL FUNCTION 'GUI_UPLOAD'
exporting
filetype = 'BIN'
filename = 'C:\DOWNLOAD.BIN'
tables
data_tab = itab.
loads the contents of the file 'C:\DOWNLOAD.BIN' on the frontend PC into the internal backend table itab. The transferred data is not converted.
CALL FUNCTION 'GUI_UPLOAD'
exporting
filetype = 'ASC'
filename = 'C:\DOWNLOAD.TXT'
tables
data_tab = itab.
loads the contents of the file 'C:\DOWNLOAD.TXT' on the frontend PC line by line into the internal backend table itab. The transferred data is converted.
07-26-2005 8:00 AM
Find below one report that uploads the data into internal table.
U can get the logic out of that.
Regards,
Nitin
REPORT zphysical_stock_upload.
----
*----
TYPE-POOLS : slis.
Declaration for ALV Display
*----
CONSTANTS: c_pfstatus TYPE slis_formname VALUE 'SET_PF_STATUS'.
DATA:
w_current_inv_no(5) TYPE n,
w_answer TYPE c,
ws_fieldcat TYPE slis_fieldcat_alv,
it_fieldcat TYPE slis_t_fieldcat_alv,
ws_layout TYPE slis_layout_alv,
wa_event TYPE slis_alv_event,
it_eventtab TYPE slis_t_event,
wa_line TYPE slis_listheader,
gt_top_of_page_no_sel TYPE slis_t_listheader.
DATA :
BEGIN OF wfcsr_ui_popup_text,
titlebar(60) TYPE c,
question(400) TYPE c,
END OF wfcsr_ui_popup_text.
DATA :
w_pop_up_text LIKE wfcsr_ui_popup_text.
w_pop_up_text-titlebar = 'Upload Physical Stock'.
w_pop_up_text-question = 'Are u sure ! You Want To Upload Physical Stock ? '.
*----
TABLES : zphy_stock.
TYPES: BEGIN OF t_datatab ,
kostl(30) TYPE c, " Department Code or Cost Center
werks(30) TYPE c, " Plant
tag_number(30) TYPE c, " Tag Number
matnr(30) TYPE c, " Material Number
phy_qty(30) TYPE c, " Physical Qty.
END OF t_datatab.
DATA: it_tab TYPE filetable,
gd_subrc TYPE i.
DATA: it_datatab TYPE STANDARD TABLE OF t_datatab WITH HEADER LINE INITIAL SIZE 0,
it_phy_data TYPE STANDARD TABLE OF zphy_stock WITH HEADER LINE INITIAL SIZE 0.
DATA : gd_scol TYPE i VALUE '1',
gd_srow TYPE i VALUE '1',
gd_ecol TYPE i VALUE '256',
gd_erow TYPE i VALUE '65536'.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY
DEFAULT 'C:\Documents and Settings\nrp00161\My Documents\Inventory\Perpetual\Physical Stock.xls' .
" File Name
SELECTION-SCREEN END OF BLOCK b1.
***********************************************************************
AT SELECTION-SCREEN
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = 'Select File Name'
default_extension = '*.xls'
default_filename = '*.xls'
file_filter = '*.xls'
initial_directory = 'c:\'
MULTISELECTION =
WITH_ENCODING =
CHANGING
file_table = it_tab
rc = gd_subrc.
USER_ACTION =
FILE_ENCODING =
EXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
NOT_SUPPORTED_BY_GUI = 4
others = 5
.
IF sy-subrc <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ELSE.
READ TABLE it_tab INTO p_file INDEX 1.
ENDIF.
**********************************************************
INITIALIZATION.
Get Current Inventory Number
SELECT MAX( inv_number ) INTO w_current_inv_no
FROM zcurr_inv.
START-OF-SELECTION.
START-OF-SELECTION.
PERFORM upload_excel_file TABLES it_datatab
USING p_file
gd_scol
gd_srow
gd_ecol
gd_erow.
Build event table
PERFORM eventtab_build CHANGING it_eventtab.
PERFORM prepare_field_catelog.
PERFORM display_data.
*&----
*& Form upload_excel_file
&----
-->P_P_FILE text
-->P_GD_SCOL text
-->P_GD_SROW text
-->P_GD_ECOL text
-->P_GD_EROW text
*----
FORM upload_excel_file TABLES it_datatab
USING p_file
p_scol
p_srow
p_ecol
p_erow.
DATA : lt_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.
Has the following format:
Row number | Colum Number | Value
---------------------------------------
i.e. 1 1 Name1
2 1 Joe
DATA : ld_index TYPE i.
FIELD-SYMBOLS : <fs>.
Note: Alternative function module - 'ALSM_EXCEL_TO_INTERNAL_TABLE'
CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
EXPORTING
filename = p_file
i_begin_col = p_scol
i_begin_row = p_srow
i_end_col = p_ecol
i_end_row = p_erow
TABLES
intern = lt_intern
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
FORMAT COLOR COL_BACKGROUND INTENSIFIED.
WRITE:/ 'Error Uploading file'.
EXIT.
ENDIF.
IF lt_intern[] IS INITIAL.
FORMAT COLOR COL_BACKGROUND INTENSIFIED.
WRITE:/ 'No Data Uploaded'.
EXIT.
ELSE.
SORT lt_intern BY row col.
LOOP AT lt_intern.
MOVE lt_intern-col TO ld_index.
ASSIGN COMPONENT ld_index OF STRUCTURE it_datatab TO <fs>.
MOVE lt_intern-value TO <fs>.
AT END OF row.
APPEND it_datatab.
CLEAR it_datatab.
ENDAT.
ENDLOOP.
ENDIF.
LOOP AT it_datatab.
MOVE-CORRESPONDING it_datatab TO it_phy_data.
APPEND it_phy_data.
ENDLOOP.
READ TABLE it_phy_data INDEX 1.
SELECT SINGLE * FROM zphy_stock WHERE inv_number = w_current_inv_no
AND kostl = it_phy_data-kostl.
IF sy-subrc = 0.
MESSAGE i000(zqm) WITH 'ERROR ! Data for Cost Center' it_phy_data-kostl ' already exists.'.
ENDIF.
ENDFORM. " upload_excel_file
&----
&----
<-- p2 text
*----
FORM display_data .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_INTERFACE_CHECK = ' '
I_BYPASSING_BUFFER = ' '
I_BUFFER_ACTIVE = ' '
i_callback_program = sy-repid
i_callback_pf_status_set = c_pfstatus
i_callback_user_command = 'USER_COMMAND'
I_CALLBACK_TOP_OF_PAGE = ' '
I_CALLBACK_HTML_TOP_OF_PAGE = ' '
I_CALLBACK_HTML_END_OF_LIST = ' '
i_structure_name = 'ZPHY_STOCK'
I_BACKGROUND_ID = ' '
I_GRID_TITLE =
I_GRID_SETTINGS =
IS_LAYOUT =
IT_FIELDCAT =
IT_EXCLUDING =
IT_SPECIAL_GROUPS =
IT_SORT =
IT_FILTER =
IS_SEL_HIDE =
I_DEFAULT = 'X'
I_SAVE = ' '
IS_VARIANT =
IT_EVENTS =
IT_EVENT_EXIT =
IS_PRINT =
IS_REPREP_ID =
I_SCREEN_START_COLUMN = 0
I_SCREEN_START_LINE = 0
I_SCREEN_END_COLUMN = 0
I_SCREEN_END_LINE = 0
IT_ALV_GRAPHICS =
IT_HYPERLINK =
IT_ADD_FIELDCAT =
IT_EXCEPT_QINFO =
I_HTML_HEIGHT_TOP =
I_HTML_HEIGHT_END =
IMPORTING
E_EXIT_CAUSED_BY_CALLER =
ES_EXIT_CAUSED_BY_USER =
TABLES
t_outtab = it_phy_data
EXCEPTIONS
PROGRAM_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.
ENDFORM. " display_data
07-26-2005 8:08 AM
Hi Suresh,
To Read XL file cell by cell, u can try using FM
ALSM_EXCEL_TO_INTERNAL_TABLE. It works very good.. gives info by cells..
07-26-2005 8:12 AM
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = xls_file
I_BEGIN_COL = 1
I_BEGIN_ROW = 2
I_END_COL = 6
I_END_ROW = 500
TABLES
INTERN = INTERTAB
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2.
07-26-2005 8:46 AM
Hi,
Try this out
* Retrieve data file from presentation server(Upload from PC)
DATA: i_file like rlgrap-filename value '/usr/sap/tmp/file.txt'.
DATA: begin of it_datatab occurs 0,
row(500) type c,
end of it_datatab.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = i_file
filetype = 'ASC'
TABLES
data_tab = it_datatab "ITBL_IN_RECORD[]
EXCEPTIONS
file_open_error = 1
OTHERS = 2.
Check this link too
http://www.sapdevelopment.co.uk/file/file_updown.htm
Thanks & Regards,
Judith.
07-26-2005 8:57 AM
UPLOADING EXCEL FILE IS VERY TEDIOUS, WHY DON'T YOU FIRST SAVE IT AS TAB DELIMITED TXT FILE AND THEN USE "GUI_UPLOAD".
IF YOU WANT TO UPLOAD IT AS EXCEL FILE, THEN YOU WILL HAVE TO USE FM TOLD ABOVE "A....".
09-19-2005 6:19 AM
HI all,
I m uploading an excel file to R3, i m first saving as tab delimeted. My file may contain the first row with the names of the fields which i dont want to upload, how to delete the row and to upload the data without the names of the fields. Kindly help me, its very urgent..
and want actions should i take to upload the numeric fields..
please.
kumar
09-19-2005 6:24 AM
Hi Suresh,
If you want to upload the XLS directly into SAP without first converting it to a tab-delimited format, use the following FM "ALSM_EXCEL_TO_INTERNAL_TABLE". Below is an example of the same:
DATA: IT_MAIN LIKE STANDARD TABLE OF ALSMEX_TABLINE WITH HEADER LINE.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = 'c:\test.xls'
i_begin_col = 1
i_begin_row = 1
i_end_col = 4
i_end_row = 4
tables
intern = IT_MAIN
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.
LOOP AT IT_MAIN.
WRITE:/ IT_MAIN-ROW, ' ',
IT_MAIN-COL, ' ',
IT_MAIN-VALUE, ' '.
ENDLOOP.
The field VALUE of internal table IT_MAIN will contain the data. The first row will contain data from the first column, second for the second column and so on... In your case, the third row will contain 2nd row data from the first column of your excel sheet and so on.
Hopw this helps,
Cheers,
Madhur
09-19-2005 6:36 AM
HI madhu,
can u sort my problem please,its very urgent.
how can i do not take into consideration the first record in the excel file which has the names of the fields while uploading, please tell me soon
Nithin
09-19-2005 6:50 AM
Hi kumar,
Here's a code that i think would be helpful to you:
TYPE-POOLS truxs.
DATA: Begin of i_articles occurs 0,
matnr like mara-matnr,
flag(6) type c,
End of i_articles.
DATA: v_file type string,
i_temp type truxs_t_text_data.
selection-screen begin of block b1.
parameters: fl_file like rlgrap-filename lower case.
selection-screen end of block b1.
FORM UploadData.
v_file = fl_file.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
FILENAME = v_file
FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = 'X'
* HEADER_LENGTH = 0
* READ_BY_LINE = 'X'
DAT_MODE = ' '
* IMPORTING
* FILELENGTH =
* HEADER =
TABLES
DATA_TAB = i_temp
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.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
I_FIELD_SEPERATOR = 'X'
* I_LINE_HEADER =
I_TAB_RAW_DATA = i_temp
I_FILENAME = fl_file
TABLES
I_TAB_CONVERTED_DATA = i_articles
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.
ENDFORM. " UploadData
GUI_UPLOAD will convert your excel file to an internal file that can be interpreted by SAP. It then passes this internal file to TEXT_CONVERT_XLS_TO_SAP which in turn would convert the data into what you have defined in your internal table. No need to convert your excel file to a tab-delimited one.
09-19-2005 6:54 AM
In order to consider the first line as the header and not include it in your data, mark I_LINE_HEADER = 'X' in TEXT_CONVERT_XLS_TO_SAP.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
I_FIELD_SEPERATOR = 'X'
I_LINE_HEADER = 'X'
I_TAB_RAW_DATA = i_temp
I_FILENAME = fl_file
TABLES
I_TAB_CONVERTED_DATA = i_articles
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.
I hope this solves your problem.
09-19-2005 8:13 AM
thanx wienne for ur reply,
i think i dont want to take any other actions more than what u gav for excel uploading.hope the data will coome into my internal table.
but i also hav first coloumn which is blank.my actual data starts from second column.how to delete the first coloumn.
and what is the truxs field u gave?
reply me.
thanx
Nithin
Message was edited by: kumar kumar
09-19-2005 9:57 AM
Hi Nithin (Kumar),
Once you have uploaded the data into your internal table, simply delete the first line of the internal table (one which contains your field names) by using the delete statement.
DELETE IT_MAIN INDEX 1.
This will remove the first line and your internal table will contain only the data you want.
Regards,
Madhur
09-19-2005 9:58 AM
WEINNE,
its not working its going into dump, this is my code,
kindly help me, i hav to deliver thisby today,
data: begin of itab occurs 0,
KUNNR(10),
KLIMG(15),
KLIME(15),
WAERS(5),
DLAUS(10),
KUNNR1(10),
KKBER(4),
KLIMK(15),
KNKLI(10),
SAUFT(15),
SKFOR(15),
SSOBL(15),
UEDAT(15),
XCHNG(1),
ERNAM(12),
ERDAT(10),
CTLPC(3),
DTREV(10),
CRBLB(1),
SBGRP(3),
NXTRV(8),
KRAUS(1),
PAYDB(2),
DBRAT(3),
REVDB(10),
AEDAT(10),
AETXT(10),
GRUPP(4),
AENAM(12),
SBDAT(10),
KDGRP(8),
CASHD(10),
CASHA(13),
CASHC(5),
DBPAY(3),
DBRTG(5),
DBEKR(15),
DBWAE(5),
DBMON(8),
ABSBT(15),
end of itab.
****************************************
SELECTION-SCREEN: BEGIN OF BLOCK BLOCK2 WITH FRAME TITLE TEXT-002.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: ISC RADIOBUTTON GROUP RAD1 .
SELECTION-SCREEN COMMENT 03(24) TEXT-010.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: SSC RADIOBUTTON GROUP RAD1 DEFAULT 'X'.
SELECTION-SCREEN COMMENT 03(21) TEXT-011.
SELECTION-SCREEN END OF LINE.
PARAMETERS: P_INFILE LIKE RLGRAP-FILENAME. " default
'C:\MYTEST.TXT'." OBLIGATORY.
SELECTION-SCREEN END OF BLOCK BLOCK2.
PARAMETERS : P_MODE LIKE CTU_PARAMS-DISMODE DEFAULT 'N'.
TYPE-POOLS truxs.
DATA: v_file type string,
i_temp type truxs_t_text_data.
v_file = P_INFILE .
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
FILENAME = v_file
FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = 'X'
HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = ' '
IMPORTING
FILELENGTH =
HEADER =
TABLES
DATA_TAB = i_temp
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.
WRITE:'FAILED IN GUI UPLOAD'.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
I_FIELD_SEPERATOR = 'X'
I_LINE_HEADER =
I_TAB_RAW_DATA = i_temp
I_FILENAME = P_INFILE
TABLES
I_TAB_CONVERTED_DATA = ITAB
EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2
.
IF SY-SUBRC <> 0.
WRITE:'FAILED IN NEXT'.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
WRITE: ITAB-KUNNR.
09-19-2005 10:04 AM
rather than deleting in internal table,
delete header in excel and save it as txt file.
hope you got it
regards
are u on 4.6c or 4.7 uni
Message was edited by: Surpreet Singh Bal
09-19-2005 10:35 AM
Hi Surpreet,
Your solution is applicable if this is to be done for just one file, but what if its a convention there that the first row will always contain the field names? Then each time the file is to be uploaded, it will have to be edited. Also, converting it to text again requires a no. of steps which will only add to the workload, not to mention the scope for mistakes it opens up.
Since, SAP provides an FM to upload XLS files directly into SAP why not use it?
I hope you will agree with me.
Regards,
Madhur
09-19-2005 10:40 AM
09-19-2005 11:28 AM
Hi all,
IF I use the aslm_into_internal_table fm it has to be formatted for all the coloumns which i think combossum.
I used the code which was given to me. but its going into the dump. please tell me how should i move forward. My code aslo i pasted before/ see and please tell me.
Kumar
09-19-2005 11:58 AM
Hi Kumar,
The proper way is to format the data once you have it in the internal table. You must develop the logic first and then place it within a loop to move the values into another internal table in the format that is desired. This is less cumbersome than any other method your seek. There are no shortcuts for this problem.
Also, please initiate a new thread for your problems in future, since people helping you here will never get rewarded for their efforts. If you have your own thread you can award points and encourage them to help you in future as well. Besides, you have also robed the people who helped Suresh of their much deserved points since he cannot close this issue until your questions have been answered let alone the confusion this is going to create. We all are trying to work ethically here and hence maintain the sanctity of this forum.
I am sure you will appreciate this.
Regards,
Madhur
09-20-2005 5:00 AM
SORRY madhur,
iwas new to this i dont know how to create a new thread and close that.Lastly i ask u to how to do this.
and sorry for all who helped me as v r here to work and help with lot of efforts. sorry for this once again..
Kumar
09-20-2005 6:06 AM
hi kumar,
you need to include the line
TYPE-POOLS truxs
in your code so that you'll be able to use the type
truxs_t_text_data
. This will be the type of your internal table that will hold the raw data coming from
GUI_UPLOAD
...
Regarding your other question, i don't think it can be handled by the two function modules. you could just modify the internal table, removing the blank fields...
09-20-2005 7:35 AM
Hi Kumar,
That's fine. We do give you the benefit of doubt since you are new to the forum. To post your question, simply click on the "Post a New Topic" link on the main ABAP Forum page. This will take you to a screen where you can compose your message just like you have been doing it now
Regards,
Madhur
09-19-2005 8:33 AM
09-19-2005 9:18 AM
09-19-2005 3:00 PM