topic Re: Upload Excel data to internal table in Application Development and Automation Discussions

Upload Excel data to internal table

Former Member — Thu, 02 Feb 2012 01:38:53 GMT

Hello,

Any idea how to import a HUGE Excel .xlsx file (> 150K rows) into an internal table?

I have explored the following FMs but it seems like none of them can handle more than 65K rows.

ALSM_EXCEL_TO_INTERNAL_TABLE

KCD_EXCEL_OLE_TO_INT_CONVERT

TEXT_CONVERT_XLS_TO_SAP

GUI_UPLOAD

Thanks in advance.

Regards

Re: Upload Excel data to internal table

Former Member — Thu, 02 Feb 2012 01:51:25 GMT

Hi, save .xlsx file as .csv and try uploading with gui_upload

Cheers,

Thomas.

Re: Upload Excel data to internal table

surajarafath — Thu, 02 Feb 2012 04:29:14 GMT

Upload excel data into XSTRING and Convert it into internal table.

Re: Upload Excel data to internal table

Former Member — Thu, 02 Feb 2012 04:35:20 GMT

Hi,

Check the bellow thred for your requirement.

Regards,

Goutam Kolluru.

Re: Upload Excel data to internal table

sreenivas_pachva — Thu, 02 Feb 2012 05:20:46 GMT

Hi,

Try this below code

Note

you should change the program based on your number of fields of excel sheet. if you have five fields

then you can adjust structure to 5 fields

* Here based on excell sheet fileds we can create number of fields

TYPES:
BEGIN OF ty_upload,
F1 TYPE c length 15,
F2 TYPE c length 15,
F3 TYPE c length 15,
END OF ty_upload.



DATA it_upload TYPE STANDARD TABLE OF ty_upload WITH DEFAULT KEY.
DATA wa_upload TYPE ty_upload.

DATA itab TYPE STANDARD TABLE OF alsmex_tabline WITH DEFAULT KEY.
FIELD-SYMBOLS: <wa> type alsmex_tabline.

* create variable to hold the file name of presentation server

PARAMETERS: p_file TYPE  rlgrap-filename.
* 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 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
**filename = filename

filename = p_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 3
i_end_row = 65535
TABLES
intern = itab.

LOOP AT itab ASSIGNING <wa>.


CASE <wa>-col.
WHEN '0001'.
wa_upload-F1 = <wa>-value.
WRITE:<wa>-value.
WHEN '0002'.
wa_upload-F2 = <wa>-value.
WRITE:<wa>-value.
WHEN '0003'.
wa_upload-F3 = <wa>-value.
WRITE:<wa>-value.
ENDCASE.
APPEND wa_upload TO it_upload.
CLEAR wa_upload.
ENDLOOP.

and also try the function module : TEXT_CONVERT_XLS_TO_SAP

Re: Upload Excel data to internal table

Former Member — Thu, 02 Feb 2012 06:05:14 GMT

It is confirmed ALSM_EXCEL_TO_INTERNAL_TABLE won't work on more than 65K rows. Will convert the .xlsx file to tab delimited text file for upload.

Thanks to all.

Re: Upload Excel data to internal table

Former Member — Thu, 02 Feb 2012 06:28:04 GMT

Hi Sou,

Prepare the structure of the required fields.

Generate the excel sheet of the required field and then save the data in the excel sheet and then save as the same excel file in the tab delimited format and upload the same using fm GUI_UPLOAD.

Regards,

akshay ruia