Excel file upload issue

[Former Member]

Hi,

My objective is for upload of data through excel into SAP by using a simple ABAP report. But in case of any format issue in any of the record of input, the next records are not getting processed.

For e.g. if I have 100 records in excel and 40th record is having some format issue, then only 39 records are being converted to internal table and remaining are not being processed.

Can anyone tell me that how to skip that record with format error and process next records after that since I have already used ALSM_EXCEL_TO_INTERNAL_TABLE, TEXT_CONVERT_XLS_TO_SAP

[raymond_giuseppi]

Either use standard tool (like mentionned FM) or OLE2 or a tool such as Abap2XLS (*)

• But you could first map data to a table without format (text fields) and then map yourself those field values to the final internal table (with TRY/CATCH/ENDTRY) in some loop on records and then on subfields.

(*) abap2xlsx – Generate your professional Excel spreadsheet from ABAP, abap2xlsx by ivanfemia

[maheshpalavalli]

HI Swaraj Kumar Mishra,

Can you check the below code where it converts Excel to Internal table using class based approach. Maybe in this those bugs might not come.

*Read the Excel Data

    cl_gui_frontend_services=>gui_upload(

      EXPORTING

        filename                = iv_file_name    " Name of file

        filetype                = 'BIN'

      IMPORTING

        filelength              = DATA(lv_length)

      CHANGING

        data_tab                = lt_data    " Transfer table for file contents

      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 INTO DATA(lv_dummy).

* Add Message

      append_message(

        CHANGING

          ct_messages = et_messages    " Return parameter table

      ).

      RETURN.

    ENDIF.

* Show provgress indicator

    cl_progress_indicator=>progress_indicate(

      EXPORTING

        i_text               = 'Loading the excel data....'

        i_processed          = 50

        i_total              = 100

        i_output_immediately = abap_true ).



* Convert it to xstring

    CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'

      EXPORTING

        input_length = lv_length

      IMPORTING

        buffer       = lv_content

      TABLES

        binary_tab   = lt_data

      EXCEPTIONS

        failed       = 1

        OTHERS       = 2.

    IF sy-subrc NE 0.

* Add Message

      append_message(

        CHANGING

          ct_messages = et_messages    " Return parameter table

      ).

      RETURN.

    ENDIF.



* User Excel functions to read the worksheet

    TRY.

        DATA(lr_excel) = NEW cl_fdt_xl_spreadsheet( document_name = iv_file_name xdocument = lv_content ).

        IF lr_excel IS BOUND.

          lr_excel->if_fdt_doc_spreadsheet~get_worksheet_names( IMPORTING worksheet_names = DATA(lt_worksheets) ).

        ENDIF.

      CATCH cx_root.

* Add Message

        MESSAGE ID 'ZMSG_EGA_CA' TYPE 'E' NUMBER 008 WITH 'Error while reading the Excel' INTO lv_dummy.

        append_message(

          CHANGING

            ct_messages = et_messages    " Return parameter table

        ).

        RETURN.

    ENDTRY.



    mt_excel_read_data = ct_excel_read_data.

    LOOP AT lt_worksheets REFERENCE INTO DATA(lo_worksheets).



      CHECK line_exists( mt_excel_read_data[ name = lo_worksheets->* ] ).



      DATA(lr_worksheet_itab) = lr_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lo_worksheets->* ).

      ASSIGN lr_worksheet_itab->* TO <ft_excel_data>.



      DATA(lo_data) = mt_excel_read_data[ name = lo_worksheets->* ]-data.

      ASSIGN lo_data->* TO <ft_conv_data>.



      IF <ft_excel_data> IS ASSIGNED AND <ft_conv_data> IS ASSIGNED.

        LOOP AT <ft_excel_data> ASSIGNING <fs_excel_data>.

          IF sy-tabix = 1. " First line is header

            CONTINUE.

          ENDIF.

*   Initialize flag
" Your output table below
          APPEND INITIAL LINE TO <ft_conv_data> ASSIGNING <fs_conv_data>.

          DATA(lv_flag) = abap_true.

          WHILE lv_flag = abap_true.

            DATA(lv_index) = sy-index.

*     Read columnwise entries

            ASSIGN COMPONENT lv_index OF STRUCTURE <fs_excel_data> TO <fs_comp_excel>.

            IF <fs_comp_excel> IS NOT ASSIGNED.

              lv_flag = abap_false.

*       Exit the loop when a row ends

              EXIT.

            ELSE.

              ASSIGN COMPONENT lv_index OF STRUCTURE <fs_conv_data> TO <fs_comp_target>.

              <fs_comp_target> = <fs_comp_excel>.

            ENDIF.

*     Unassign field symbol

            UNASSIGN <fs_comp_excel>.

          ENDWHILE.

        ENDLOOP.

      ELSE.

        "Record error

      ENDIF.

    ENDLOOP.

BR,

Mahesh