Solved: Issue with GUI_UPLOAD and internal table memory

Former Member · ‎2012 Mar 28

Hi Experts,

I am uploading huge csv file data using FM GUI_UPLOAD.

I have an issue now with the memory of internal table.

I am trying to upload about more than 200k of records using GUI_UPLOAD. The program dump in half way and show runtime error as attached.

I notice that is cursor we can set the package size to read huge table data.

How about reading csv file data? Is there any way to set something like package size to control the amount of row to read the csv data?

My code as below:

TYPES: BEGIN OF tdat,

client(6) TYPE c,

guid(32) TYPE c,

object_id(10) TYPE c,

object_type(11) TYPE c,

process_type(12) TYPE c,

posting_date(12) TYPE c,

description(25) TYPE c,

descr_language(14) TYPE c,

logical_system(14) TYPE c,

crm_release(12) TYPE c,

changed_at(14) TYPE c,

changed_by(14) TYPE c,

END OF tdat.

TYPES: BEGIN OF ttab,

rec(10000) TYPE c,

END OF ttab.

DATA : lt_ttab TYPE TABLE OF ttab,

ls_ttab TYPE ttab,

ls_idat TYPE tdat,

file_str TYPE string,

csv_data TYPE REF TO data.

FIELD-SYMBOLS: <fs_tdat> TYPE tdat,

<fs_ttab> TYPE ttab,

<ft_csv_data> TYPE STANDARD TABLE.

PARAMETERS: p_csv TYPE localfile.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_csv.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

static = 'X'

CHANGING

file_name = p_csv.

START-OF-SELECTION.

file_str = p_csv.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = file_str

TABLES

data_tab = lt_ttab

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.

ASSIGN ls_idat TO <fs_tdat>.

ASSIGN ls_ttab TO <fs_ttab>.

CREATE DATA csv_data TYPE STANDARD TABLE OF tdat.

ASSIGN csv_data->* TO <ft_csv_data>.

LOOP AT lt_ttab INTO <fs_ttab>.

SPLIT <fs_ttab>-rec AT ',' INTO

<fs_tdat>-client

<fs_tdat>-guid

<fs_tdat>-object_id

<fs_tdat>-object_type

<fs_tdat>-process_type

<fs_tdat>-posting_date

<fs_tdat>-description

<fs_tdat>-descr_language

<fs_tdat>-logical_system

<fs_tdat>-crm_release

<fs_tdat>-changed_at

<fs_tdat>-changed_by.

CASE <fs_tdat>-client.

WHEN '450'.

<fs_tdat>-client = '100'.

ENDCASE.

CASE <fs_tdat>-object_id.

WHEN '1730541'.

<fs_tdat>-object_id = '0000000'.

ENDCASE.

APPEND <fs_tdat> TO <ft_csv_data>.

ENDLOOP.

delete <ft_csv_data> index 1. "delete csv first row header

How to enhance the code to limit the size of records to be read each time to example 10000 records each time, then move to second package and so on. Thank you.

Regards,

Honda

Clemenss · ‎2012 Mar 28

Hi Kelvin,

why so complicated? After reading most of the extended discussion of all apsects of life, I came to the conclusion that you read a few bytes per line into a record of 20k bytes (if unicode).

Just try rec(100) TYPE c and extend it if anything gets cut off.

Then the dump will come 1000 times later.

Regards,

Clemens

former_member222709 · ‎2012 Mar 28

Hi Honda,

You can use the FM 'ALSM_EXCEL_TO_INTERNAL_TABLE' within a loop to specify start and end points - say '10,000' records and keep on incrementing the values to read all the records from the file.

I believe you'll manage the code part as there are many posts available for FM - ALSM_EXCEL_TO_INTERNAL_TABLE. For any specific issues further, do post.

Hope this helps!

Regards,

Pranav.

Former Member · ‎2012 Mar 28

Hi Pranav,

Unluckly i do not have the access to FM 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

too bad.

Thanks.

Regards,

Honda

former_member222709 · ‎2012 Mar 28

Hi Honda,

The best way to resolve this is to use CL_GUI_FRONTEND_SERVCES as this is the class used internally within the FM 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

As you can follow the attached code in text format will help you to pass the start and end point references to the class.

Hope this helps.

Regards,

Pranav.

Former Member · ‎2012 Mar 28

Hi Pranav,

I am not so sure about that. Can you briefly explain on that?

Thank you so much..

former_member222709 · ‎2012 Mar 28

Hi Honda,

The new concepts of ABAP OOP's have been used by the Type Group OLE2. Here you can use advanced features of excel like multiple worksheets, password protection, cell colouring, etc.

You can try creating a Z FM with the help of the code attached earlier. The performs will give an error and hence I've attached the performs associated with the above FM. You can try using this Z FM with test parameters.

For further issues, do post.

Regards,

Pranav.

JJosh · ‎2012 Mar 28

Hi,

Declare the internal table as field symbols. so that only during runtime the memory gets allocated and you will be able to upload any no of files..

FIELD-SYMBOL: <ITAB1> TYPE (DECLARE AS REQD).. OR U CAN USE TYPE ANY TABLE..

Former Member · ‎2012 Mar 28

Hi Josh,

I have did declare internal table as field symbol.

CREATE DATA get_data TYPE STANDARD TABLE OF ttab.
ASSIGN get_data->* TO <ft_ttab>.

but yet the program still get dump..

JJosh · ‎2012 Mar 28

HI Kevin,

Try this code :

REPORT  zupload.

*----------------------------------------------------------------------*

*       CLASS lcl_report DEFINITION

*----------------------------------------------------------------------*

*----------------------------------------------------------------------*

CLASS lcl_report DEFINITION.

  PUBLIC SECTION.

    CLASS-METHODS:

      open_file RETURNING value(r_file) TYPE string.

    METHODS:

      read_file IMPORTING i_file TYPE string,

      convert_file2struc IMPORTING i_header TYPE boolean

                                   i_separator TYPE c.

  PRIVATE SECTION.

    CONSTANTS:

      struc_name TYPE string VALUE 'ZST_TEST01'. " Put yours in here

    DATA:

      t_filecontent TYPE string_table,

      d_struc_content TYPE REF TO data.

ENDCLASS.                    "lcl_report DEFINITION

DATA:

  o_report TYPE REF TO lcl_report.

SELECTION-SCREEN BEGIN OF BLOCK file WITH FRAME TITLE text-001.

PARAMETERS:

  p_file TYPE string.

SELECTION-SCREEN END OF BLOCK file.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  p_file = lcl_report=>open_file( ).

START-OF-SELECTION.

  CREATE OBJECT o_report.

  o_report->read_file( i_file = p_file ).

END-OF-SELECTION.

* The file will be loaded in the data object d_struc_content

  o_report->convert_file2struc( i_header    = ''

                                i_separator = '' )."cl_abap_char_utilities=>horizontal_tab ).

*----------------------------------------------------------------------*

*       CLASS lcl_report IMPLEMENTATION

*----------------------------------------------------------------------*

*----------------------------------------------------------------------*

CLASS lcl_report IMPLEMENTATION.

  METHOD open_file.

    DATA:

      l_t_file_table TYPE TABLE OF file_table,

      l_s_file_table TYPE file_table,

      l_rc TYPE i.

    CALL METHOD cl_gui_frontend_services=>file_open_dialog

      CHANGING

        file_table              = l_t_file_table

        rc                      = l_rc

      EXCEPTIONS

        file_open_dialog_failed = 1

        cntl_error              = 2

        error_no_gui            = 3

        not_supported_by_gui    = 4

        OTHERS                  = 5.

    IF sy-subrc <> 0.

      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

    ENDIF.

    READ TABLE l_t_file_table INTO l_s_file_table INDEX 1.

    r_file = l_s_file_table-filename.

  ENDMETHOD.                    "open_file

  METHOD read_file.

    CALL METHOD cl_gui_frontend_services=>gui_upload

      EXPORTING

        filename                = i_file

*        has_field_separator     = 'X'

      CHANGING

        data_tab                = t_filecontent

      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

        not_supported_by_gui    = 17

        error_no_gui            = 18

        OTHERS                  = 19.

    IF sy-subrc <> 0.

      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

    ENDIF.

  ENDMETHOD.                    "read_file

  METHOD convert_file2struc.

    DATA:

      l_struc TYPE REF TO data,

      l_o_datadescr TYPE REF TO cl_abap_datadescr,

      l_o_tabledescr TYPE REF TO cl_abap_tabledescr,

      l_t_fields TYPE string_table,

      l_s_fields TYPE string.

    FIELD-SYMBOLS:

      <l_filecontent> TYPE ANY,

      <l_field> TYPE ANY,

      <l_row> TYPE ANY,

      <l_struc_content> TYPE table.

* Create the structure

    l_o_datadescr ?= cl_abap_datadescr=>describe_by_name( struc_name ).

    CREATE DATA l_struc TYPE HANDLE l_o_datadescr.

    ASSIGN l_struc->* TO <l_row>.

* Create the table

    TRY.

        CALL METHOD cl_abap_tabledescr=>create

          EXPORTING

            p_line_type = l_o_datadescr

          RECEIVING

            p_result    = l_o_tabledescr.

      CATCH cx_sy_table_creation .

    ENDTRY.

    CREATE DATA d_struc_content TYPE HANDLE l_o_tabledescr.

    ASSIGN d_struc_content->* TO <l_struc_content>.

    IF i_header = abap_true.

      DELETE t_filecontent INDEX 1.

    ENDIF.

    LOOP AT t_filecontent ASSIGNING <l_filecontent>.

      IF i_separator IS INITIAL. " File is same as structure

        <l_row> = <l_filecontent>.

      ELSE.

*---split based on the separator

        SPLIT <l_filecontent> AT i_separator INTO TABLE l_t_fields.

        LOOP AT l_t_fields INTO l_s_fields.

          ASSIGN COMPONENT sy-tabix OF STRUCTURE <l_row> TO <l_field>.

          <l_field> = l_s_fields.

        ENDLOOP.

      ENDIF.

      APPEND <l_row> TO <l_struc_content>.

    ENDLOOP.

  ENDMETHOD.                    "convert_file2struc

ENDCLASS.                    "lcl_report IMPLEMENTATION

JJosh · ‎2012 Mar 28

HI Kevin,

Class name:
CALL METHOD cl_gui_frontend_services=>file_open_dialog EXPORTING window_title = 'Select Source Excel File' default_filename = '*.xls' multiselection = ' ' CHANGING file_table = lt_it_tab rc = lv_subrc.

Try with this class....

Regards,

Josh

Former Member · ‎2012 Mar 28

HI Jacob,

I will have a try on this.

Please let me know if found anyway to define package size to read csv file data.

Thank you.

Regards,

Kelvin

Former Member · ‎2012 Mar 28

Hi

Pls try to define and try

Instead of

TYPES: BEGIN OF ttab,

rec(10000) TYPE c,

END OF ttab.

Declare

TYPES: BEGIN OF ttab,

rec(200) TYPE c,

END OF ttab.

former_member222707 · ‎2012 Mar 28

Hi Kelvin,

Best best way to deal with huge records of file is dataset. Place your data file is AS and use dataset to process them line by line.

Regards,

Rakesh.

Former Member · ‎2012 Mar 28

Hi Rakesh,

Thanks for the info. I will work on it first and get back to you if any issue.

Regards,

Kelvin

Former Member · ‎2012 Mar 28

Hi Rakesh,

Dataset seems not working for csv file? Error shows file cannot open and sy-subrc = 8.

DATA : file_str TYPE string.

DATA : BEGIN OF ITAB OCCURS 0,

COL1(1024) TYPE C,

END OF ITAB,

WA_ITAB LIKE LINE OF ITAB.

PARAMETERS: p_csv TYPE localfile.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_csv.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

static = 'X'

CHANGING

file_name = p_csv.

START-OF-SELECTION.

file_str = p_csv.

OPEN DATASET file_str FOR INPUT IN TEXT MODE ENCODING NON-UNICODE.

IF SY-SUBRC = 8.

WRITE:/ 'File' , file_str , 'cannot be opened'.

EXIT.

ENDIF.

WHILE SY-SUBRC <> 4.

READ DATASET file_str INTO WA_ITAB.

APPEND WA_ITAB TO ITAB.

ENDWHILE.

CLOSE DATASET file_str.

Thank you.

Regards,

Kelvin

Former Member · ‎2012 Mar 28

An 8 would indicate that SAP does not have permissions to open/read a file in that particular folder. Store where accessible by SAP or modify the permissions on the folder and/or the file.

former_member222707 · ‎2012 Mar 29

Dear,

Kelvin as David Lindsey has told that we cant open file using data from Presentation system.
You have to put your file in Application server than olny it will work.

Regards,

Rakesh

Former Member · ‎2012 Mar 28

The amount of data transferred is most probably directly related to the ability of the clipboard to store and transfer from desktop to SAP. This is probably around 32Mb, at max? At any rate, for very, very large files, one would segregate the file (if possible) logically, and then store on the apps server as tab-delimited (.txt) file(s) in a folder accessible by SAP. A 200K rows file could result in exceeding maximum size of the user context, so care is required when using internal tables, etc., of this size. FREE internal data tables as soon as no longer needed, etc., to minimize memory requirements.

Clemenss · ‎2012 Mar 28

Hi Kelvin,

why so complicated? After reading most of the extended discussion of all apsects of life, I came to the conclusion that you read a few bytes per line into a record of 20k bytes (if unicode).

Just try rec(100) TYPE c and extend it if anything gets cut off.

Then the dump will come 1000 times later.

Regards,

Clemens

Former Member · ‎2012 Mar 29

Hi Clemens,

Agree with your answer. Now able to read more than 200K records.

But not sure whats happening when reading up to millions of records.

I have another question. I have done a program that able to read no matter how many millions of records from transparent table to transparent table. Is there any method in SAP that work exactly like below code to process CVS file records by batches?

CREATE DATA source_data TYPE STANDARD TABLE OF (p_source).
ASSIGN source_data->* TO <ft_source_table>.

* get data from source table and fetch into cursor
   OPEN CURSOR WITH HOLD cursor_source_data FOR SELECT * FROM (p_source) BYPASSING BUFFER.

   DO.
     FETCH NEXT CURSOR cursor_source_data INTO TABLE <ft_source_table> PACKAGE SIZE 5000.

     IF sy-subrc = 0.
       MODIFY (p_target) FROM TABLE <ft_source_table>.
       EXEC SQL.
         COMMIT WORK
       ENDEXEC.
     ELSEIF sy-subrc <> 0.
       CLOSE CURSOR cursor_source_data.
       EXIT.
     ENDIF.
   ENDDO.

Thank you.

Regards,

Kelvin

Private_Member_49934 · ‎2012 Mar 29

kelvin I think you can do it programatically using the syntax give in sap help .

1) Open the data set.

2) Read specific size of the file in internal table. Track the number of byte read in a variable

3) close the data set.

4) Process the internal table and go back to step one opening at the position set in the variable at step 2

Opening a File at a Given Position

To open a file at a particular position, use the AT POSITION addition in the OPEN DATASET statement.
Syntax
OPEN DATASET <dsn> [FOR ....] [IN ... MODE] AT POSITION <pos>.
This statement opens the file <dsn>, and prepares it for reading or writing from position <pos>. <pos> is the number of bytes from the beginning of the file. It is not possible to specify a position before the beginning of the file.

Former Member · ‎2012 Mar 29

Hi Kelvin,

After FM 'GUI_UPLOAD', the FM 'TEXT_CONVERT_CSV_TO_SAP' will help.

Regards.

former_member222707 · ‎2012 Mar 29

Dear,

Kelvin here is a sample code of Dataset. first try with basics and then go for the your requirment.

DATA : w_appfile(150) TYPE c VALUE '/data/fi/kannantest'."Given a proper path.

data : t_mara TYPE STANDARD TABLE OF mara,
        l_mara TYPE mara,
        v_data type string.

START-OF-SELECTION.

SELECT matnr
        meins UP TO 10 ROWS FROM
        mara INTO CORRESPONDING FIELDS OF TABLE
        t_mara.

DELETE DATASET w_appfile.
open dataset w_appfile for OUTPUT in TEXT MODE ENCODING default.

loop at t_mara into l_mara.

concatenate l_mara-matnr ','
             l_mara-meins ','
             into v_data.
transfer v_data to w_appfile.
clear: v_data.

endloop.

close dataset w_appfile.

open dataset w_appfile for INPUT in TEXT MODE ENCODING default.

do.
   READ DATASET w_appfile into v_data.
   if sy-subrc <> 0.
     exit.
   ENDIF.
enddo.

Former Member · ‎2012 Mar 29

Hi Rakesh,

Too bad I do not have the access to upload file to application server.

Anyway thanks for your effort.

At this moment by using FM GUI_UPLOAD, i am able to read up to 1,213,560 millions records into the internal table.

Hope it should also work if amount of records are more than that.

Anyway i still prefer something to process the records in batch.

I think dataset should be able to work without dump no matter how many records are. Will try if have the access to upload flatfile to application server.

Regards,

Kelvin

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

Issue with GUI_UPLOAD and internal table memory