topic Re: Upload EXCEL to Internal Table in Application Development and Automation Discussions

Upload EXCEL to Internal Table

former_member125661 — Wed, 29 Nov 2006 22:25:10 GMT

I have an excel sheet with more than 10000 entries. I have tried to use various function modules to upload them to ITAB. But most of the FM's just take first 10000 rows. Is there any solution for this ? I use sap 4.6 b & 4.6 c.

Here are some of the FMs I tried and failed :

/UBFNA/EXCEL_TO_ITAB

ALSM_EXCEL_TO_INTERNAL_TABLE

. Is there any way I can use GUI_UPLOAD to upload excel file with more than 10000 entries ? If so, what should be the Filetype parameter. I used DAT and WK1, the prog gives a dump. Thanks in advance.

Re: Upload EXCEL to Internal Table

Former Member — Wed, 29 Nov 2006 22:26:52 GMT

Hi,

Check the function module TEXT_CONVERT_XLS_TO_SAP

Thanks,

Naren

Re: Upload EXCEL to Internal Table

Former Member — Wed, 29 Nov 2006 22:39:25 GMT

Hi,

Check GUI_UPLOAD. Set the parameter 'HAS_FIELD_SEPARATOR' .

Directly pass the internal table to the function module.

Regards,

Vara

Re: Upload EXCEL to Internal Table

former_member125661 — Wed, 29 Nov 2006 22:47:29 GMT

I used it. I am not sure what to pass to the mandatory parameter i_tab_raw_data. I passed empty and it gave me conversion error.



REPORT ZGUITEST2 .


TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE  rlgrap-filename.

DATA : IT_TAB_DLUF TYPE STANDARD TABLE OF ZMM_VENDOR_CORRL WITH HEADER
    LINE .



DATA: it_raw TYPE truxs_t_text_data.

* At selection screen
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_FIELD_SEPERATOR        =
*      i_line_header            =  'X'
      i_tab_raw_data           =  it_raw       " WORK TABLE
      i_filename               =  p_file
    TABLES
      i_tab_converted_data     = IT_TAB_DLUF[]    "ACTUAL DATA
   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.

Re: Upload EXCEL to Internal Table

former_member125661 — Wed, 29 Nov 2006 22:57:21 GMT

I tried to use GUI_UPLOAD . In 4.6b, it doesn't have the HAS SEPERATOR option.THe only parameters available are as follows

*" REFERENCE(FILENAME) LIKE RLGRAP-FILENAME

*" DEFAULT SPACE

*" REFERENCE(FILETYPE) LIKE RLGRAP-FILETYPE

*" DEFAULT 'ASC'

*" EXPORTING

*" VALUE(FILELENGTH) TYPE I

*" TABLES

*" DATA_TAB

*" EXCEPTIONS

*" FILE_OPEN_ERROR

*" FILE_READ_ERROR

*" NO_BATCH

*" GUI_REFUSE_FILETRANSFER

*" INVALID_TYPE

Re: Upload EXCEL to Internal Table

Former Member — Wed, 29 Nov 2006 22:58:22 GMT

Hi,

The excel file should match the structure of your internal table including the mandt..

Thanks,

Naren

Re: Upload EXCEL to Internal Table

former_member125661 — Wed, 29 Nov 2006 23:00:14 GMT

Naren..ofcourse, I have it in the same format including the MANDT field.

Re: Upload EXCEL to Internal Table

Former Member — Wed, 29 Nov 2006 23:06:44 GMT

Hi,

I tried with MARA..WIth the input as the following..It worked fine...

Once you execute it will display which conversion failed..Like Invalid date..

10 ASDFASDF 10.10.2006

Code

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE rlgrap-filename.

DATA : IT_TAB_DLUF TYPE STANDARD TABLE OF <b>MARA</b> WITH HEADER

LINE .

DATA: it_raw TYPE truxs_t_text_data.

At selection screen

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_FIELD_SEPERATOR =
i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = IT_TAB_DLUF[] "ACTUAL DATA

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.

Thanks,

Naren

Re: Upload EXCEL to Internal Table

Former Member — Wed, 29 Nov 2006 23:24:22 GMT

Check the code below.

It will uload the excel to Internal table.

Excel format is specified at the end of the code.

Shejal.

REPORT ZTEST_12 .

tables : zvbrp_1.

data : g_repid like sy-repid,

$v_start_col type i value '1',

$v_start_row type i value '1',

$v_end_col type i value '256',

$v_end_row type i value '65536',

gd_currentrow type i.

DATA : BEGIN OF it_table OCCURS 0,

vbeln LIKE zvbrp_1-vbeln,

posnr LIKE zvbrp_1-posnr,

uepos LIKE zvbrp_1-uepos,

netwr LIKE zvbrp_1-netwr,

matnr LIKE zvbrp_1-matnr,

END OF it_table.

data: itab like alsmex_tabline occurs 0 with header line.

selection-screen : begin of block blk with frame title text.

parameters : p_file like rlgrap-filename obligatory.

selection-screen : end of block blk.

initialization.

g_repid = sy-repid.

at selection-screen on value-request for p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = g_repid

IMPORTING

FILE_NAME = p_file.

start-of-selection.

Get the data from XLS to Internal Table

perform upload_data.

&----

*& Form upload_data

&----

text

----

--> p1 text
<-- p2 text

----

FORM upload_data.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_file

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

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

write:/10 'File '.

ENDIF.

if sy-subrc eq 0.

read table itab index 1.

gd_currentrow = itab-row.

loop at itab.

if itab-row ne gd_currentrow.

append it_table.

clear it_table.

gd_currentrow = itab-row.

endif.

case itab-col.

when '0001'.

it_table-vbeln = itab-value.

when '0002'.

it_table-posnr = itab-value.

when '0003'.

it_table-uepos = itab-value.

when '0004'.

it_table-netwr = itab-value.

when '0005'.

it_table-matnr = itab-value.

endcase.

endloop.

endif.

append it_table.

ENDFORM. " upload_data

vbeln posnr uepos netwr matnr

10 10 0 0 1

10 20 10 2 2

10 30 10 3 3

10 40 0 0 4

10 50 40 5 5

10 60 0 6 6

20 10 0 7 7

20 20 0 0 8

20 30 20 9 9

20 40 20 10 10

Re: Upload EXCEL to Internal Table

former_member125661 — Thu, 30 Nov 2006 01:26:09 GMT

Shejal...

The FM 'ALSM_EXCEL_TO_INTERNAL_TABLE' doesnt work when no. of rowsin the excel sheet exceed 10000. Check it out!!

Re: Upload EXCEL to Internal Table

Former Member — Thu, 30 Nov 2006 03:22:06 GMT

Hi,

Use the following code, It suite's your requirement.

data: lt_intern type kcde_cells occurs 0 with header line.

data: ld_index type i.

field-symbols: <fs>.

call function 'KCD_EXCEL_OLE_TO_INT_CONVERT'

exporting

filename = p_fname

i_begin_col = 1

i_begin_row = 1

i_end_col = 256

i_end_row = 65536

tables

intern = lt_intern

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

if sy-subrc eq 0.

sort lt_intern by row col.

delete lt_intern from 1 to 18.

loop at lt_intern.

move lt_intern-col to ld_index.

assign component ld_index of structure itab to <fs>.

move lt_intern-value to <fs>.

at end of row.

append itab. clear itab.

endat.

endloop.

endif.

Regards,

Rajesh.

Reward points if useful

Re: Upload EXCEL to Internal Table

former_member125661 — Thu, 30 Nov 2006 10:14:47 GMT

Rajesh..this doesn't upload more than 10000 rows of excel..Have already tried it.

Re: Upload EXCEL to Internal Table

anversha_s — Thu, 30 Nov 2006 10:28:00 GMT

hi,

pls try GUI_UPLOAD.

types: begin of ttab,
       rec(1000) type c,
       end of ttab.
 
types: begin of tdat,
       fld1(10) type c,
       fld2(10) type c,
       fld3(10) type c,
       end of tdat.
 
data: itab type table of ttab with header line.
data: idat type table of tdat with header line.
 
data: file_str type string.
 
parameters: p_file type localfile.
 
at selection-screen on value-request for p_file.
  call function 'KD_GET_FILENAME_ON_F4'
       exporting
            static    = 'X'
       changing
            file_name = p_file.
 
start-of-selection.
 
  file_str = p_file.
 
  call function 'GUI_UPLOAD'
       exporting
            filename                = file_str
       tables
            data_tab                = itab
       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.
 
 
delete itab index 1.
 
  loop at itab.
    clear idat.
    split itab-rec at cl_abap_char_utilities=>horizontal_tab
                          into idat-fld1
                               idat-fld2
                               idat-fld3.
    append idat.
 
  endloop.
 
 
  loop at idat.
    write:/ idat-fld1, idat-fld2, idat-fld3.
  endloop.

rgds

Anver