Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

FROM EXCEL FILE TO INTERNAL TABLE

Adi_Bathineni
Participant

‎10-01-2008 1:43 AM

0 Kudos

HI GURU'S,

i'm using the following code to conver the excel file into internal table.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

  • I_FIELD_SEPERATOR = 'X'

  • I_LINE_HEADER =

I_TAB_RAW_DATA = IT_TAB_RAW_DATA

I_FILENAME = P_FILE

TABLES

I_TAB_CONVERTED_DATA = IT_MM01

EXCEPTIONS

CONVERSION_FAILED = 1

OTHERS = 2

.

What is the exact format for the to send the Excel File.

My requirement is the Excel file contains the several fields , that should be stored as a records in the internal table

My problem is when i send the data in the excel file as one column, i'm getting all the data in the first column of the Internal Table.

If i send the data in the excel file as a record it is not filling the internal table.

what is the problem??/

if the Excel files contain multiple records is it able to convert the data into internal table records.

Regards,

Adi.

5 REPLIES 5

Former Member

‎10-01-2008 3:19 AM

0 Kudos

Hi The below code could be the best possible solutions for you.

Try to use field-symbols to get the data in row format into internal table.


*&---------------------------------------------------------------------*
*& Report  ZEXCELUPLOAD_EXAMPLE                                        *
*&--------------------------------------------------------------------&*
*& This program is used to upload master data into the SCARR table     *
*& which is an Airline Master data Table.                              *
*&---------------------------------------------------------------------*


report  zexcelupload_example   .

*data declarations

tables: scarr.

data: begin of itab occurs 0.
        include structure alsmex_tabline.
data: end of itab.

data: begin of data_itab occurs 0,
      value_0001(50),
      value_0002(50),
      value_0003(50),
      value_0004(50),
      value_0005(50),
      value_0006(50),
       value_0007(50),
       value_0008(50),
       value_0009(50),
       value_0010(50),
       value_0011(50),
       value_0012(50),
       value_0013(50),
       value_0014(50),
       value_0015(50),
       value_0016(50),
       value_0017(50),
       value_0018(50),
       value_0019(50),
       value_0020(50),
       value_0021(50),
       value_0022(50),
       value_0023(50),
       value_0024(50),
       value_0025(50),
       value_0026(50),
       value_0027(50),
       value_0028(50),
       value_0029(50),
       value_0030(50),
       value_0031(50),
       value_0032(50),
       value_0033(50),
       value_0034(50),
       value_0035(50),
       value_0036(50),
       value_0037(50),
       value_0038(50),
       value_0039(50),
       value_0040(50),
       value_0041(50),
       value_0042(50),
       value_0043(50),
       value_0044(50),
       value_0045(50),
       value_0046(50),
       value_0047(50),
       value_0048(50),
       value_0049(50),
       value_0050(50),
       value_0051(50),
       value_0052(50),
       value_0053(50),
       value_0054(50),
       value_0055(50),
       value_0056(50),
       value_0057(50),
       value_0058(50),
       value_0059(50),
       value_0060(50),
       value_0061(50),
       value_0062(50),
       value_0063(50),
       value_0064(50),
       value_0065(50),
       value_0066(50),
       value_0067(50),
       value_0068(50),
       value_0069(50),
       value_0070(50),
       value_0071(50),
       value_0072(50),
       value_0073(50),
       value_0074(50),
       value_0075(50),
       value_0076(50),
       value_0077(50),
       value_0078(50),
       value_0079(50),
       value_0080(50),
       value_0081(50),
       value_0082(50),
       value_0083(50),
       value_0084(50),
       value_0085(50),
       value_0086(50),
       value_0087(50),
       value_0088(50),
       value_0089(50),
       value_0090(50),
       value_0091(50),
       value_0092(50),
       value_0093(50),
       value_0094(50),
       value_0095(50),
       value_0096(50),
       value_0097(50),
       value_0098(50),
       value_0099(50),
       value_0100(50),

end of data_itab.

field-symbols <fs1>.
data: wa_col(4) type n,
      wa_field(20).

*selection screen parameters

parameters: f_name like rlgrap-filename,
            begcol type i default '1' no-display,
            begrow type i default '1' no-display,
            endcol type i default '100' no-display,
            endrow type i default '9999' no-display.


at selection-screen on value-request for f_name.

  call function 'F4_FILENAME'
    exporting
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
      field_name    = ' '
    importing
      file_name     = f_name.

start-of-selection.

  call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    exporting
      filename                      = f_name
      i_begin_col                   = begcol
      i_begin_row                   = begrow
      i_end_col                     = endcol
      i_end_row                     = endrow
    tables
      intern                        = itab
* 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.
*    WRITE: / 'Upload Error'.
*  ENDIF.
*
  sort itab by row col.

  loop at itab.
    if itab-row = 1.
      continue.
    else.
      wa_col = itab-col.
      concatenate 'data_itab-value_' wa_col into wa_field.
      assign (wa_field) to <fs1>.
      <fs1> = itab-value.
      at end of row.
        append data_itab.
        clear data_itab.
      endat.
    endif.

  endloop.

  loop at data_itab.
    scarr-mandt    = data_itab-value_0001.
    scarr-carrid   = data_itab-value_0002.
    scarr-carrname = data_itab-value_0003.
    scarr-currcode = data_itab-value_0004.
    scarr-url      = data_itab-value_0005.
    insert scarr.
*    IF sy-subrc <> 0.
*      WRITE: / 'Error while inserting records into SCARR'.
*    ENDIF.
    write: / sy-tabix, data_itab-value_0001+0(3), data_itab-value_0002+0(3), data_itab-value_0003+0(20),
             data_itab-value_0004+0(3), data_itab-value_0005+0(30).
  endloop.

Thanks,

srinivas cherukuru.

Former Member

‎10-01-2008 5:12 AM

0 Kudos

**Internal Table to hold the records in the text file

Hello Adi,

Put the fields in excel in the same format in which u want in ur internal table(here record)

U can refer the following code.

data: begin of record occurs 0,

  • General Data

  • data element: BUKRS

bukrs_001(004), " Company Code

  • data element: EKORG

ekorg_002(004), " Purchase Orgn

  • data element: KTOKK

ktokk_003(004), " Account Group

end of record.

  • Internal Table

data:it_excel like table of alsmex_tabline with header line.

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

**SELECTION-SCREEN**

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

selection-screen begin of block blk.

parameters: p_file like rlgrap-filename default 'c:\vendor_creation.xls'

.

selection-screen end of block blk.

*----

  • A T S E L E C T I O N - S C R E E N O U T P U T

*----

***************START*********************** F4 Help for field p_file

at selection-screen on value-request for p_file.

call function 'KD_GET_FILENAME_ON_F4'

exporting

program_name = syst-repid

dynpro_number = syst-dynnr

  • FIELD_NAME = ' '

  • STATIC = ' '

  • MASK = ' '

changing

file_name = p_file

  • EXCEPTIONS

  • MASK_TOO_LONG = 1

  • OTHERS = 2

.

if sy-subrc <> 0.

message e006(zhnc).

endif.

**----

  • S T A R T - O F - S E L E C T I O N

*----

start-of-selection.

data : vf_index type i.

data : vf_start_col type i value '1', "start column

vf_start_row type i value '4', "start row

vf_end_col type i value '200', "maximum column

vf_end_row type i value '2500', "maximum row

p_text(20). "stores error messages

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

*/ Work Area

data: wa_intern like it_excel.

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

*/ Field symbol

field-symbols : <fs>.

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

*********Fn Module to convert the excel file data into internal table

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = 'c:\vendor_creation.xls'

i_begin_col = vf_start_col

i_begin_row = vf_start_row

i_end_col = vf_end_col

i_end_row = vf_end_row

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.

if it_excel[] is initial.

p_text = 'No Data Uploaded'.

else.

sort it_excel by row col.

loop at it_excel.

move : it_excel-col to vf_index.

assign component vf_index of structure record to <fs>.

move : it_excel-value to <fs>.

at end of row.

append record.

clear record.

endat.

endloop.

endif.

In case of any problem u can revert back.

Aastha

Former Member

‎10-01-2008 5:16 AM

0 Kudos

Hi

Check this code...will give clear irdea on how to upload excel file data to internal table:

DATA: I_XLTAB TYPE STANDARD TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

 CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      FILENAME    = P_FNAME
      I_BEGIN_COL = B_COL
      I_BEGIN_ROW = B_ROW
      I_END_COL   = E_COL
      I_END_ROW   = E_ROW
    TABLES
      INTERN      = I_XLTAB.
    IF SY-SUBRC <> 0.
  message i002(zv).
    ENDIF.

  LOOP AT I_XLTAB.
    CASE I_XLTAB-COL.
      WHEN '1'.
        wa_flat-BUSINESS = I_XLTAB-VALUE.
      WHEN '2'.
        wa_flat-DESCR = I_XLTAB-VALUE.
      WHEN '3'.
        wa_flat-VENDOR = I_XLTAB-VALUE.
      WHEN '4'.
        wa_flat-PROF = I_XLTAB-VALUE.
      WHEN '5'.
        wa_flat-PROJID = I_XLTAB-VALUE.
      WHEN '6'.
        wa_flat-PROJTY = I_XLTAB-VALUE.
      WHEN '7'.
        wa_flat-WBS = I_XLTAB-VALUE.
      WHEN '8'.
        wa_flat-NETWTY = I_XLTAB-VALUE.
      WHEN '9'.
        wa_flat-NETWORK = I_XLTAB-VALUE.
      WHEN '10'.
        wa_flat-STARTDATE = I_XLTAB-VALUE.
      WHEN '11'.
        wa_flat-FINISH = I_XLTAB-VALUE.
      WHEN '12'.
        wa_flat-ACTUAL = I_XLTAB-VALUE.
      WHEN '13'.
        wa_flat-OP_INDI = I_XLTAB-VALUE.
      WHEN '14'.
        wa_flat-ANALTY = I_XLTAB-VALUE.
      WHEN '15'.
        wa_flat-MSTONE_COMPL = I_XLTAB-VALUE.
      WHEN '16'.
        wa_flat-MSTONE_INVOICE = I_XLTAB-VALUE.
      WHEN '17'.
        wa_flat-MSTONE_ANALYSIS = I_XLTAB-VALUE.
      WHEN '18'.
        wa_flat-MATERIAL = I_XLTAB-VALUE.
      WHEN '19'.
        wa_flat-QTY = I_XLTAB-VALUE.
      WHEN '20'.
        wa_flat-GLACC = I_XLTAB-VALUE.
      WHEN '21'.
        wa_flat-REQDATE  = I_XLTAB-VALUE.
        APPEND wa_flat TO IT_flat.
    ENDCASE.
  ENDLOOP.

Regards,

Vishwa.

former_member198275
Active Contributor

‎10-01-2008 5:22 AM

0 Kudos

hi

DATA:LI_TAB_RAW_DATA TYPE  TRUXS_T_TEXT_DATA.
DATA:L_FILENAME      LIKE  RLGRAP-FILENAME.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      I_TAB_RAW_DATA       = LI_TAB_RAW_DATA
      I_FILENAME           = L_FILENAME
    TABLES
      I_TAB_CONVERTED_DATA = LT_internal
    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.

Now it will capture all the record of excel file. If ur excel file contains header then it will also come

in internal table.

here lt_internal is ur internal table. Define its fields accoring to ur excel file contained fields.

Hope this will work.

Adi_Bathineni
Participant

‎12-04-2008 7:09 PM

0 Kudos

Thanks