Skip to Content
avatar image
Former Member

how to upload two sheets data into internal table

I have a requirement to upload multiple sheet data like header data in sheet 1 and item data in sheet 2 as shown below.

sheet 1

Header 1 Header 2 Header 3 Header 4

h1

h2

val..

val

val..

val

val..

val

sheet 2

item1 item1 item1 item1

i1

i1

i2

i2

val

val

val

val

val

val

val

val

val

val

val

val

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Jan 11, 2015 at 08:20 AM

    That's your requirement, but what is your question?

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 11, 2015 at 12:23 PM

    Hi Praveen,

      We can upload multiples sheets in the same excel using class I_OI_SPREADSHEET.

    Please find the below link.

    http://wiki.scn.sap.com/wiki/display/Snippets/Read+multiple+sheets+of+an+Excel+file+into+SAP+through+ABAP

    Regards

    Rajkumar Narasimman

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 11, 2015 at 08:18 AM

    Hi Praveen,

    You will have to upload the files separately only, take the data into 2 different internal tables.

    Then you can combine the data into single internal table based on header.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 11, 2015 at 11:55 AM

    You can create two Internal Tables with the same structure,

    upload to fill each internal table

    then loop at one of them and append it's work area fields to the other internal table

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 13, 2015 at 04:51 PM

    Hi I see, same as other people mentioned....

    Create 2 internal tables use FieldSymbols, and fill, use separated GUI_UPLOAD or WS_UPLOAD (Obsolete)

    My dude is:

    in you sample data where are the relation (HEADER<->ITEM)?

    thanks

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 14, 2015 at 12:05 AM

    Hi test the code:

    the sample work with lfa1 Vendor table and REGUH Payments table.

    1) convert your excel data into csv files

    2) run the program this break in the breakpoint

    3) check if internal tables have the data of csv files

    Note: i used WS_UPLOAD depreseated function because GUI_UPLOAD generate a dump:

    Short Text Type conflict when calling a function module.

       74 FORM LOAD_FILE1  TABLES   P_IT_LFA1

       75                  USING    P_P_ARCH1.

    >>>>> CALL FUNCTION 'GUI_UPLOAD'

       77   EXPORTING

       78     FILENAME                      = P_P_ARCH1

    4) in the second SplitTable the FM:   TEXT_CONVERT_TEX_TO_SAP generate a dump i think because in this structure i use a decimal(packed) field. i do manually the SPLIT.

    5) change the path and filenames for work (i added the filenames as attachemtns).

    *&---------------------------------------------------------------------*

    *& Report  Z_2T_FS

    *&

    *&---------------------------------------------------------------------*

    *&

    *&

    *&---------------------------------------------------------------------*

    REPORT  Z_2T_FS.

    TYPE-POOLS:  truxs.

    tables: lfa1, reguh.

    *Structure for get in 1 BIG field the first file.

    types: begin of t_lfa1_0,   "Master Vendor

              largeField(100) type c,

            end of t_lfa1_0.

    *Structure for get in 1 BIG field the second file.

    types: begin of t_reguh_0,   "Detail Vendor

              largeField(100) type c,

            end of t_reguh_0.

    types: begin of t_lfa1,   "Master Vendor

              MANDT like LFA1-MANDT,

              LIFNR like LFA1-LIFNR,

              NAME1 like LFA1-NAME1,

            end of t_lfa1.

    types: begin of t_reguh,   "Detail Vendor

              MANDT like REGUH-MANDT,

              LIFNR like REGUH-LIFNR,

              RBETR like REGUH-RBETR,

            end of t_reguh.

    data: it_lfa10 type TABLE OF t_lfa1_0 with HEADER LINE.

    data: it_reguh0 type TABLE OF t_reguh_0 with HEADER LINE.

    data: it_lfa1 type TABLE OF t_lfa1 with HEADER LINE.

    data: it_reguh type TABLE OF t_reguh with HEADER LINE.

    data: go_table1 TYPE REF TO cl_salv_table, columns1 type REF TO cl_salv_columns_table, column1 type REF TO cl_salv_column_table.

    data: go_table2 TYPE REF TO cl_salv_table, columns2 type REF TO cl_salv_columns_table, column2 type REF TO cl_salv_column_table.

    data: lv_msg(100) type c.

    SELECTION-SCREEN BEGIN OF BLOCK Screen_capt WITH FRAME TITLE Text-001.

    *Parameters: p_lifnr LIKE lfa1-LIFNR Obligatory DEFAULT '0002000007'.

    Parameters: p_arch1 LIKE RLGRAP-FILENAME Obligatory DEFAULT 'c:\temp\raw2\MASTER.csv'.

    Parameters: p_arch2 LIKE RLGRAP-FILENAME Obligatory DEFAULT 'c:\temp\raw2\detail.csv'.

    SELECTION-SCREEN END OF BLOCK Screen_Capt.

    *select MANDT LIFNR NAME1 FROM LFA1 INTO CORRESPONDING FIELDS OF TABLE it_lfa1 where LIFNR = P_LIFNR.

    CALL FUNCTION 'F4_FILENAME'

      EXPORTING

        PROGRAM_NAME        = SY-REPID

        DYNPRO_NUMBER       = SY-DYNNR

        FIELD_NAME          = 'P_ARCH1'

      IMPORTING

        FILE_NAME           = P_ARCH1.

    CALL FUNCTION 'F4_FILENAME'

      EXPORTING

        PROGRAM_NAME        = SY-REPID

        DYNPRO_NUMBER       = SY-DYNNR

        FIELD_NAME          = 'P_ARCH2'

      IMPORTING

        FILE_NAME           = P_ARCH2.

    clear lv_msg.

    IF SY-SUBRC NE 0.

       lv_msg =  'No hay entradas'.

    *  concatenate 'No hay entradas con LIFNR = ' p_lifnr into lv_msg.

    *    MESSAGE E398(00) with lv_msg.

       MESSAGE lv_msg type 'E'.

    ENDIF.

    *Load Files int internal tables

    perform load_file1 tables it_lfa10 using p_arch1.

    perform load_file2 tables it_reguh0 using p_arch2.

    *BREAK-POINT.

    perform Split_tablelfa1 TABLES it_lfa1  it_lfa10.

    perform Split_tablereguh TABLES it_reguh  it_reguh0.

    BREAK-POINT.

    *&---------------------------------------------------------------------*

    *&      Form  LOAD_FILE1

    *&---------------------------------------------------------------------*

    *       text

    *----------------------------------------------------------------------*

    *      -->P_IT_LFA1  text

    *      -->P_P_ARCH1  text

    *----------------------------------------------------------------------*

    FORM LOAD_FILE1  TABLES   P_IT_LFA1

                      USING    P_P_ARCH1.

    data:  lv_no_records type i.

    CALL FUNCTION 'WS_UPLOAD'

       EXPORTING

         FILENAME                      = P_P_ARCH1

        FILETYPE                      = 'ASC'

    *   HAS_FIELD_SEPARATOR           = ' '

    *   HEADER_LENGTH                 = 0

    *   READ_BY_LINE                  = 'X'

    *   DAT_MODE                      = ' '

    *   CODEPAGE                      = ' '

    *   IGNORE_CERR                   = ABAP_TRUE

    *   REPLACEMENT                   = '#'

    *   CHECK_BOM                     = ' '

    *   VIRUS_SCAN_PROFILE            =

    *   NO_AUTH_CHECK                 = ' '

    * IMPORTING

    *   FILELENGTH                    =

    *   HEADER                        =

       TABLES

         DATA_TAB                      = P_IT_LFA1

      EXCEPTIONS

        FILE_OPEN_ERROR               = 1

        FILE_READ_ERROR               = 2

        NO_BATCH                      = 3

        GUI_REFUSE_FILETRANSFER       = 4

        INVALID_TYPE                  = 5

        NO_AUTHORITY                  = 6.

    IF SY-SUBRC <> 0.

       MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

               WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

    ENDIF.

    describe TABLE p_it_lfa1 lines lv_no_records.

    write:/ '1)No Records Master: ', lv_no_records.

    *BREAK-POINT.

    ENDFORM.                    " LOAD_FILE1

    FORM LOAD_FILE2  TABLES   P_it_reguh

                      USING    P_P_ARCH2.

    data:  lv_no_records type i.

    CALL FUNCTION 'WS_UPLOAD'

       EXPORTING

         FILENAME                      = P_P_ARCH2

        FILETYPE                      = 'ASC'

    *   HAS_FIELD_SEPARATOR           = ' '

    *   HEADER_LENGTH                 = 0

    *   READ_BY_LINE                  = 'X'

    *   DAT_MODE                      = ' '

    *   CODEPAGE                      = ' '

    *   IGNORE_CERR                   = ABAP_TRUE

    *   REPLACEMENT                   = '#'

    *   CHECK_BOM                     = ' '

    *   VIRUS_SCAN_PROFILE            =

    *   NO_AUTH_CHECK                 = ' '

    * IMPORTING

    *   FILELENGTH                    =

    *   HEADER                        =

       TABLES

         DATA_TAB                      = P_it_reguh

      EXCEPTIONS

        FILE_OPEN_ERROR               = 1

        FILE_READ_ERROR               = 2

        NO_BATCH                      = 3

        GUI_REFUSE_FILETRANSFER       = 4

        INVALID_TYPE                  = 5

        NO_AUTHORITY                  = 6.

    IF SY-SUBRC <> 0.

    * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

    *         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

    ENDIF.

    describe TABLE P_it_reguh lines lv_no_records.

    write:/ '1) No Records Detail: ', lv_no_records.

    ENDFORM.                    " LOAD_FILE2

    *&---------------------------------------------------------------------*

    *&      Form  SPLIT_TABLELFA1

    *&---------------------------------------------------------------------*

    *       text

    *----------------------------------------------------------------------*

    *      -->P_IT_LFA1  text

    *      -->P_IT_LFA10  text

    *      -->P_BREAK_POINT  text

    *----------------------------------------------------------------------*

    FORM SPLIT_TABLELFA1  TABLES  P_IT_LFA1 P_IT_LFA10.

       data: gt_raw_data_lfa1 type truxs_t_text_data.

       data:  lv_no_records type i.

       gt_raw_data_lfa1[] = P_IT_LFA10[].

    *  BREAK-POINT.

       CALL FUNCTION 'TEXT_CONVERT_TEX_TO_SAP'

         EXPORTING

    *     I_FIELD_SEPERATOR          =

    *     I_LINE_HEADER              =

           I_TAB_RAW_DATA             = gt_raw_data_lfa1[]

    *     I_FILENAME                 =

         TABLES

           I_TAB_CONVERTED_DATA       = it_lfa1[]

        EXCEPTIONS

          CONVERSION_FAILED          = 1

          OTHERS                     = 2.

       IF SY-SUBRC <> 0.

           MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

          WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

       ENDIF.

       describe TABLE it_lfa1 lines lv_no_records.

       write:/ '2) No Records Master: ', lv_no_records.

    ENDFORM.                    " SPLIT_TABLELFA1

    *&---------------------------------------------------------------------*

    *&      Form  SPLIT_TABLEREGUH

    *&---------------------------------------------------------------------*

    *       text

    *----------------------------------------------------------------------*

    *      -->P_IT_REGUH  text

    *      -->P_IT_REGUH0  text

    *----------------------------------------------------------------------*

    FORM SPLIT_TABLEREGUH  TABLES   P_IT_REGUH P_IT_REGUH0.

       data: lv_no_records type i.

       data: lv_a(20) type c, lv_b(20) type c, lv_c(20) type c.

       FIELD-SYMBOLS: <fs_xtable> type standard table.

       FIELD-SYMBOLS: <fs_wa> like line of IT_REGUH0.

       break-point.

       assign it_reguh0[] to <fs_xtable>.

       clear it_reguh.

       LOOP at <fs_xtable> assigning <fs_wa>.

         split <fs_wa> at ';' into lv_a lv_b lv_c.

         it_reguh-MANDT = lv_a.

         it_reguh-LIFNR = lv_b.

         it_reguh-RBETR = lv_c.

         append it_reguh.

       endloop.

       describe TABLE it_reguh lines lv_no_records.

       write:/ '2) No Records Detail: ', lv_no_records.

    ENDFORM.                    " SPLIT_TABLELFA1

    any other thing?

    please comment the code.

    thanks


    detail.csv.zip (243 B)
    MASTER.CSV.zip (196 B)
    Add comment
    10|10000 characters needed characters exceeded