topic Re: Upload Excel data into internal table in Application Development and Automation Discussions

Upload Excel data into internal table

former_member549415 — Fri, 30 May 2008 14:35:38 GMT

Hi to all,

i'hv downloaded the MARA data into excel format using SE16N transaction .and trying to upload all data into internal table using Function Module

"ALSM_EXCEL_TO_INTERNAL_TABLE"

in that i hv pass the internal table .. but in the following fields which parameter i should have to pass. ?

EXPORTING :

I_BEGIN_COL

I_BEGIN_ROW

I_END_COL

I_END_ROW

please tell me with sample code..

Thanks in advanced..

Re: Upload Excel data into internal table

valter_oliveira — Fri, 30 May 2008 14:40:49 GMT

Hello.

If you want to get all data from mara, and populate an internal table, why downloading to excel and then upload? Why don't you get data using SELECT statement?

Best regards.

Valter Oliveira.

Re: Upload Excel data into internal table

former_member549415 — Fri, 30 May 2008 15:01:37 GMT

But if the provided data is in excel file ,thats why i want to know the procedure by using this Function module..

Re: Upload Excel data into internal table

Former Member — Fri, 30 May 2008 15:05:10 GMT

Hi Somnath kalamkar,

Please check this code

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

*&---------------------------------------------------------------------*
*& 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.

Best regards,

raam

Re: Upload Excel data into internal table

valter_oliveira — Fri, 30 May 2008 15:06:03 GMT

Hi again.

So, consider to transform excel in CSV file and then use:


CALL FUNCTION 'UPLOAD'
    EXPORTING
      filename           = 'C:\'
      filetype           = 'ASC'
   IMPORTING
      cancel             = cancel
    TABLES
      data_tab           = itab.

and then,


LOOP AT itab INTO wa.
    SPLIT wa-line AT ';'
     INTO wa2-field1
             wa2-field2
             wa2-fieldn.
    APPEND wa2 TO itab2.
ENDLOOP.

Best regards.

Valter Oliveira.

Re: Upload Excel data into internal table

Former Member — Mon, 02 Jun 2008 05:08:55 GMT

hi,

for uploading the excel the function modules used are:

call FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

call FUNCTION TEXT_CONVERT_XLS_TO_SAP,

for 'ALSM_EXCEL_TO_INTERNAL_TABLE' :

TYPES: BEGIN OF TY_ITAB,

some fields

END OF TY_ITAB.

DATA: T_ITAB TYPE TABLE OF TY_ITAB,

w_iTAB TYPE TY_ITAB.

DATA : ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA: R1 TYPE I VALUE 1,

C1 TYPE I VALUE 1,

R2 TYPE I VALUE 4,

C2 TYPE I VALUE 8,

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = FILEPATH

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 8

I_END_ROW = 4

TABLES

INTERN = ITAB1

EXCEPTION

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.

SORT ITAB1 BY ROW COL.

LOOP AT ITAB1.

CASE ITAB1-COL.

WHEN 1.

W_ITAB-LIFNR = ITAB1-VALUE.

WHEN 2.

W_ITAB-KTOKK = ITAB1-VALUE.

WHEN 3.

W_ITAB-NAME1 = ITAB1-VALUE.

WHEN 4.

W_ITAB-SORTL = ITAB1-VALUE.

WHEN 5.

W_ITAB-LAND1 = ITAB1-VALUE.

WHEN 6.

W_ITAB-SPRAS = ITAB1-VALUE.

ENDCASE.

AT END OF ROW.

APPEND W_ITAB TO T_ITAB.

CLEAR W_ITAB.

ENDAT.

ENDLOOP.

reward if useful,

thanks and regards

Re: Upload Excel data into internal table

Former Member — Mon, 02 Jun 2008 07:38:22 GMT

Refer the sample code.

&----

*& Report ZUK_WIP11_EXCEL_FILE

&----

REPORT ZUK_WIP11_EXCEL_FILE NO STANDARD PAGE HEADING MESSAGE-ID ZMSG44 LINE-SIZE 150 LINE-COUNT 65(3).

TYPES : BEGIN OF S_EMP,

F1(20),

F2(10),

F3 TYPE I,

F4 TYPE I,

F5 TYPE I,

F6 TYPE I,

F7 TYPE I,

F8 TYPE I,

F9 TYPE I,

END OF S_EMP.

Internal table to handle emp data

DATA : ITAB TYPE TABLE OF S_EMP,

WA TYPE S_EMP.

DATA : ITAB1 TYPE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

PARAMETERS : P_FILE TYPE RLGRAP-FILENAME.

SELECTION-SCREEN END OF BLOCK B1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING
PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '

IMPORTING

FILE_NAME = P_FILE

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 10

I_END_ROW = 5

TABLES

INTERN = ITAB1

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.

LOOP AT ITAB1.

CASE ITAB1-COL.

WHEN '001'.

WA-F1 = ITAB1-VALUE.

WHEN '002'.

WA-F2 = ITAB1-VALUE.

WHEN '003'.

WA-F3 = ITAB1-VALUE.

WHEN '004'.

WA-F4 = ITAB1-VALUE.

WHEN '005'.

WA-F5 = ITAB1-VALUE.

WHEN '006'.

WA-F6 = ITAB1-VALUE.

WHEN '007'.

WA-F7 = ITAB1-VALUE.

WHEN '008'.

WA-F8 = ITAB1-VALUE.

WHEN '009'.

WA-F9 = ITAB1-VALUE.

ENDCASE.

AT END OF ROW.

APPEND WA TO ITAB.

CLEAR WA.

ENDAT.

ENDLOOP.

LOOP AT ITAB INTO WA.

WRITE : / WA-F1, WA-F2,WA-F3,WA-F4,WA-F5,WA-F6,WA-F6,WA-F7,WA-F8,WA-F9.

ENDLOOP.

Rgds

Uamkanth

Re: Upload Excel data into internal table

Former Member — Wed, 02 Jul 2008 05:50:27 GMT

If you want to upload data from excel with multiple worksheets

(say 6 worksheets) into internal tables then modify the function module "ALSM_EXCEL_TO_INTERNAL_TABLE" as follows:

Added one more IMPORTING parameter :-

VALUE(SHEET_NO) TYPE I.

Then added the following code :-

DATA : sheet TYPE ole2_object.

open file in Excel

IF application-header = space OR application-handle = -1.

CREATE OBJECT application 'Excel.Application'.

m_message.

ENDIF.

CALL METHOD OF application 'Workbooks' = workbook.

m_message.

CALL METHOD OF workbook 'Open' EXPORTING #1 = filename.

m_message.

********************START ADDING********************

CASE sheet_no.

WHEN 1.