topic Re: Issue with GUI_UPLOAD and internal table memory in Application Development and Automation Discussions

Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 05:49:55 GMT

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

Re: Issue with GUI_UPLOAD and internal table memory

former_member222709 — Wed, 28 Mar 2012 06:20:18 GMT

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.

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 06:25:52 GMT

Hi Pranav,

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

too bad.

Thanks.

Regards,

Honda

Re: Issue with GUI_UPLOAD and internal table memory

former_member222709 — Wed, 28 Mar 2012 06:39:15 GMT

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.

Re: Issue with GUI_UPLOAD and internal table memory

JJosh — Wed, 28 Mar 2012 06:41:06 GMT

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

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 08:04:54 GMT

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

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 08:09:37 GMT

Hi Pranav,

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

Thank you so much..

Re: Issue with GUI_UPLOAD and internal table memory

former_member222709 — Wed, 28 Mar 2012 08:33:34 GMT

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.

Re: Issue with GUI_UPLOAD and internal table memory

JJosh — Wed, 28 Mar 2012 08:46:44 GMT

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

Re: Issue with GUI_UPLOAD and internal table memory

JJosh — Wed, 28 Mar 2012 08:50:16 GMT

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

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 09:46:01 GMT

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

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 09:57:51 GMT

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.

Re: Issue with GUI_UPLOAD and internal table memory

former_member222707 — Wed, 28 Mar 2012 10:10:41 GMT

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.

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 10:26:57 GMT

Hi Rakesh,

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

Regards,

Kelvin

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 14:51:06 GMT

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

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 17:23:46 GMT

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.

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Wed, 28 Mar 2012 19:27:38 GMT

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.

Re: Issue with GUI_UPLOAD and internal table memory

Clemenss — Wed, 28 Mar 2012 22:10:44 GMT

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

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Thu, 29 Mar 2012 01:32:29 GMT

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

Re: Issue with GUI_UPLOAD and internal table memory

Former Member — Thu, 29 Mar 2012 02:17:58 GMT

Hi Kelvin,

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

Regards.