topic Re: Dowloading data from excel sheet in Application Development and Automation Discussions

Dowloading data from excel sheet

Former Member — Fri, 09 Feb 2007 13:51:58 GMT

Hi all,

Please tell me the function module to download the data from excel sheet in to the internal table created in program.

And how to download the data from a particular line in the excel sheet into internal table.

Re: Dowloading data from excel sheet

Former Member — Fri, 09 Feb 2007 13:54:56 GMT

U can use

1.ALSM_EXCEL_TO_INTERNAL_TABLE

2. 'KCD_EXCEL_OLE_TO_INT_CONVERT' FM

to upload data from excel into internal table.

1. CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 3

i_end_row = 65535

TABLES

intern = it_vbak.

2. DATA : l_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.

DATA : l_index TYPE i.

DATA : l_start_col TYPE i VALUE '1',

l_start_row TYPE i VALUE '1',

l_end_col TYPE i VALUE '256',

l_end_row TYPE i VALUE '65536'.

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = p_file

i_begin_col = l_start_col

i_begin_row = l_start_row

i_end_col = l_end_col

i_end_row = l_end_row

TABLES

intern = l_intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

http://www.sap-img.com/abap/upload-direct-excel.htm

http://www.sap-img.com/abap/excel_upload_alternative-kcd-excel-ole-to-int-convert.htm

http://www.sapdevelopment.co.uk/file/file_upexcel.htm

Re: Dowloading data from excel sheet

Former Member — Fri, 09 Feb 2007 13:55:25 GMT

Hi Shweta,

I think you mean upload from an excel sheet into the internal table.

You can user GUI_UPLOAD, upload the excel into a internal table first with a single field of type string.

Then split the string using the separator CL_ABAP_CHAR_UTILITIES=>horizonal_tab into the fields of your table.

Next if you want only one of the specific rows, read that line using the READ TABLE with INDEX clause.

Hope it helps.

Aditya

Re: Dowloading data from excel sheet

Former Member — Fri, 09 Feb 2007 13:58:21 GMT

HI,

TYPES:
  BEGIN OF ty_upload,
    field1 TYPE c length 12,
    field2 TYPE c length 12,
    field3 TYPE c length 12,
  END OF ty_upload.
  DATA it_upload TYPE STANDARD TABLE OF ty_upload WITH DEFAULT KEY.
  DATA wa_upload TYPE ty_upload.
  DATA itab TYPE STANDARD TABLE OF alsmex_tabline WITH DEFAULT KEY.
  FIELD-SYMBOLS: <wa> type alsmex_tabline.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename    = filename
      i_begin_col = 1
      i_begin_row = 1
      i_end_col   = 3
      i_end_row   = 65535
    TABLES
      intern      = itab.

  LOOP AT itab ASSIGNING <wa>.
    CASE <wa>-col.
      WHEN '0001'.
        wa_upload-field1 = <wa>-value.
      WHEN '0002'.
        wa_upload-field2 = <wa>-value.
      WHEN '0003'.
        wa_upload-field3 = <wa>-value.
    ENDCASE.
    APPEND wa_upload TO it_upload.
    CLEAR wa_upload.
  ENDLOOP

http://www.sap-img.com/abap/upload-direct-excel.htm

<b>Another Program</b>

*&---------------------------------------------------------------------*
*& Report  UPLOAD_EXCEL                                                *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*& Upload and excel file into an internal table using the following    *
*& function module: ALSM_EXCEL_TO_INTERNAL_TABLE                       *
*&---------------------------------------------------------------------*
REPORT  UPLOAD_EXCEL no standard page heading.

*Data Declaration
*----------------
data: itab like alsmex_tabline occurs 0 with header line.
* Has the following format:
*             Row number   | Colum Number   |   Value
*             ---------------------------------------
*      i.e.     1                 1             Name1
*               2                 1             Joe

TYPES: Begin of t_record,
    name1 like itab-value,
    name2 like itab-value,
    age   like itab-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.

*Selection Screen Declaration
*----------------------------
PARAMETER p_infile like rlgrap-filename.


************************************************************************
*START OF SELECTION
 call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       exporting
            filename                = p_infile
            i_begin_col             = '1'
            i_begin_row             = '2'  "Do not require headings
            i_end_col               = '14'
            i_end_row               = '31'
       tables
            intern                  = itab
       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 itab by row col.

* Get first row retrieved
  read table itab index 1.

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

  loop at itab.
*   Reset values for next row
    if itab-row ne gd_currentrow.
      append wa_record to it_record.
      clear wa_record.
      gd_currentrow = itab-row.
    endif.

    case itab-col.
      when '0001'.                              "First name
        wa_record-name1 = itab-value.
      when '0002'.                              "Surname
        wa_record-name2 = itab-value.
      when '0003'.                              "Age
        wa_record-age   = itab-value.
    endcase.
  endloop.
  append wa_record to it_record.
*!! Excel data is now contained within the internal table IT_RECORD

* Display report data for illustration purposes
  loop at it_record into wa_record.
    write:/     sy-vline,
           (10) wa_record-name1, sy-vline,
           (10) wa_record-name2, sy-vline,
           (10) wa_record-age, sy-vline.
  endloop.

Regards

Sudheer

Re: Dowloading data from excel sheet

Former Member — Fri, 09 Feb 2007 14:01:10 GMT

Hello,

You can use the Function module ALSM_EXCEL_TO_INTERNAL_TABLE to read the Excel file into the internal table of type alsmex_tabline. From this internal table you can fill the target internal table.

TYPES:

BEGIN OF ty_upload,

field1 TYPE c length 12,

field2 TYPE c length 12,

field3 TYPE c length 12,

END OF ty_upload.

DATA it_upload TYPE STANDARD TABLE OF ty_upload WITH DEFAULT KEY.

DATA wa_upload TYPE ty_upload.

DATA itab TYPE STANDARD TABLE OF alsmex_tabline WITH DEFAULT KEY.

FIELD-SYMBOLS: <wa> type alsmex_tabline.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 3

i_end_row = 65535

TABLES

intern = itab.

LOOP AT itab ASSIGNING <wa>.

CASE <wa>-col.

WHEN '0001'.

wa_upload-field1 = <wa>-value.

WHEN '0002'.

wa_upload-field2 = <wa>-value.

WHEN '0003'.

wa_upload-field3 = <wa>-value.

ENDCASE.

APPEND wa_upload TO it_upload.

CLEAR wa_upload.

ENDLOOP.

Vasanth

Re: Dowloading data from excel sheet

Former Member — Fri, 09 Feb 2007 14:07:19 GMT

Hi Shweta,

You can use Function module FAA_FILE_UPLOAD_EXCEL for uploading data from excel sheet. This function module will upload the data in internal table using comma as a field seprator. If you want to change the field separator the copy the function module to Z func module & use it.

Ashven