topic Re: Download data from EXCEL sheet to Database Table. in Application Development and Automation Discussions

Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 08:54:13 GMT

Hi Every one,

I want to Upload data from EXCEL sheet to Database table.. Are there any standard Function Modules to perform this task.

Regards,

Yugesh A

Re: Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 08:58:48 GMT

Hi Yugesh,

You can use GUI_UPLOAD to get the excel sheet data into a internal table. After this the corresponding table can be updated from the internal table using the following syntax.

Update dbtable

SET field1 = itab1-field1

field2 = itab1-field2

where (conditions if you have any).

Regards,

Vik

Re: Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 09:02:48 GMT

Hi,

Please read the Excel file using the FM TEXT_CONVERT_XLS_TO_SAP and manually upload it to the table using MODIFY statement.

Thanks,

Renjith.

Re: Download data from EXCEL sheet to Database Table.

sridhar_meesala — Mon, 20 Jul 2009 09:05:49 GMT

Hi,

Use the FM 'TEXT_CONVERT_XLS_TO_SAP' to upload Excel to internal table. Then you can update your Database table from the internal table.

Thanks,

Sri.

Re: Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 09:06:40 GMT

Hi Yugesh,

Please Search in SCN Before Posting

Just See example below,

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR = ','

i_line_header = p_head

i_tab_raw_data = it_raw

i_filename = p_upl

TABLES

i_tab_converted_data = t_indata

EXCEPTIONS

conversion_failed = 1

OTHERS = 2

Get the Records to you internal table,then loop your internal table and update the database accordingly.

Edited by: suresh suresh on Jul 20, 2009 11:15 AM

Re: Download data from EXCEL sheet to Database Table.

venkat_o — Mon, 20 Jul 2009 09:09:01 GMT

HI Yogesh, <li> You can use GUI_UPLOAD function module or TEXT_CONVERT_XLS_TO_SAP <li> You can use cl_gui_frontend_services->Gui_upload method to upload data. Thanks Venkat.O

Re: Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 09:20:55 GMT

Hi,

Function module GUI_UPLOAD

Class cl_gui_frontend_services->Gui_upload.

Regards,

ShreeMohan

Re: Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 09:22:04 GMT

********************Store file

data: wa_att_file like swotobjid.
  data: it_att_file like
              table of wa_att_file.
  data: dummy_sodocchgi1 type  sodocchgi1.
  clear wa_att_file.
  refresh it_att_file.

  wa_att_file-logsys = Number.       " Any number
  wa_att_file-objtype = 'ATT'.
  wa_att_file-objkey = '01'.
  append wa_att_file to it_att_file.

  call function 'SO_WIND_ATTACHMENT_CREATE_API1'
       exporting
*            attachment_data     = ''
            attachment_data     = dummy_sodocchgi1
            attachment_type     = 'DOC'
       tables
            application_objects = it_att_file.

********Display list of file............

data: wa_att_file like swotobjid.
  data: it_att_file type
              table of swotobjid.
  data:it_exclude_func type
                table of soxet
                with header line.
  clear wa_att_file.
  refresh it_att_file.

  wa_att_file-logsys = Number.
  wa_att_file-objtype = 'ATT'.
  wa_att_file-objkey = '01'.
** Exclude function code............
  it_exclude_func-fcode = 'CHNG'.
  append it_exclude_func.

  call function 'SO_WIND_ATTACHMENT_LIST_API1'
    exporting
      application_object       = wa_att_file
*   FUNCTION                 = ' '
 tables
   func_exclude             = it_exclude_func
            .

Regards,

Gurpreet

Re: Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 09:51:33 GMT

Hi,

Use the function module TEXT_CONVERT_XLS_TO_SAP'

defien the types declaration according to the table.

DATA:
      fs_s TYPE type_s_upload, 
      t_upload LIKE STANDARD TABLE OF fs_s.


DATA:
      fs_truxs TYPE truxs_t_text_data.



      CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
        EXPORTING
*         I_FIELD_SEPERATOR          =
*         I_LINE_HEADER              =
          i_tab_raw_data             = fs_truxs
          i_filename                 = <FILE-PATH>
        tables
          i_tab_converted_data       = t_upload
*       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.



LOOP AT t_upload INTO fs_s.

  INSERT <Database- table>FROM fs_s.
ENDLOOP.

Hope this solves the issue

Regards,

Rajani

Re: Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 09:56:24 GMT

Hi,

Use function module 'TEXT_CONVERT_XLS_TO_SAP'

to get the data from EXCEL to SAP internal table

declare the internal it_upload table as per your need .

see the code below

call function 'TEXT_CONVERT_XLS_TO_SAP'

exporting

i_field_seperator = 'X'

I_LINE_HEADER =

i_tab_raw_data = it_raw

i_filename = p_file

tables

i_tab_converted_data = it_upload[]

exceptions

conversion_failed = 1

others = 2

Thanks

Deepanker Dwivedi

Re: Download data from EXCEL sheet to Database Table.

knitinkumarsethy — Mon, 20 Jul 2009 10:51:55 GMT

HI,

If there is only one table for which u want to up[load the data, then create a table maintenance for the table.

Arrange the column of the excel sheet according to the tables column. Copy the Rows of excel sheet & paste in table table maintenance screen (SM30), Other wise you upload the data to you report program through Function module GUI_UPLOAD to you internal table and run a database update query.

If more than one tables need to be updated then you need to run BDC.

Regards,

Nitin.

Edited by: K Nitin Kumar Sethy on Jul 20, 2009 4:26 PM

Re: Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 12:21:07 GMT

Hi Suresh,

Thanks for ur Response.. but I am working on SRD server.. The FM that u specified is not available in SRD server.. Do u know any other FM's?

Regards,

Yugesh A

Re: Download data from EXCEL sheet to Database Table.

Former Member — Mon, 20 Jul 2009 12:23:41 GMT

hello Rajani,

Thanks for ur Reply.. Th Fm u specified was not there in SRD server.. Will u please tell if there are any other FM's that will be present in SRD server..

Regerds,

Yugesh A

Re: Download data from EXCEL sheet to Database Table.

Former Member — Thu, 06 Aug 2009 04:55:20 GMT

The Issue I am facing is resolved. When we are uploading data from excel sheet to Internal table first we have to save it as tab delimited and then we have to delete the field names in the excel sheet. Else it will through a message 'cannot interpret data in file'. Thanks all for their help.