Uploading excel with 48000 records into abap internal table

[former_member227580]

Hi Colleagues,

     I am trying to upload an excel with 48000 records into abap internal (dynpro program) table through a Function Module, I used the following FM's

1. GUI_UPLOAD: I tried this function modules by loading the excel as there is no restriction of no. of rows, but it does not process them properly and on further reading about this function module, I found out that it is not recommended to use this FM for uploading excel.
   
   
2. TEXT_CONVERT_XLS_TO_SAP: Even for this FM, max rows specification is not required, but when loaded with the excel sent by the customer, it dumped as it could not handle the content of the excel.
   
   
3. ZFAA_FILE_UPLOAD_EXCEL: I simply extended the no. of max rows in the original FM FAA_FILE_UPLOAD_EXCEL to 60,000 rows which is approximately the max number of rows provided by MS Excel 2003, and I was able to upload those many records (48,000). The FAA_FILE_UPLOAD_EXCEL uses the FM ALSM_EXCEL_TO_INTERNAL_TABLE for which the max no. of rows to be specified is mandatory.
   
   

    But Sometimes the abap server might throw an exception of memory paging issue, I had observed this when I was uploading the 48000 records in the morning it got uploaded, but the same program when I ran in the afternoon, I got memory dumps. So I tried lowering the max rows and at 27000 records limit I was able to upload. So the abap system memory seems variable & is making me figure hard to set max rows value.

        Can you kindly let me know which function module to use which could possibly load up these 48000 records into the database? Is there any basis based function module which I could explore for loading the excel?

Thanks & Regards,

Deepak

[Former Member]

Do you know why GUI_UPLOAD isn't recommended? I've used this to upload excel files with 100,000+ rows before by saving the excel as a .csv (or a tab delimited file). You can then split each row at your delimiter to get the specific values you're looking for. I am not claiming that this is the most efficient way and I don't know what kind of wait time is acceptable in your requirements, but it should be doable.

[former_member197445]

I have had success with TEXT_CONVERT_XLS_TO_SAP.  I recommend it.  Is your dump a security issue, maybe?

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

       EXPORTING

         i_field_seperator          = 'X'

*         I_LINE_HEADER              =

         i_tab_raw_data             = wa_raw

         i_filename                 = p_file1

       TABLES

         i_tab_converted_data       = t_input[]

      EXCEPTIONS

         conversion_failed          = 1

         OTHERS                     = 2

               .

[former_member182040]

See the following Exmaple:

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.

   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_datatab[]    "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.