Upload an Excel file

[Former Member]

Hi guys,

I am doing a BDC and i got an excel file that i need to upload into an internal table before i process it. I am defining an internal table as text and using GUI_UPLOAD with delimiter as '|' but i guess its not working. Can i try something else here??.

Thanks in advance,

David.

[rahulkavuri]

sorry wrong code posted earlier

are u talking about the field seperator

  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      FILENAME                = V_FILE
      FILETYPE                = 'ASC'
      HAS_FIELD_SEPARATOR     = 'X'
    TABLES
      DATA_TAB                = IT_SALES
    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.

To upload excel use this code

data str(1000).
data itab like table of str with  header line.
 
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                      = 'C:sample1.xls'
    i_begin_col                   = 1
    i_begin_row                   = 1
    i_end_col                     = 2
    i_end_row                     = 100
  tables
    intern                        =  it_excel
.
 
  LOOP AT it_excel.
    AT NEW row.
      CLEAR itab.
    ENDAT.
    IF itab IS INITIAL.
      MOVE it_excel-value TO itab.
    ELSE.
      CONCATENATE itab ',' it_excel-value INTO itab.
    ENDIF.
 
    AT END OF row.
      APPEND itab.
    ENDAT.
  ENDLOOP.

[Former Member]

Hi,

you can use this FM <b>ALSM_EXCEL_TO_INTERNAL_TABLE</b>instead of gui_upload. Also I am providing the code provided to Vijay by Rich . This should solve your poblem . And I thank Rich here for providing such a good solution .

report zrich_0002.

types: begin of ttab ,

fld1(30) type c,

fld2(30) type c,

fld3(30) type c,

fld4(30) type c,

fld5(30) type c,

end of ttab.

data: itab type table of ttab with header line.

selection-screen skip 1.

parameters: p_file type localfile default

'C:\test.txt'.

selection-screen skip 1.

at selection-screen on value-request for p_file.

call function 'KD_GET_FILENAME_ON_F4'

exporting

static = 'X'

changing

file_name = p_file.

start-of-selection.

clear itab. refresh itab.

perform upload_data.

loop at itab.

write:/ itab-fld1, itab-fld2, itab-fld3, itab-fld4, itab-fld5.

endloop.

************************************************************************

• Upload_Data

************************************************************************

form upload_data.

data: file type rlgrap-filename.

data: xcel type table of alsmex_tabline with header line.

file = p_file.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = file

i_begin_col = '1'

i_begin_row = '1'

i_end_col = '200'

i_end_row = '5000'

tables

intern = xcel

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

loop at xcel.

case xcel-col.

when '0001'.

itab-fld1 = xcel-value.

when '0002'.

itab-fld2 = xcel-value.

when '0003'.

itab-fld3 = xcel-value.

when '0004'.

itab-fld4 = xcel-value.

when '0005'.

itab-fld5 = xcel-value.

endcase.

at end of row.

append itab.

clear itab.

endat.

endloop.

endform.

[RichHeilman]

Varun, thanks for the plug. Here is an example of doing the same but with a dynamic internal table.

report zrich_0002.

type-pools: slis.

field-symbols: <dyn_table> type standard table,
               <dyn_wa>,
               <dyn_field>.

data: it_fldcat type lvc_t_fcat,
      wa_it_fldcat type lvc_s_fcat.

type-pools : abap.

data: new_table type ref to data,
      new_line  type ref to data.

data: xcel type table of alsmex_tabline with header line.

selection-screen begin of block b1 with frame title text .
parameters: p_file type  rlgrap-filename default 'c:Test.csv'.
parameters: p_flds type i.
selection-screen end of block b1.

start-of-selection.

* Add X number of fields to the dynamic itab cataelog
  do p_flds times.
    clear wa_it_fldcat.
    wa_it_fldcat-fieldname = sy-index.
    wa_it_fldcat-datatype = 'C'.
    wa_it_fldcat-inttype = 'C'.
    wa_it_fldcat-intlen = 10.
    append wa_it_fldcat to it_fldcat .
  enddo.
  .
* Create dynamic internal table and assign to FS
  call method cl_alv_table_create=>create_dynamic_table
               exporting
                  it_fieldcatalog = it_fldcat
               importing
                  ep_table        = new_table.

  assign new_table->* to <dyn_table>.

* Create dynamic work area and assign to FS
  create data new_line like line of <dyn_table>.
  assign new_line->* to <dyn_wa>.

* Upload the excel
  call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       exporting
            filename                = p_file
            i_begin_col             = '1'
            i_begin_row             = '1'
            i_end_col               = '200'
            i_end_row               = '5000'
       tables
            intern                  = xcel
       exceptions
            inconsistent_parameters = 1
            upload_ole              = 2
            others                  = 3.

* Reformt to dynamic internal table
  loop at xcel.
    assign component xcel-col of structure <dyn_wa> to <dyn_field>.
    if sy-subrc = 0.
     <dyn_field> = xcel-value.
    endif.

    at end of row.
      append <dyn_wa> to <dyn_table>.
      clear <dyn_wa>.
    endat.
  endloop.

* Write out data from table.
  loop at <dyn_table> into <dyn_wa>.
    do.
      assign component  sy-index  of structure <dyn_wa> to <dyn_field>.
      if sy-subrc <> 0.
        exit.
      endif.
      if sy-index = 1.
        write:/ <dyn_field>.
      else.
        write: <dyn_field>.
      endif.
    enddo.
  endloop.

Regards,

Rich Heilman

[Former Member]

HI,

Simple way is to save the excel into TAB DELIMITED file. Use GUI_UPLOAD function module to upload records into internal table containing the fields (You can directly import the records into the required internal table. Internal table as text can be avoided here).

Regards,

Vara

[Former Member]

HI,

Simple way is to save the excel into TAB DELIMITED file. Use GUI_UPLOAD function module to upload records into internal table containing the fields (You can directly import the records into the required internal table. Internal table as text can be avoided here).

In the GUI_UPLOAD Function module

pass HAS_FIELD_SEPARATOR = 'X'.

Regards,

Vara

[Former Member]

Hi

I believe the best solution is to save the excel file in CSV format, so you should only manage a text file (it's easier).

The CSV file use ; as separator so you don't care for the field length.

You can use fm TEXT_CONVERT_CSV_TO_SAP to store the data in your internal table:

- Step 1: Upload the file GUI_UPLOAD;

- Step 2: convert the file TEXT_CONVERT_CSV_TO_SAP

- Step 3: process the file

Max

[Former Member]

Try this code.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

PERFORM upload_help.

************************************************************************

*Start of selection

************************************************************************

START-OF-SELECTION.

PERFORM upload_data.

&----

---

*& Form upload_help

&----

---

• text

----

---

FORM upload_help .

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

def_filename = p_file

• DEF_PATH = DEF_PATH

mask = ',.,..'

• MASK = TMP_MASK

mode = 'O'

IMPORTING

filename = p_file

EXCEPTIONS

inv_winsys = 01

no_batch = 02

selection_cancel = 03

selection_error = 04.

IF sy-subrc = 0.

ENDIF.

ENDFORM. " upload_help

&----

---

*& Form upload_data

&----

---

• text

----

---

FORM upload_data .

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = it_tab[] "ACTUAL DATA

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.

APPEND IT_TAB.

CLEAR IT_TAB.

• describe table it_tab lines v_lines.

LOOP AT it_tab.

• FROM 2 TO v_lines .

move sy-mandt TO IT_TAB1-mandt.

move it_tab-leg_bukrs to IT_TAB1-leg_bukrs.

move it_tab-leg_sysid to IT_TAB1-leg_sysid.

move it_tab-leg_saknr to IT_TAB1-leg_saknr .

move it_tab-db_cr to IT_TAB1-db_cr.

move it_tab-con_bukrs to IT_TAB1-con_bukrs.

move it_tab-CON_SYSID to IT_TAB1-CON_SYSID.

move it_tab-con_c_saknr to IT_TAB1-con_c_saknr.

move it_tab-con_c_summ to IT_TAB1-con_c_summ.

move it_tab-con_d_saknr to IT_TAB1-con_d_saknr.

move it_tab-con_d_summ to IT_TAB1-con_d_summ.

move it_tab-exp_bukrs to IT_TAB1-exp_bukrs.

move it_tab-exp_sysid to IT_TAB1-exp_sysid .

move it_tab-exp_c_saknr to IT_TAB1-exp_c_saknr.

move it_tab-exp_c_summ to IT_TAB1-exp_c_summ.

move it_tab-exp_d_saknr to IT_TAB1-exp_d_saknr.

move it_tab-exp_d_summ to IT_TAB1-exp_d_summ .

APPEND IT_TAB1.

CLEAR IT_TAB1.

ENDLOOP.

REFRESH IT_TAB.

LOOP AT IT_TAB1.

IF IT_TAB1-LEG_BUKRS <> SPACE.

MODIFY ZFDLC_ALT_ACCT FROM IT_TAB1.

ENDIF.

ENDLOOP.

FREE IT_TAB1.

FORMAT COLOR COL_POSITIVE.

WRITE: / 'NO.OF.RECORDS LOADED: ' , SY-TABIX.

ENDFORM. " upload_dataendloop.