topic put a file into a table in Application Development and Automation Discussions

put a file into a table

Former Member — Fri, 30 Dec 2005 23:40:03 GMT

hello i would like to know how can i put a file into a db table.

the file is an excel file and the first step that iam doing is to create an internal table and then i sent the file in the GUI_UPLOAD and the the internal table receice the file information , and the last step i make a loop to the internal table and i need to insert the information into the db table.

please if have some examples please sent me thanks a lot

Re: put a file into a table

RichHeilman — Fri, 30 Dec 2005 23:51:51 GMT

First, I would suggest converting your excel file to a comma delimited text file. Then you can use the following code to upload and update your custom table.



report zrich_0001.

types: begin of ttab,
       rec(1000) type c,
       end of ttab.

types: begin of tdat,
       fld1(10) type c,
       fld2(10) type c,
       fld3(10) type c,
       end of tdat.

data: itab type table of ttab with header line.
data: idat type table of tdat with header line.

data: file_str type string.

parameters: p_file type localfile.

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.

  file_str = p_file.

  call function 'GUI_UPLOAD'
    exporting
      filename                = file_str
    tables
      data_tab                = itab
    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.


  loop at itab.
    clear idat.
    split itab-rec at ',' into idat-fld1
                               idat-fld2
                               idat-fld3.
    append idat.
  endloop.

* now you have your data in specific fields of an internal table.
* you can now update your database table.

  loop at idat.
   insert zdata from idat.
  endloop.

Regards,

Rich Heilman

Re: put a file into a table

suresh_datti — Sat, 31 Dec 2005 00:46:33 GMT

Hi Gilos,

here is another option.. for this, you will have to save the excel file as a tab delimited .txt file..


report zp_testbdc1.
*&---------------------------------------------------------------------*
*& TYPES
*&---------------------------------------------------------------------*
types:
* structure for inbound file
  begin of typ_in_file,
   field1(4),
   field2(4),
   field3(4),
  end of typ_in_file.
data:
        t_file        type typ_in_file occurs 0,
        rec_file      like line of t_file.
t_filetable type filetable,
w_filetable like file_table-filename,
w_subrc     type i.
parameters:
          p_file  like rlgrap-filename.  "file name

* get local file
at selection-screen on value-request for p_file.
  call method cl_gui_frontend_services=>file_open_dialog
    exporting
      window_title            = 'Choose File'
    changing
      file_table              = t_filetable
      rc                      = w_subrc
    exceptions
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      others                  = 5.

  case sy-subrc.
    when 0.
      read table t_filetable index 1 into w_filetable.
      move w_filetable to p_file.
  endcase.

* upload the file

  call method cl_gui_frontend_services=>gui_upload
    exporting
      filename                = p_file
      filetype                = 'ASC'
      has_field_separator     = 'X'
    changing
      data_tab                = t_file
    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
      not_supported_by_gui    = 17
      error_no_gui            = 18
      others                  = 19.
  if sy-subrc eq 0.
    loop at t_file into rec_file.
* update your dbtable.
      modify ztab from rec_file.
    endloop.
  endif.

Regards,

Suresh Datti

Message was edited by: Suresh Datti

Re: put a file into a table

tamra_walstrom — Mon, 02 Jan 2006 21:09:51 GMT

There is a way to import your Excel file without changing it to a text file. We've used the following code with great success.

FORM excel_import TABLES p_tab

USING p_filename LIKE rlgrap-filename

p_rc LIKE sy-subrc.

DATA : t_table TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.

DATA : gv_index TYPE i.

DATA : gv_start_col TYPE i VALUE '1',

gv_start_row TYPE i VALUE '1',

gv_end_col TYPE i VALUE '256',

gv_end_row TYPE i VALUE '65536'.

FIELD-SYMBOLS : <$fs>.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_filename

i_begin_col = gv_start_col

i_begin_row = gv_start_row

i_end_col = gv_end_col

i_end_row = gv_end_row

TABLES

intern = t_table

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

MOVE : sy-subrc TO p_rc.

CHECK NOT t_table[] IS INITIAL.

SORT t_table BY row col.

LOOP AT t_table.

MOVE : t_table-col TO gv_index.

ASSIGN COMPONENT gv_index OF STRUCTURE p_tab TO <$fs>.

MOVE : t_table-value TO <$fs>.

AT END OF row.

APPEND p_tab.

CLEAR p_tab.

ENDAT.

ENDLOOP.

ENDFORM.

Best of luck.

Re: put a file into a table

Former Member — Wed, 03 Dec 2008 21:29:22 GMT

what if I would have to upload like 1 million records ata time from a excel file to a custom table . Is there a program that can load that many records from excel file to a custom table directly.

Regards

Aarav

Re: put a file into a table

Former Member — Thu, 04 Dec 2008 04:18:51 GMT

Hi,

Use table mant Gen to play with millions of data....

just copy & paste, no other go...

I did the same way, else do the way my friends have said through code..

Thanks & Regards,

Krishna..