INTERVIEW QUESTION

[Former Member]

1. how can we write BDC program to upload data from CSV, XL, TAB delimeter type flat files?

2. In BDC if the flat file consist of header and multiple line items then how to upload the load, does we create a single internal table for both header and body or different internal tables?

[Former Member]

Hi Vamsi,

1. In BDC, to upload CSV we need to use FM "GUI_UPLOAD" and for XL we can use: 'KCD_EXCEL_OLE_TO_INT_CONVERT' or 'ALSM_EXCEL_TO_INTERNAL_TABLE'

2. If the file consists of header and items, we need to upload the data into one internal table and from that internal table, we need to pass the data into header and item internal tables.

Check:

FORM upload_csv_file.

CLEAR g_file.

g_file = p_file.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = g_file

filetype = 'ASC'

has_field_separator = c_x

• HEADER_LENGTH = 0

• READ_BY_LINE = 'X'

• DAT_MODE = ' '

• CODEPAGE = ' '

• IGNORE_CERR = ABAP_TRUE

• REPLACEMENT = '#'

• CHECK_BOM = ' '

• VIRUS_SCAN_PROFILE =

• IMPORTING

• FILELENGTH =

• HEADER =

TABLES

data_tab = gt_dummy

• 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

• OTHERS = 17

.

IF sy-subrc <> 0.

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

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

ENDIF.

perform header.

perform item.

FORM header .

DATA: str1 TYPE string,

str2 TYPE string,

gt_itab TYPE TABLE OF string,

wa(10) TYPE c.

READ TABLE gt_dummy INDEX 1.

CLEAR:wa.

SPLIT gt_dummy AT ',' INTO: str1 str2, TABLE gt_itab.

READ TABLE gt_itab INTO wa INDEX 2.

IF sy-subrc EQ 0.

gs_header-lifnr = wa.

ENDIF.

APPEND gs_header TO gt_header.

CLEAR: gs_header,gt_itab[].

ENDFORM. " header

regards

Kannaiah

[rahulkavuri]

1. This is a sample program where I have used the option of Excel and Tab delimited file upload in a radio button, u can run the program and check

report ytest_file
       no standard page heading line-size 255
       message-id zz .

************************************************************************
*                             Variables                                *
************************************************************************

data: it_bdcdata like bdcdata occurs 0 with header line,
      it_msgs like bdcmsgcoll occurs 0 with header line.

data: v_file type string,
      flag,
      v_msg(200) type c.

************************************************************************
*                         Internal Tables                              *
************************************************************************

data: begin of itab occurs 0,
        saknr(010),
        bukrs(004),
        waers(005),
        xkres(001),
        zuawa(003),
        fstag(004),
      end of itab.

************************************************************************
*                       Selection-Screen                               *
************************************************************************

selection-screen begin of block b1 with frame title text-001.

selection-screen begin of line.

selection-screen position 1.
selection-screen comment 1(13) text-r01 for field excel.
parameters: excel radiobutton group g1.

selection-screen position 18.
selection-screen comment 18(15) text-r02 for field tabfile.
parameters: tabfile radiobutton group g1.

selection-screen end of line.

parameters: file type rlgrap-filename obligatory.

selection-screen end of block b1.

************************************************************************
*                    At Selection-Screen                               *
************************************************************************

at selection-screen.

  data: file_ext(3), len type int2.

  len      = strlen( file ).
  len      = len - 3.
  file_ext = file+len(3).


  if excel = 'X'.

    if file_ext <> 'xls' and file_ext <> 'XLS'
                         and file_ext <> 'csv'
                         and file_ext <> 'CSV'.

      message e000 with 'This is not a valid Excel Sheet'.

    endif.

  endif.

  if tabfile = 'X'.

    if file_ext <> 'txt' and file_ext <> 'TXT'.

      message e000 with 'This is not a text file, please check'.

    endif.

  endif.


************************************************************************
*                       At Selection-Screen on Value request
************************************************************************

at selection-screen on value-request for file.

  call function 'F4_FILENAME'
    exporting
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
      field_name    = 'file'
    importing
      file_name     = file.

************************************************************************
*                       Start of Selection                             *
************************************************************************

start-of-selection.


*upload the excel file into the internal table
  if excel = 'X'.
    perform upload_excel.
  else.
    perform upload_textfile.
  endif.

  loop at itab.

    perform populate_bdc.

    call transaction 'FSS0' using it_bdcdata
                                  mode 'A'
                                  update 'A'
                                  messages into it_msgs.

    if not it_msgs[] is initial.

      loop at it_msgs.

        call function 'FORMAT_MESSAGE'
          exporting
            id        = it_msgs-msgid
            lang      = 'EN'
            no        = it_msgs-msgnr
            v1        = it_msgs-msgv1
            v2        = it_msgs-msgv2
            v3        = it_msgs-msgv3
            v4        = it_msgs-msgv4
          importing
            msg       = v_msg
          exceptions
            not_found = 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.
        write:/ v_msg.
      endloop.

    endif.

  endloop.

*&---------------------------------------------------------------------*
*&      Form  UPLOAD_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form upload_excel.

  data: it_excel like alsmex_tabline occurs 0 with header line.
  types: begin of t_record,
      field1 like it_excel-value,
      field2 like it_excel-value,
      field3 like it_excel-value,
      field4 like it_excel-value,
      field5 like it_excel-value,
      field6 like it_excel-value,
      end of t_record.


  data: it_record type standard table of t_record initial size 0,
        wa_record type t_record.
  data: gd_currentrow type i.


  call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    exporting
      filename                = file
      i_begin_col             = '1'
      i_begin_row             = '2'  "Do not require headings
      i_end_col               = '6'
      i_end_row               = '60000'
    tables
      intern                  = it_excel
    exceptions
      inconsistent_parameters = 1
      upload_ole              = 2
      others                  = 3.
  if sy-subrc <> 0.
    message e010(zz) with text-001. "Problem uploading Excel Spreadsheet
  endif.

* Sort table by rows and colums
  sort it_excel by row col.

* Get first row retrieved
  read table it_excel index 1.

* Set first row retrieved to current row
  gd_currentrow = it_excel-row.

  loop at it_excel.
*   Reset values for next row
    if it_excel-row ne gd_currentrow.
*   populate the values into table itab
      itab-saknr    = wa_record-field1.
      itab-bukrs    = wa_record-field2.
      itab-waers    = wa_record-field3.
      itab-xkres    = wa_record-field4.
      itab-zuawa    = wa_record-field5.
      itab-fstag    = wa_record-field6.
      append itab.

      clear wa_record.
      gd_currentrow = it_excel-row.
    endif.

    case it_excel-col.
      when '0001'.
        wa_record-field1 = it_excel-value.
      when '0002'.
        wa_record-field2 = it_excel-value.
      when '0003'.
        wa_record-field3 = it_excel-value.
      when '0004'.
        wa_record-field4 = it_excel-value.
      when '0005'.
        wa_record-field5 = it_excel-value.
      when '0006'.
        wa_record-field6 = it_excel-value.
    endcase.

  endloop.

  itab-saknr    = wa_record-field1.
  itab-bukrs    = wa_record-field2.
  itab-waers    = wa_record-field3.
  itab-xkres    = wa_record-field4.
  itab-zuawa    = wa_record-field5.
  itab-fstag    = wa_record-field6.


  append itab.

*!! Excel data is now contained within the internal table IT_RECORD

endform.                    "UPLOAD_EXCEL

*&---------------------------------------------------------------------*
*&      Form  UPLOAD_TEXTFILE
*&---------------------------------------------------------------------*

form upload_textfile .

  data: l_file type string.

  l_file = file.

  call function 'GUI_UPLOAD'
    exporting
     filename                      = l_file
     filetype                      = 'ASC'
     has_field_separator           = 'X'
*   HEADER_LENGTH                 = 0
*   READ_BY_LINE                  = 'X'
*   DAT_MODE                      = ' '
*   CODEPAGE                      = ' '
*   IGNORE_CERR                   = ABAP_TRUE
*   REPLACEMENT                   = '#'
*   CHECK_BOM                     = ' '
*   VIRUS_SCAN_PROFILE            =
* IMPORTING
*   FILELENGTH                    =
*   HEADER                        =
    tables
      data_tab                      = itab
            .
  if sy-subrc <> 0.
    message id sy-msgid type sy-msgty number sy-msgno
            with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  endif.

*deleting the row which contains the description
  delete itab index 1.

endform.                    " UPLOAD_TEXTFILE
*&---------------------------------------------------------------------*
*&      Form  POPULATE_BDC
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form populate_bdc.

  clear it_msgs.
  refresh it_msgs.

  clear it_bdcdata.
  refresh it_bdcdata.

  perform bdc_dynpro      using 'SAPLGL_ACCOUNT_MASTER_MAINTAIN' '2001'.
  perform bdc_field       using 'BDC_OKCODE'
                                '=ACC_CRE'.
  perform bdc_field       using 'BDC_CURSOR'
                                'GLACCOUNT_SCREEN_KEY-SAKNR'.
  perform bdc_field       using 'GLACCOUNT_SCREEN_KEY-SAKNR'
                                itab-saknr.
  perform bdc_field       using 'GLACCOUNT_SCREEN_KEY-BUKRS'
                                itab-bukrs.
  perform bdc_dynpro      using 'SAPLGL_ACCOUNT_MASTER_MAINTAIN' '2001'.
  perform bdc_field       using 'BDC_OKCODE'
                                '=TAB02'.
  perform bdc_field       using 'GLACCOUNT_SCREEN_CCODE-WAERS'
                                itab-waers.
  perform bdc_field       using 'BDC_CURSOR'
                                'GLACCOUNT_SCREEN_CCODE-ZUAWA'.
  perform bdc_field       using 'GLACCOUNT_SCREEN_CCODE-XKRES'
                                itab-xkres.
  perform bdc_field       using 'GLACCOUNT_SCREEN_CCODE-ZUAWA'
                                itab-zuawa.
  perform bdc_dynpro      using 'SAPLGL_ACCOUNT_MASTER_MAINTAIN' '2001'.
  perform bdc_field       using 'BDC_OKCODE'
                                '=SAVE'.
  perform bdc_field       using 'BDC_CURSOR'
                                'GLACCOUNT_SCREEN_CCODE-FSTAG'.
  perform bdc_field       using 'GLACCOUNT_SCREEN_CCODE-FSTAG'
                                itab-fstag.


endform.                    "POPULATE_BDC
*&---------------------------------------------------------------------*
*&      Form  bdc_dynpro
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form bdc_dynpro  using    value(p_0586)
                          value(p_0587).

  clear it_bdcdata.
  it_bdcdata-program  = p_0586.
  it_bdcdata-dynpro   = p_0587.
  it_bdcdata-dynbegin = 'X'.
  append it_bdcdata.

endform.                    " bdc_dynpro
*&---------------------------------------------------------------------*
*&      Form  bdc_field
*&---------------------------------------------------------------------*

form bdc_field  using    value(p_0631)
                         fval.

  clear it_bdcdata.
  it_bdcdata-fnam = p_0631.
  it_bdcdata-fval = fval.
  append it_bdcdata.

endform.                    " bdc_field