topic Re: abap in Application Development and Automation Discussions

abap

Former Member — Thu, 26 Jul 2007 05:59:01 GMT

how to retrive data from excel sheet to internal table

Re: abap

seshatalpasai_madala — Thu, 26 Jul 2007 06:00:21 GMT

HI,

Use FM TEXT_CONVERT_XLS_TO_SAP.

Regards,

Sesh

Re: abap

Former Member — Thu, 26 Jul 2007 06:01:18 GMT

hi,

chk this, put the data into an excel file.then download.

sample excel sheet.

coloumn 1 is name and column 2 is age

name age

A 8

C 13

D 55

************************************

DATA : int_excel LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.

data : record like db_name_age occurs 0 with header line.

DATA : v_start_col TYPE i VALUE '1', "starting col

       v_start_row TYPE i VALUE '1', " starting row

       v_end_col   TYPE i VALUE '2', " total columns

       v_end_row   TYPE i VALUE '10'. "total no of record


FORM f_upload .

  CLEAR : int_excel, int_excel[].

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = wf_filename
      i_begin_col             = v_start_col
      i_begin_row             = v_start_row
      i_end_col               = v_end_col
      i_end_row               = v_end_row
    TABLES
      intern                  = int_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc <> 0.
*Message is 'Unable to upload data from  '  wf_filename.
    MESSAGE e169(zm050) WITH wf_filename.
  ELSE.

    SORT int_excel BY row col.
    REFRESH : record.
    CLEAR   : record.


    LOOP AT int_excel.

      CASE int_excel-col. "go thru each column.

        WHEN 1.
          record-name  = int_excel-value. 
        WHEN 2.
          record-age = int_excel-value.      

      ENDCASE.

      AT END OF row.

        APPEND record.

        CLEAR record.

      ENDAT.

    ENDLOOP.


  ENDIF.

if this helped pld rewrd points,

rgrds

Reshma

Re: abap

Former Member — Thu, 26 Jul 2007 06:01:31 GMT

Hi,

u can use any one of the below 2 Function modules

ALSM_EXCEL_TO_INTERNAL_TABLE

GUI_UPLOAD

&----

*& Report Z_UPLOAD_EXAMPLE

&----

report z_upload_example.

parameters: exl_file type rlgrap-filename.

exl_file = p_file.

data: begin of itab occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

matkl like mara-matkl,

wrkst like mara-wrkst,

end of itab.

data: begin of itab1 occurs 0.

include structure alsmex_tabline.

data: end of itab1.

----

AT S E L E C T I O N S C R E E N ON VALUE REQUEST

----

at selection-screen on value-request for exl_file.

To get the F4 help for file

perform get_filename.

&----

*& Form GET_FILENAME

&----

This routine allows user to select the input file path

----

&----

*& Form get_filename

&----

text

----

--> p1 text
<-- p2 text

----

form get_filename .

call function 'F4_FILENAME'

exporting

program_name = syst-cprog

dynpro_number = syst-dynnr

FIELD_NAME = ' '

importing

file_name = exl_file.

endform. " get_filename

start-of-selection.

perform upload_data.

&----

*& Form upload_data

&----

text

----

--> p1 text
<-- p2 text

----

form upload_data .

data: v_file type string.

v_file = exl_file.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = exl_file

i_begin_col = 1

i_begin_row = 1

i_end_col = 4

i_end_row = 4

tables

intern = itab1

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.

endform. " upload_data

end-of-selection.

sort itab1 by row.

loop at itab1.

case itab1-col.

when '0001'.

itab-matnr = itab1-value.

when '0002'.

itab-mtart = itab1-value.

when '0003'.

itab-matkl = itab1-value.

when '0004'.

itab-wrkst = itab1-value.

endcase.

at end of row.

append itab.

clear itab.

endat.

endloop.

loop at itab.

write 😕 itab.

endloop.

Regards

Re: abap

Former Member — Thu, 26 Jul 2007 06:01:36 GMT

Hi,

Check the code below:

FORM upload_csv_file .

CLEAR gv_file.

gv_file = pa_file.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = gv_file

filetype = gc_asc

has_field_separator = gc_x

TABLES

data_tab = gt_dummy

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 NE gc_zero_num.

MESSAGE i006.

LEAVE LIST-PROCESSING.

ENDIF.

Check if the input file is blank

IF gt_dummy[] IS INITIAL.

MESSAGE i007.

LEAVE LIST-PROCESSING.

ENDIF.

Regards

Kannaiah

Re: abap

Former Member — Thu, 26 Jul 2007 06:02:32 GMT

Hi,

Use the Function Module - "TEXT_CONVERT_XLS_TO_SAP"

SAMPLE - CODE

TYPE-POOLS truxs.

types: begin of t_tab,

col1(5) type c,

col2(5) type c,

col3(5) type c,

end of t_tab.

data : itab type standard table of t_tab,

wa type t_tab.

data it_type type truxs_t_text_data.

parameter p_file type rlgrap-filename.

data ttab type tabname.

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.

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 = itab[]

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.

loop at itab into wa.

write : wa-col1.

write : wa-col2.

write : wa-col3.

endloop.

Regards,

Priyanka.

Re: abap

Former Member — Thu, 26 Jul 2007 06:03:05 GMT

hi,

use Fm ALSM_EXCEL_TO_INTERNAL_TABLE.

ex:

REPORT ZSR_BDC_XL

NO STANDARD PAGE HEADING LINE-SIZE 255.

TABLES : LFA1,RF02K.

DATA : BEGIN OF ITAB OCCURS 0,

LIFNR LIKE RF02K-LIFNR,

KTOKK LIKE RF02K-KTOKK,

NAME1 LIKE LFA1-NAME1,

SORTL LIKE LFA1-SORTL,

LAND1 LIKE LFA1-LAND1,

SPRAS LIKE LFA1-SPRAS,

END OF ITAB.

DATA : ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA : B1 TYPE I VALUE 1,

C1 TYPE I VALUE 1,

B2 TYPE I VALUE 10,

C2 TYPE I VALUE 99.

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = 'C:\Documents and Settings\sudhapa\Desktop\xl.XLS'

I_BEGIN_COL = B1

I_BEGIN_ROW = C1

I_END_COL = B2

I_END_ROW = C2

TABLES

INTERN = ITAB1

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.

FORM ORGANIZE_UPLOADED_DATA .

SORT ITAB1 BY ROW

COL.

LOOP AT ITAB1.

CASE ITAB1-COL.

....................................................

WHEN 1.

ITAB-LIFNR = ITAB1-VALUE.

WHEN 2.

ITAB-KTOKK = ITAB1-VALUE.

WHEN 3.

ITAB-NAME1 = ITAB1-VALUE.

WHEN 4.

ITAB-SORTL = ITAB1-VALUE.

WHEN 5.

ITAB-LAND1 = ITAB1-VALUE.

WHEN 6.

ITAB-SPRAS = ITAB1-VALUE.

....................................................

ENDCASE.

AT END OF ROW.

APPEND ITAB.

CLEAR ITAB.

ENDAT.

ENDLOOP.

ENDFORM. " ORGANIZE_UPLOADED_DATA

Re: abap

Former Member — Thu, 26 Jul 2007 06:14:37 GMT

Hi,

You can refer the following code snippet for excel upload.

CALL METHOD cl_gui_frontend_services=>gui_upload

EXPORTING

filename = l_filename " Path of the excel file

filetype = 'ASC'

has_field_separator = 'X'

CHANGING

data_tab = t_int_tab " Intenral table name.

Reegards,

Dilli