EXCEL upload into ITAB

Former Member · ‎2006 Jun 28

Hi Experts,

Iam trying to upload excel sheet data into ITAB.Excel sheet having 13,000 records.But in the ITAB iam receiving only 10,000 records.What might be the reason?How can i get all the 13,000 records into my itab? Here is my code.


data: BEGIN OF t_actstock occurs 10,
       material(15),
       material_desc(25),
       total_stk(15),
       um(5),
       total_val(15),
       p(2),
       unitcost(15),
       prodcode(10),
       type(10),
       per(5),
       cost(15),
       END OF t_actstock.

DATA: file12 LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.

PARAMETERS: p_file12 LIKE rlgrap-filename OBLIGATORY DEFAULT  'C:act-stk-ledger.xls'.

 CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       EXPORTING
            filename                = p_file12
            i_begin_col             = '1'
            i_begin_row             = '2'
            i_end_col               = '150'
            i_end_row               = '63500'
       TABLES
            intern                  = file12
       EXCEPTIONS
            inconsistent_parameters = 1
            upload_ole              = 2
            OTHERS                  = 3.


 SORT file12 BY row col.
  READ TABLE file12 INDEX 1.
  as_currentrow = file12-row.
  LOOP AT file12.
    IF file12-row NE as_currentrow.
      APPEND wa_asrecord TO it_asrecord.
      CLEAR wa_asrecord.
      as_currentrow = file12-row.
    ENDIF.

    CASE file12-col.
      WHEN '0001'.
        wa_asrecord-material = file12-value.
      WHEN '0002'.
        wa_asrecord-material_desc = file12-value.
      WHEN '0003'.
        wa_asrecord-total_stk   = file12-value.
      WHEN '0004'.
        wa_asrecord-um = file12-value.
      WHEN '0005'.
        wa_asrecord-total_val = file12-value.
      WHEN '0006'.
        wa_asrecord-p = file12-value.
      WHEN '0007'.
        wa_asrecord-unitcost = file12-value.
      WHEN '0008'.
        wa_asrecord-prodcode = file12-value.
      WHEN '0009'.
        wa_asrecord-type = file12-value.
      WHEN '0010'.
        wa_asrecord-per = file12-value.
      WHEN '0011'.
        wa_asrecord-cost = file12-value.
    ENDCASE.
  ENDLOOP.
  APPEND wa_asrecord TO it_asrecord.
  CLEAR wa_asrecord.

Reward guaranteed

thanks

kaki

Former Member · ‎2006 Jun 28

Hi,

Try through this program.

data:

file type string,

path type string,

file_path type string,

return type i..

data: itab type table of string.

data: wa type string.

start-of-selection.

wa = 'This is row1'. append wa to itab.

wa = 'This is row2'. append wa to itab.

call method cl_gui_frontend_services=>file_save_dialog

changing

filename = file

path = path

fullpath = file_path.

check not file_path is initial.

call method cl_gui_frontend_services=>gui_download

exporting

filename = file_Path

changing

data_tab = itab

If helpful reward points.

Thanks,

Rani.

RichHeilman · ‎2006 Jun 28

I don't think that there is actually any problem with you code. I think that you are getting all of your records. If you look at the internal table that is being passed back from the ALSM_EXCEL_TO_INTERNAL_TABLE, the ROW column is only 4 characters big. So the row number like 17565, will look like 7565. I have this test program, where I am seeing this behavior, but it does actually have all of the rows . Is this what is throwing you off?



report zrich_0002.

type-pools: slis.

field-symbols: <dyn_table> type standard table,
               <dyn_wa>,
               <dyn_field>.

data: it_fldcat type lvc_t_fcat,
      wa_it_fldcat type lvc_s_fcat.

type-pools : abap.

data: new_table type ref to data,
      new_line  type ref to data.

data: xcel type table of alsmex_tabline with header line.

selection-screen begin of block b1 with frame title text .
parameters: p_file type  rlgrap-filename default 'c:Test.csv'.
parameters: p_flds type i.
selection-screen end of block b1.

start-of-selection.

* Add X number of fields to the dynamic itab cataelog
  do p_flds times.
    clear wa_it_fldcat.
    wa_it_fldcat-fieldname = sy-index.
    wa_it_fldcat-datatype = 'C'.
    wa_it_fldcat-inttype = 'C'.
    wa_it_fldcat-intlen = 10.
    append wa_it_fldcat to it_fldcat .
  enddo.
  .
* Create dynamic internal table and assign to FS
  call method cl_alv_table_create=>create_dynamic_table
               exporting
                  it_fieldcatalog = it_fldcat
               importing
                  ep_table        = new_table.

  assign new_table->* to <dyn_table>.

* Create dynamic work area and assign to FS
  create data new_line like line of <dyn_table>.
  assign new_line->* to <dyn_wa>.

* Upload the excel
  call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       exporting
            filename                = p_file
            i_begin_col             = '1'
            i_begin_row             = '1'
            i_end_col               = '200'
            i_end_row               = '65000'
       tables
            intern                  = xcel
       exceptions
            inconsistent_parameters = 1
            upload_ole              = 2
            others                  = 3.

* Reformt to dynamic internal table
  loop at xcel.
    assign component xcel-col of structure <dyn_wa> to <dyn_field>.
    if sy-subrc = 0.
      <dyn_field> = xcel-value.
    endif.

    at end of row.
      append <dyn_wa> to <dyn_table>.
      clear <dyn_wa>.
    endat.
  endloop.

* Write out data from table.
  loop at <dyn_table> into <dyn_wa>.
    do.
      assign component  sy-index  of structure <dyn_wa> to <dyn_field>.
      if sy-subrc <> 0.
        exit.
      endif.
      if sy-index = 1.
        write:/ <dyn_field>.
      else.
        write: <dyn_field>.
      endif.
    enddo.
  endloop.

Regards,

Rich Heilman

Former Member · ‎2006 Jun 28

Kaki,

You have set the following parameters as:

i_end_col = '150' i_end_row = '63500'

You mention that the file has 13000 entries.

Why go to 63500 then?

In reviewing the Func Mod, it does a copy of the sheet to the Windows Clipboard. You might be reaching a Clipboard memory limit with the 63500 value.

I would suggest that you try to reduce that number.

Former Member · ‎2006 Jun 28

hi kaki,

try uploading with the FM GUI_UPLOAD..pass filetype as 'DAT'

Former Member · ‎2006 Jun 28

Why dont you use FM

TEXT_CONVERT_XLS_TO_SAP .

Maybe here you wont have any problem.

Former Member · ‎2006 Jun 28

Hello,

If you look at the FM ALSM_EXCEL_TO_INTERNAL_TABLE and how it works, you will see that is uses OLE automation to open EXCEL, load the spreadsheet, mark all the relevat cells, copy those cells to the clipboard and import the clipboard into an internal table.

My guess is, that if you can rule out possible typos in your coding that there is an upper limit to the amount of data that can be imported - e.g. up to 10.000 lines.

Can you split up the large EXCEL file into smaller chunks? Otherwise I would suggest exporting the EXCEL data into a CSV file and the reading in this CSV file using GUI_UPLOAD etc.

Regards, Joerg

RichHeilman · ‎2006 Jun 28

Ok, I managed to re-create your problem. The problem is that you are sorting the FILE12 table and that when the row number is creater than 9999, the "1" in 10000 is stripped off. This is creating two records for each row. This is why you are not seeing all rows. Comment out the line of code that SORTs the table. This table should already be sorted in the correct way anyway.

This will fix the problem.

Regards,

Rich Heilman

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

EXCEL upload into ITAB