topic Re: Read multiple excel sheets using dataset... in Application Development and Automation Discussions

Read multiple excel sheets using dataset...

aris_hidalgo — Tue, 22 Sep 2009 06:36:06 GMT

Hello Experts,

I've searched the forums and I cannot find any suitable threads for regarding my question. Anyway, I wanto to read and .XLS file residing in the application server using the DATASET commands.However, I am having a shortdump saying "An exception with the type CX_SY_CONVERSION_CODEPAGE".

Re: Read multiple excel sheets using dataset...

Former Member — Tue, 22 Sep 2009 06:41:19 GMT

Hi,

Try using the open dataset like this,


OPEN DATASET  filename for input IGNORING CONVERSION ERRORS.

Regards,

Vikranth

Re: Read multiple excel sheets using dataset...

aris_hidalgo — Tue, 22 Sep 2009 06:48:39 GMT

Hi Vikrnath,

I followed what you said but when I checked it it has a lof of '#' values? this is an XLS file.

Re: Read multiple excel sheets using dataset...

Former Member — Tue, 22 Sep 2009 06:54:41 GMT

Hi,

is nothing but Horizontal tab. After reading the dataset into a internal table, use this



loop at it_file.
replace all occurences of CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB in it_file with space.
modify it_file.
endloop.

Regards,

Vikranth

Re: Read multiple excel sheets using dataset...

venkat_o — Tue, 22 Sep 2009 07:05:10 GMT

Hi,

Try to check this link. It will be helpful.

Thanks

Venkat.O

Re: Read multiple excel sheets using dataset...

venkat_o — Tue, 22 Sep 2009 07:13:04 GMT

Hi, Try this way to download excel file from application server


 REPORT ztest_program.
DATA: BEGIN OF it_t001 OCCURS 0,
        bukrs TYPE t001-bukrs,
        butxt TYPE t001-butxt,
        ort01 TYPE t001-ort01,
        land1 TYPE t001-land1,
        waers TYPE t001-waers,
        spras TYPE t001-spras,
        ktopl TYPE t001-ktopl,
        waabw TYPE t001-waabw,
      END OF it_t001.
DATA: BEGIN OF it_app_file OCCURS 0,
        data TYPE string,
      END OF it_app_file.
CLASS cl_abap_char_utilities DEFINITION LOAD.
CONSTANTS:hor_tab  TYPE c VALUE cl_abap_char_utilities=>horizontal_tab.
PARAMETERS : p_file(150) TYPE c.
START-OF-SELECTION.
  "Open file for reading
  OPEN DATASET p_file FOR INPUT IN TEXT MODE ENCODING DEFAULT.
  IF sy-subrc EQ 0.
    DO.
      READ DATASET p_file INTO it_app_file-data.
      IF it_app_file IS INITIAL.
        "If file ends or if does not have data
        EXIT.
      ELSE.
        "Splitting TAB delimited data into into internal table
        SPLIT it_app_file-data AT hor_tab INTO it_t001-bukrs
                                               it_t001-butxt
                                               it_t001-ort01
                                               it_t001-land1
                                               it_t001-waers
                                               it_t001-spras
                                               it_t001-ktopl
                                               it_t001-waabw.
        APPEND it_t001.
        CLEAR  it_t001.
      ENDIF.
    ENDDO.
  ENDIF.
  "close file
  CLOSE DATASET p_file.
  LOOP AT it_t001.
    WRITE:/ it_t001-bukrs,
            it_t001-butxt,
            it_t001-ort01,
            it_t001-land1,
            it_t001-waers,
            it_t001-spras,
            it_t001-ktopl,
            it_t001-waabw.
  ENDLOOP.

Thanks Venkat.O

Re: Read multiple excel sheets using dataset...

aris_hidalgo — Tue, 22 Sep 2009 07:31:47 GMT

Hi again,

I tried using command 'REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab IN lw_string_itab WITH space' but the '#' is still not replaced.

Re: Read multiple excel sheets using dataset...

Former Member — Tue, 22 Sep 2009 07:34:27 GMT

Hi,

Can you post the code you tried with?

Re: Read multiple excel sheets using dataset...

aris_hidalgo — Tue, 22 Sep 2009 07:51:57 GMT

Here it is:

OPEN DATASET im_filename FOR INPUT IGNORING CONVERSION ERRORS IN TEXT MODE ENCODING DEFAULT.
DO.
            READ DATASET im_filename INTO lw_string_itab.
            IF sy-subrc <> 0.
              EXIT.
            ELSE.
              ADD 1 TO lv_row.
*             Do not process rows less than the defined start row
              IF lv_row < im_start_row.
                CONTINUE.
              ENDIF.

              REFRESH lt_split_itab.

              REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab
                                      IN lw_string_itab WITH space.

*             Split values by delimiter
              SPLIT lw_string_itab
                 AT im_delimiter
               INTO TABLE lt_split_itab.

              IF sy-subrc = 0.
                LOOP AT lt_split_itab INTO lw_split_itab.
                  CLEAR lw_fieldcat.
                  READ TABLE lt_fieldcat INTO lw_fieldcat INDEX sy-tabix.
                  IF sy-subrc = 0.
                    ASSIGN COMPONENT lw_fieldcat-fieldname OF STRUCTURE <fs_dyn_wa> TO <fs_field>.
                    IF <fs_field> IS ASSIGNED.
                      <fs_field> = lw_split_itab.
                    ENDIF.
                  ENDIF.
                ENDLOOP.
              ENDIF.

              APPEND <fs_dyn_wa> TO <fs_dyn_table>.
              FREE <fs_dyn_wa>.
            ENDIF.
          ENDDO.

Re: Read multiple excel sheets using dataset...

venkat_o — Tue, 22 Sep 2009 07:59:48 GMT

Hi, Try to look into this .


      REPLACE ALL OCCURRENCES OF CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB
                              IN LW_STRING_ITAB WITH IM_DELIMITER."SPACE. instead of space i have added IM_DELIMITER

Thanks Venkat.O

Re: Read multiple excel sheets using dataset...

aris_hidalgo — Tue, 22 Sep 2009 08:24:27 GMT

Hi,

It is still the same.

Re: Read multiple excel sheets using dataset...

Former Member — Tue, 22 Sep 2009 08:29:35 GMT

Hi,

I suppose lw_string_itab is a internal table. If it is so you will have to loop at it and then try replacing



loop at lw_string_itab.
REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab
                                      IN lw_string_itab-field1 WITH space.                      " Mention the particular field 
modify  lw_string_itab.
endloop.

Regards,

Vikranth

Re: Read multiple excel sheets using dataset...

Former Member — Tue, 22 Sep 2009 08:30:10 GMT

Hii,

Can u please try the below statement.

REPLACE ALL OCCURRENCES OF '#' in test WITH space.

Thanks,

Ravindra.

Re: Read multiple excel sheets using dataset...

venkat_o — Tue, 22 Sep 2009 09:04:34 GMT

Hi,

May I know the declaration of lw_string_itab structure.

Thanks

Venkat.O

Re: Read multiple excel sheets using dataset...

venkat_o — Tue, 22 Sep 2009 09:25:01 GMT

Hi, As I have given example code above,


        SPLIT it_app_file-data AT hor_tab INTO it_t001-bukrs
                                               it_t001-butxt
                                               it_t001-ort01
                                               it_t001-land1
                                               it_t001-waers
                                               it_t001-spras
                                               it_t001-ktopl
                                               it_t001-waabw.

"Here I have given  it_app_file-data

You need use your structure field in the below places.

              REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab
                                      IN lw_string_itab-data WITH im_delimiter. "lw_string_itab-data
 
*             Split values by delimiter
              SPLIT lw_string_itab-data   "lw_string_itab-data
                 AT im_delimiter
               INTO TABLE lt_split_itab.

I hope that you are following. Thanks Venkat.O

Re: Read multiple excel sheets using dataset...

aris_hidalgo — Tue, 22 Sep 2009 09:53:50 GMT

Hi,

It is a type table of string.

Re: Read multiple excel sheets using dataset...

Former Member — Tue, 22 Sep 2009 10:16:45 GMT

Example


REPORT  ZREAD_MULTIPLESHEETS_EXCEL.
DATA:
oref_container   TYPE REF TO cl_gui_custom_container,
iref_control     TYPE REF TO i_oi_container_control,
iref_document    TYPE REF TO i_oi_document_proxy,
iref_spreadsheet TYPE REF TO i_oi_spreadsheet,
iref_error       TYPE REF TO i_oi_error.

DATA:
v_document_url TYPE c LENGTH 256,
i_sheets TYPE soi_sheets_table,
wa_sheets TYPE soi_sheets,
i_data        TYPE soi_generic_table,
wa_data       TYPE soi_generic_item,
i_ranges      TYPE soi_range_list.
PARAMETERS:
p_file  TYPE  localfile OBLIGATORY,
p_rows TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536)
p_cols TYPE i DEFAULT 10 OBLIGATORY.    "Columns (Maximum 256)
INITIALIZATION.
  CALL METHOD c_oi_container_control_creator=>get_container_control
     IMPORTING
       control = iref_control
       error   = iref_error
*      retcode =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.
  CREATE OBJECT oref_container
    EXPORTING
       container_name              = 'CONT'
    EXCEPTIONS
       cntl_error                  = 1
       cntl_system_error           = 2
       create_error                = 3
       lifetime_error              = 4
       lifetime_dynpro_dynpro_link = 5
       OTHERS                      = 6.
  IF sy-subrc <> 0.
    MESSAGE e001(00) WITH 'Error while creating container'.
  ENDIF.

  CALL METHOD iref_control->init_control
    EXPORTING
       inplace_enabled          = 'X'
       r3_application_name      = 'EXCEL CONTAINER'
       parent                   = oref_container
    IMPORTING
       error                    = iref_error
    EXCEPTIONS
       javabeannotsupported     = 1
       OTHERS                   = 2        .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

  CALL METHOD iref_control->get_document_proxy
    EXPORTING
      document_type      = soi_doctype_excel_sheet
     IMPORTING
       document_proxy     = iref_document
       error              = iref_error .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

Edited by: krupa jani on Sep 22, 2009 12:17 PM

Re: Read multiple excel sheets using dataset...

Former Member — Tue, 22 Sep 2009 10:22:20 GMT

continue........


AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

* To provide F4 help for the file
  PERFORM sub_file_f4.

START-OF-SELECTION.

  CONCATENATE 'FILE://' p_file INTO v_document_url.

  CALL METHOD iref_document->open_document
    EXPORTING
      document_title   = 'Excel'
      document_url     = v_document_url
*     no_flush         = ' '
      open_inplace     = 'X'
*     open_readonly    = ' '
*     protect_document = ' '
*     onsave_macro     = ' '
*     startup_macro    = ''
*     user_info        =
    IMPORTING
      error            = iref_error
*     retcode          =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.

  CALL METHOD iref_document->get_spreadsheet_interface
     EXPORTING
       no_flush        = ' '
    IMPORTING
      error           = iref_error
      sheet_interface = iref_spreadsheet
*     retcode         =
      .

  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.

  CALL METHOD iref_spreadsheet->get_sheets
      EXPORTING
       no_flush = ' '
*      updating = -1
     IMPORTING
       sheets   = i_sheets
       error    = iref_error
*      retcode  =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.
  LOOP AT i_sheets INTO wa_sheets.
    CALL METHOD iref_spreadsheet->select_sheet
       EXPORTING
          name     = wa_sheets-sheet_name
*         no_flush = ' '
      IMPORTING
          error    = iref_error
*         retcode  =
            .
    IF iref_error->has_failed = 'X'.
      EXIT.
*      call method iref_error->raise_message
*        exporting
*          type = 'E'.
    ENDIF.

Re: Read multiple excel sheets using dataset...

Former Member — Tue, 22 Sep 2009 10:25:59 GMT

Continue.....


CALL METHOD iref_spreadsheet->set_selection
      EXPORTING
        top     = 1
        left    = 1
        rows    = p_rows
        columns = p_cols.

    CALL METHOD iref_spreadsheet->insert_range
      EXPORTING
        name     = 'Test'
        rows     = p_rows
        columns  = p_cols
        no_flush = ''
      IMPORTING
        error    = iref_error.
    IF iref_error->has_failed = 'X'.
      EXIT.
*      call method iref_error->raise_message
*        exporting
*          type = 'E'.
    ENDIF.

    REFRESH i_data.

    CALL METHOD iref_spreadsheet->get_ranges_data
       EXPORTING
*        no_flush  = ' '
         all       = 'X'
*        updating  = -1
*        rangesdef =
       IMPORTING
         contents  = i_data
         error     = iref_error
*        retcode   =
       CHANGING
         ranges    = i_ranges
             .
    DELETE i_data WHERE value IS INITIAL OR value = space.
    ULINE.
    WRITE:/1 wa_sheets-sheet_name COLOR 3.
    ULINE.

    LOOP AT i_data INTO wa_data.
      WRITE:(50) wa_data-value.
      AT END OF row.
        NEW-LINE.
      ENDAT.
    ENDLOOP.
  ENDLOOP.

  CALL METHOD iref_document->close_document
*  EXPORTING
*    do_save     = ' '
*    no_flush    = ' '
     IMPORTING
       error       = iref_error
*    has_changed =
*    retcode     =
      .

Re: Read multiple excel sheets using dataset...

Former Member — Tue, 22 Sep 2009 10:27:23 GMT

Continue.......


IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.
  CALL METHOD iref_document->release_document
*  EXPORTING
*    no_flush = ' '
     IMPORTING
       error    = iref_error
*    retcode  =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  SUB_FILE_F4
*&---------------------------------------------------------------------*
*       F4 help for file path
*----------------------------------------------------------------------*
FORM sub_file_f4 .
  DATA:
  l_desktop       TYPE string,
  l_i_files       TYPE filetable,
  l_wa_files      TYPE file_table,
  l_rcode         TYPE int4.

* Finding desktop
  CALL METHOD cl_gui_frontend_services=>get_desktop_directory
    CHANGING
      desktop_directory    = l_desktop
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    MESSAGE e001(00) WITH
        'Desktop not found'.
  ENDIF.

* Update View
  CALL METHOD cl_gui_cfw=>update_view
    EXCEPTIONS
      cntl_system_error = 1
      cntl_error        = 2
      OTHERS            = 3.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
     EXPORTING
       window_title            = 'Select Excel file'
       default_extension       = '.xls'
*      default_filename        =
       file_filter             = '.xls'
*      with_encoding           =
       initial_directory       = l_desktop
*      multiselection          =
    CHANGING
      file_table              = l_i_files
      rc                      = l_rcode
*     user_action             =
*     file_encoding           =
    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 e001(00) WITH 'Error while opening file'.
  ENDIF.

  READ TABLE l_i_files INDEX 1 INTO l_wa_files.
  IF sy-subrc = 0.
    p_file = l_wa_files-filename.
  ELSE.
    MESSAGE e001(00) WITH 'Error while opening file'.
  ENDIF.