Reading long text from excel file to an internal table

[Former Member]

Hi

Can any body tell me how to read long text from excel file to an internal table.

When i am using this FM KCD_EXCEL_OLE_TO_INT_CONVERT then it is reading only 32 characters from each cell.

But in my excel sheet in one of the cell has very long text which i need to upload into a internal table.

may i know which FM or what logic i need to use for this problem.

Regards

[Former Member]

Hi,

Have you tried using CALL METHOD cl_gui_frontend_services=>gui_upload - followed by calling function module 'TEXT_CONVERT_XLS_TO_SAP'?

Regards,

Jamie

[Former Member]

Hello james,

Could you please explain this in detail.

How to use CALL METHOD cl_gui_frontend_services=>gui_upload - followed by calling function module 'TEXT_CONVERT_XLS_TO_SAP'?

[Former Member]

Hi,

Here is an example program. It will upload an Excel file with two columns. You could also assign the Excel structure dynamically, but I wanted to keep the example simple. The main point is that the internal table (it_excel in this example) must match the Excel structure that you want to convert.

Remember, this is just an example to help you figure out how to properly use the technique. It will certainly need to be modified to fit your requirements, and as always there may be a better way to get the Excel converted... this is just one possibility that has worked for me in the past.

*&---------------------------------------------------------------------*
*& Report  zexcel_upload_test                            *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*&                                                                     *
*&---------------------------------------------------------------------*
REPORT  zexcel_upload_test.

TYPE-POOLS: truxs.

TYPES: BEGIN OF ty_excel,
         col_a(10) TYPE n,
         col_b(35) TYPE c,
       END OF ty_excel.

DATA: l_data_tab         TYPE TABLE OF string,
      l_text_data        TYPE truxs_t_text_data,
      l_gui_filename     TYPE string,
      it_excel           TYPE TABLE OF ty_excel.

FIELD-SYMBOLS: <wa_excel>  TYPE ty_excel.

PARAMETERS: p_file TYPE rlgrap-filename.

* Pass the file name in the correct format
l_gui_filename = p_file.

* Upload data from PC
CALL METHOD cl_gui_frontend_services=>gui_upload
  EXPORTING
    filename                = l_gui_filename
    filetype                = 'ASC'
    has_field_separator     = 'X'
  CHANGING
    data_tab                = l_data_tab
  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.
IF sy-subrc <> 0.
*   MESSAGE ...
  EXIT.
ENDIF.

* Convert from Excel into the appropriate itab
l_text_data[] = l_data_tab[].
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
  EXPORTING
    i_field_seperator    = 'X'
    i_tab_raw_data       = l_text_data
    i_filename           = p_file
  TABLES
    i_tab_converted_data = it_excel
  EXCEPTIONS
    conversion_failed    = 1
    OTHERS               = 2.
IF sy-subrc <> 0.
*   MESSAGE ...
  EXIT.
ENDIF.

LOOP AT it_excel ASSIGNING <wa_excel>.
*  Do something here...
ENDLOOP.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  PERFORM filename_get CHANGING p_file.

*---------------------------------------------------------------------*
*       FORM filename_get                                             *
*---------------------------------------------------------------------*
FORM filename_get CHANGING p_in_file TYPE rlgrap-filename.

  DATA: l_in_file  TYPE string,
        l_filetab  TYPE filetable,
        wa_filetab TYPE LINE OF filetable,
        l_rc       TYPE i,
        l_action   TYPE i,
        l_init_dir TYPE string.

* Set the initial directory to whatever you want it to be
  l_init_dir = 'C:\'.

* Call the file open dialog without multiselect
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title            = 'Load file'
      default_extension       = '.XLS'
      default_filename        = l_in_file
      initial_directory       = l_init_dir
      multiselection          = 'X'
    CHANGING
      file_table              = l_filetab
      rc                      = l_rc
      user_action             = l_action
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      OTHERS                  = 4.
  IF sy-subrc <> 0.
    REFRESH l_filetab.
  ENDIF.

* Read the selected filename
  READ TABLE l_filetab INTO wa_filetab INDEX 1.
  IF sy-subrc = 0.
    p_in_file = wa_filetab-filename.
  ENDIF.

ENDFORM.                    " filename_get

Regards,

Jamie

[tom_krutis]

Thank you. This technique works.

[Former Member]

Hi

use Function module ALSM_EXCEL_TO_INTERNAL_TABLE

will help you out

[Former Member]

Hi sabyasachi kar,

I ahve used alsm* but it didn't worked for me.

[rainer_hbenthal]

>

> use Function module ALSM_EXCEL_TO_INTERNAL_TABLE 

This is a very crappy function module

• you have to specify a start row

• you have to specify a end row

• you have to specify a start column

• you have to specify a end column

Columns may be static from file to file, but in most cases the number of rows will vary. You cannot tell your customers to have a look into the excel file to specify those values in your SAP Apllication.

At least, it will not give back a x/y Worksheet, but a table with row/column/value. This is not a very handy format to deal with.

And: someone typed the row/column values as numeric(4). What nasty idea is this?

My consequence: this FM is completly useless.

[former_member185054]

hi sami,

CALL METHOD zacinxxcl0000001=>upload_presentation

    EXPORTING

      file_name    = lv_file

      excel_file   = abap_on

      separator    = lc_tab

    CHANGING

      i_data_table = gi_file

      subrc        = lv_subrc

      MESSAGE      = lv_message.

*   if the data is not uploaded.

  IF lv_subrc <> 0.

    MESSAGE i004(zacsd01) WITH lv_message.

  ENDIF.

check this method,hope it will work.

regards,

sampath kumar