topic how to upload two sheets data into internal table in Application Development and Automation Discussions

how to upload two sheets data into internal table

Former Member — Sun, 11 Jan 2015 08:11:52 GMT

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

Header 1

Header 2

Header 3

Header 4

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

item1

val

Re: how to upload two sheets data into internal table

archanapawar — Sun, 11 Jan 2015 08:18:58 GMT

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.

Re: how to upload two sheets data into internal table

paul_bakker2 — Sun, 11 Jan 2015 08:20:05 GMT

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

Re: how to upload two sheets data into internal table

Former Member — Sun, 11 Jan 2015 08:36:09 GMT

my requirement is to upload the fb01 t code to upload the G/L's and customer and vendor details

i tried bapi_acc_document_post bust some fileds is missing in my requirement so through bdc

i need to uplode the fields like ..

=========header ===============

bldat

budat

blart

bukrs

waers

xblnr

bktxt

========item================

newbs

NEWKO

newum

wrbtr

MWSKZ

zfbdt

prctr

newbs1

NEWKO1

wrbtr1

prctr1

valut

kostl

========================

Re: how to upload two sheets data into internal table

former_member182888 — Sun, 11 Jan 2015 11:55:00 GMT

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

Re: how to upload two sheets data into internal table

rajkumarnarasimman — Sun, 11 Jan 2015 12:23:06 GMT

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

Re: how to upload two sheets data into internal table

paul_bakker2 — Sun, 11 Jan 2015 22:41:21 GMT

Hi,

BAPI_ACC_DOCUMENT_POST should be able to handle those fields.

What exactly is the problem? The GL documents are being created but those fields are empty?

cheers

Paul

Re: how to upload two sheets data into internal table

Former Member — Tue, 13 Jan 2015 16:51:19 GMT

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

Re: how to upload two sheets data into internal table

Former Member — Wed, 14 Jan 2015 00:05:55 GMT

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