Importing Excel to internal table with same layout (ABAP=

I know of function module ALSM_EXCEL_TO_INTERNAL_TABLE. This FM creates an internal table with three columns (row, column, value). But I want to create an internal table which has the same layout as my Excel sheet. How can I achieve this?

2 answers

  • answered 2017-11-13 07:48 Jozsef Szikszai

    If you upload the data with FM ALSM_EXCEL_TO_INTERNAL_TABLE, you can LOOP through the internal table this FM is uses (the one with row, column, value as you mentioned) and fill your own internal table (which looks like the Excel sheet) accordingly.

  • answered 2017-11-13 19:20 user1498318

    You can use class cl_mass_spreadsheet_service if you are uploading the excel in foreground, and cl_gui_frontend_services=>file_open_dialog to navigate to the file. See my example code below.

            METHOD read_document.
    
                DATA:
                    lv_file      TYPE if_mass_spreadsheet_types=>file_name,
                    lt_result    TYPE STANDARD TABLE OF zsd_salesorder_create. "your result table
    
                lv_file = iv_file.
    
                cl_mass_spreadsheet_service=>fill_table(
                    EXPORTING
                        iv_file                     = lv_file   "full path+name of file. See method navigate_to_file below
                        iv_from_file                = abap_true "use to upload from excel/CSV
                        iv_from_clipboard           = abap_false "use to copy directly from clipbiard
                        iv_tabname                  = 'Order_Create' "can be whatever
                    CHANGING
                        ct_table                    = lt_result "if ct_table have the same column names as the excel file, the order of the columns does not matter
                ).
    
            ENDMETHOD.
    
    
            METHOD navigate_to_file.
    
                DATA:
                    lt_files TYPE filetable,
                    lv_rcode TYPE int4,
                    lv_action TYPE int4.
    
        "Call dialog to navigate to file
                CALL METHOD cl_gui_frontend_services=>file_open_dialog
                    EXPORTING
                        default_extension       = '.xls'
                        file_filter             = '*.xls'
                        initial_directory       = 'C:\'
                    CHANGING
                        file_table              = lt_files
                        rc                      = lv_rcode
                        user_action             = lv_action
                    EXCEPTIONS
                        file_open_dialog_failed = 1
                        cntl_error              = 2
                        error_no_gui            = 3
                        OTHERS                  = 4.
                IF sy-subrc <> 0.
                    MESSAGE e208(00) WITH 'FILE_OPEN_DIALOG'.
                ENDIF.
    
        "Only continue if user hasn't cancelled
                CHECK: lv_action = 0.
    
        "Determine file to open
                READ TABLE lt_files INDEX 1  ASSIGNING FIELD-SYMBOL(<file>).
                IF sy-subrc = 0.
                    cv_file = <file>-filename.
                ELSE.
                    MESSAGE e208(00).
                ENDIF.
    
            ENDMETHOD.