01-22-2008 4:26 AM
Hi,
How to upload data from XL sheet to internal table , can u please provide the sample code.
thanks
krishna
01-22-2008 4:28 AM
* types declaration of ty_tab
TYPES : BEGIN OF ty_tab,
bukrs TYPE anla-bukrs,
anln1 TYPE anla-anln1,
anln2 TYPE anla-anln2,
anlkl TYPE anla-anlkl,
END OF ty_tab.
* data declaration for ty_tab
DATA : it_tab TYPE TABLE OF ty_tab INITIAL SIZE 0,
is_tab TYPE ty_tab.
* data declaration to upload excel file to internal table
DATA : it_data TYPE TABLE OF alsmex_tabline INITIAL SIZE 0,
is_data TYPE alsmex_tabline.
* selection screen
SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS : p_file TYPE rlgrap-filename OBLIGATORY.
SELECTION-SCREEN : END OF BLOCK b1.
* at selection screen on value request
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
program_name = syst-cprog
dynpro_number = syst-dynnr
field_name = ' '
IMPORTING
file_name = p_file.
* start of selection
START-OF-SELECTION.
* sub routine to upload data from excel sheet to internal table
PERFORM upload_data.
*&---------------------------------------------------------------------*
*& Form upload_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM upload_data.
IF NOT p_file IS INITIAL.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 256
i_end_row = 65536
TABLES
intern = it_data
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.
ENDIF.
IF NOT it_data[] IS INITIAL.
LOOP AT it_data INTO is_data.
AT NEW row.
CLEAR : is_tab.
ENDAT.
IF is_data-col = '001'.
MOVE is_data-value TO is_tab-bukrs.
ENDIF.
IF is_data-col = '002'.
MOVE is_data-value TO is_tab-anln1.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = is_tab-anln1
IMPORTING
output = is_tab-anln1.
ENDIF.
IF is_data-col = '003'.
MOVE is_data-value TO is_tab-anln2.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = is_tab-anln2
IMPORTING
output = is_tab-anln2.
ENDIF.
IF is_data-col = '004'.
MOVE is_data-value TO is_tab-anlkl.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = is_tab-anlkl
IMPORTING
output = is_tab-anlkl.
ENDIF.
AT END OF row.
APPEND is_tab TO it_tab.
ENDAT.
CLEAR : is_data.
ENDLOOP.
ENDIF.
ENDFORM. " upload_data
Regards
Gopi
01-22-2008 4:28 AM
* types declaration of ty_tab
TYPES : BEGIN OF ty_tab,
bukrs TYPE anla-bukrs,
anln1 TYPE anla-anln1,
anln2 TYPE anla-anln2,
anlkl TYPE anla-anlkl,
END OF ty_tab.
* data declaration for ty_tab
DATA : it_tab TYPE TABLE OF ty_tab INITIAL SIZE 0,
is_tab TYPE ty_tab.
* data declaration to upload excel file to internal table
DATA : it_data TYPE TABLE OF alsmex_tabline INITIAL SIZE 0,
is_data TYPE alsmex_tabline.
* selection screen
SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS : p_file TYPE rlgrap-filename OBLIGATORY.
SELECTION-SCREEN : END OF BLOCK b1.
* at selection screen on value request
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
program_name = syst-cprog
dynpro_number = syst-dynnr
field_name = ' '
IMPORTING
file_name = p_file.
* start of selection
START-OF-SELECTION.
* sub routine to upload data from excel sheet to internal table
PERFORM upload_data.
*&---------------------------------------------------------------------*
*& Form upload_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM upload_data.
IF NOT p_file IS INITIAL.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 256
i_end_row = 65536
TABLES
intern = it_data
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.
ENDIF.
IF NOT it_data[] IS INITIAL.
LOOP AT it_data INTO is_data.
AT NEW row.
CLEAR : is_tab.
ENDAT.
IF is_data-col = '001'.
MOVE is_data-value TO is_tab-bukrs.
ENDIF.
IF is_data-col = '002'.
MOVE is_data-value TO is_tab-anln1.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = is_tab-anln1
IMPORTING
output = is_tab-anln1.
ENDIF.
IF is_data-col = '003'.
MOVE is_data-value TO is_tab-anln2.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = is_tab-anln2
IMPORTING
output = is_tab-anln2.
ENDIF.
IF is_data-col = '004'.
MOVE is_data-value TO is_tab-anlkl.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = is_tab-anlkl
IMPORTING
output = is_tab-anlkl.
ENDIF.
AT END OF row.
APPEND is_tab TO it_tab.
ENDAT.
CLEAR : is_data.
ENDLOOP.
ENDIF.
ENDFORM. " upload_data
Regards
Gopi
01-22-2008 4:36 AM
HI Krishna
See this code where i had writen to UPLOAD A EXCEL SHEET AND again pass that to APPLICATION server
i think it wil be very useful for you
you can understand this very easily
REPORT ZSD_EXCEL_INT_APP.
parameter: file_nm type localfile.
types : begin of it_tab1,
f1(20),
f2(40),
f3(20),
end of it_tab1.
data : it_tab type table of ALSMEX_TABLINE with header line,
file type rlgrap-filename.
data : it_tab2 type it_tab1 occurs 1,
wa_tab2 type it_tab1,
w_message(100) TYPE c.
at selection-screen on value-request for file_nm.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '
STATIC = 'X'
MASK = ' '
CHANGING
file_name = file_nm
EXCEPTIONS
MASK_TOO_LONG = 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.
start-of-selection.
refresh it_tab2[].clear wa_tab2.
file = file_nm.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = file
i_begin_col = '1'
i_begin_row = '1'
i_end_col = '10'
i_end_row = '35'
tables
intern = it_tab
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_tab.
case it_tab-col.
when '002'.
wa_tab2-f1 = it_tab-value.
when '004'.
wa_tab2-f2 = it_tab-value.
when '008'.
wa_tab2-f3 = it_tab-value.
endcase.
at end of row.
append wa_tab2 to it_tab2.
clear wa_tab2.
endat.
endloop.
data : p_file TYPE rlgrap-filename value 'TEST3.txt'.
OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
*--- Display error messages if any.
IF sy-subrc NE 0.
MESSAGE e001(zsd_mes).
EXIT.
ELSE.
*---Data is downloaded to the application server file path
LOOP AT it_tab2 INTO wa_tab2.
TRANSFER wa_tab2 TO p_file.
ENDLOOP.
ENDIF.
*--Close the Application server file (Mandatory).
CLOSE DATASET p_file.
loop at it_tab2 into wa_tab2.
write : / wa_tab2-f1,wa_tab2-f2,wa_tab2-f3.
endloop.
01-22-2008 8:53 AM
Hi
1) Upload file using this FM into internal table.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_hfile
i_begin_col = 1
i_begin_row = 1
i_end_col = 7
i_end_row = 1000
TABLES
intern = itab_excel1
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
ENDIF.
2) Format the input data
LOOP AT itab_excel1 INTO wa_excel1.
CASE wa_excel1-col .
WHEN '1' .
wa_vbak-auart = wa_excel1-value.
WHEN '2'.
wa_vbak-vkorg = wa_excel1-value.
WHEN '3'.
wa_vbak-vtweg = wa_excel1-value.
WHEN '4'.
wa_vbak-spart = wa_excel1-value.
WHEN '5'.
wa_vbak-bstkd = wa_excel1-value.
WHEN '6'.
wa_vbak-bstdk = wa_excel1-value.
WHEN '7'.
wa_vbak-kunnr = wa_excel1-value.
ENDCASE.
AT END OF row.
CONDENSE :wa_vbak-auart,wa_vbak-vkorg,wa_vbak-vtweg,wa_vbak-spart,wa_vbak-bstkd,
wa_vbak-bstdk,wa_vbak-kunnr.
APPEND wa_vbak TO it_vbak.
CLEAR wa_vbak.
ENDAT.
ENDLOOP.
01-22-2008 9:23 AM
Hi,
Try:
REPORT ZUPLOAD.
TYPE-POOLS: truxs.
PARAMETERS: p_file TYPE rlgrap-filename.
TYPES: BEGIN OF t_datatab,
col1(30) TYPE c,
col2(30) TYPE c,
col3(4069) TYPE c,
END OF t_datatab.
DATA: it_datatab type standard table of t_datatab,
it_words type table of tdline with header line,
wa_datatab type t_datatab.
DATA: it_raw TYPE truxs_t_text_data.
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_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 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.
LOOP AT it_datatab INTO wa_datatab.
WRITE:/ wa_datatab-col1,
wa_datatab-col2,
wa_datatab-col3.
ENDLOOP.
Regards,
Gy