12-20-2007 9:12 AM
Hi all,
I have to develop a report in FI module. There are ten categories in which all g/l are assigned. It is stored in an excel file. The enduser will be just giving the categories and i need to read the excel file and get the corresponding g/l's and its value. There is function module ' TEXT_CONVERT_XLS_TO_SAP' in which i can convert excel data to sap format. But how shall i assign the path name to read the excel file because users are storing it in different location in different system. Please help me in this issue. Useful suggestions will be rewarded.
With Thanks,
Karthi,.
12-20-2007 9:24 AM
Hi,
PARAMETERS: p_infile LIKE rlgrap-filename DEFAULT 'C:\TEMP\SALES.XLS'.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_infile.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
mask = '.,..'
IMPORTING
filename = p_infile
EXCEPTIONS
selection_cancel = 3
OTHERS = 5.
IF sy-subrc EQ 3.
MESSAGE 'User cancelled file selection' TYPE 'S'.
ELSEIF sy-subrc = 5 OR sy-subrc <> 0.
MESSAGE 'Problem in file selection' TYPE 'S'.
ENDIF.
START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_field_seperator = 'X'
i_line_header = 'X'
i_tab_raw_data = it_raw
i_filename = p_infile
TABLES
i_tab_converted_data = it_datatab[]
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.
12-20-2007 9:13 AM
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-002.
PARAMETERS: P_FILE TYPE LOCALFILE OBLIGATORY .
SELECTION-SCREEN END OF BLOCK B1.
**********************AT SELECTION SCREEN EVENTS BEGINS***************
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
STATIC = 'X'
CHANGING
FILE_NAME = P_FILE.
here P-file holds your path
12-20-2007 9:14 AM
Hi,
Use FM ALSM_EXCEL_TO_INTERNAL_TABLE to upload the data from excel sheet to internal table.
Regards,
Prashant
12-20-2007 9:14 AM
Sample prg to get data from excel
Dear frnd,
Try this,
DATA:IT_EXCEL LIKE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-002.
PARAMETERS: P_FILE TYPE LOCALFILE OBLIGATORY .
SELECTION-SCREEN END OF BLOCK B1.
**********************AT SELECTION SCREEN EVENTS BEGINS***************
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
STATIC = 'X'
CHANGING
FILE_NAME = P_FILE.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FILE
I_BEGIN_COL = 1 "From 1st Column
I_BEGIN_ROW = 2 "From 2nd row
I_END_COL = 6 "Till 6th Column
I_END_ROW = 65536 "Till Row
TABLES
INTERN = IT_EXCEL
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
CLEAR IT_EXCEL.
DESCRIBE TABLE IT_EXCEL.
IF SY-TFILL = 0.
MESSAGE I937.
STOP.
ENDIF.
LOOP AT IT_EXCEL.
CASE IT_EXCEL-COL.
WHEN '0001'.
MOVE: IT_EXCEL-VALUE TO RECORD1-MATNR_005.
WHEN '0002'.
MOVE: IT_EXCEL-VALUE TO RECORD1-WERKS_006.
WHEN '0003'.
MOVE: IT_EXCEL-VALUE TO RECORD1-BUDAT_002.
WHEN '0004'.
MOVE: IT_EXCEL-VALUE TO RECORD1-BKTXT_004.
WHEN '0005'.
MOVE: IT_EXCEL-VALUE TO RECORD1-ERFMG_007.
WHEN '0006'.
MOVE: IT_EXCEL-VALUE TO RECORD1-ERFMG_008.
ENDCASE.
AT END OF ROW.
APPEND RECORD1.
CLEAR RECORD1.
ENDAT.
ENDLOOP.
reward if it helped
12-20-2007 9:19 AM
Dear keshu,
I already know a FM ' F4_FILENAME' which can be used to get the filepath location, but the thing is i should not specify a file path in selection screen as input field. So only i'm asking is there any other means to work on this.
Regards,
Karthi.
12-20-2007 9:17 AM
12-20-2007 9:18 AM
make a parameter to take input of the path of the file.
and use the event AT SELECTION-SCREEN ON VALUE REQUEST FOR <field name>
and in that use the FM F4_FILENAME
Reward if useful.
Thanks
Sayak
12-20-2007 9:24 AM
Hi,
PARAMETERS: p_infile LIKE rlgrap-filename DEFAULT 'C:\TEMP\SALES.XLS'.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_infile.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
mask = '.,..'
IMPORTING
filename = p_infile
EXCEPTIONS
selection_cancel = 3
OTHERS = 5.
IF sy-subrc EQ 3.
MESSAGE 'User cancelled file selection' TYPE 'S'.
ELSEIF sy-subrc = 5 OR sy-subrc <> 0.
MESSAGE 'Problem in file selection' TYPE 'S'.
ENDIF.
START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_field_seperator = 'X'
i_line_header = 'X'
i_tab_raw_data = it_raw
i_filename = p_infile
TABLES
i_tab_converted_data = it_datatab[]
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.
12-20-2007 9:25 AM
ALSM_EXCEL_TO_INTERNAL_TABLE and this function module is used to upload the contents of Excel file into Internal Table.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME =
I_BEGIN_COL =
I_BEGIN_ROW =
I_END_COL =
I_END_ROW =
TABLES
INTERN =
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
Reward points if useful.
12-20-2007 9:26 AM
Then on execution i.e after start-of-selection call the functional module
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
STATIC = 'X'
CHANGING
FILE_NAME = P_FILE.
declare the p_file.
01-29-2008 10:19 AM
01-29-2008 10:19 AM