Excel to Internal Table

Former Member · ‎2005 Dec 30

Hi Folks

I have a problem, please help me out.

I have a excel file like below

Description Type Length Decimals

-

Payroll Area Char 2

Employee No. Numc 8

wage Type Char 4

Number P.Dec 7 2

Units Char 8

Date Char 10 (or)

Date Date 8

I want to make this structure in Excel to internal table in SAP ABAP.

please give any sample piece of code.

Awaiting for your Responce

Regards

Praveen

Former Member · ‎2005 Dec 30

data:begin of t_data occurs 0,

Description(25),

Type(10),

Length(10),

Decimals(5),

end of t_data.

parameters : p_filename like rlgrap-filename value 'server001/datasap/test/upload.csv' .

data: filename type string,

t_data type table of string.

filename = p_filename.

call function 'GUI_UPLOAD'

exporting

filename = filename

filetype = 'ASC'

tables

data_tab = t_data

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.

Message was edited by: chandrasekhar jagarlamudi

Former Member · ‎2005 Dec 30

Hi Chandra

Thanks for your help. But it doesn't working. It doesn't populate the data.

Regards

Praveen

Former Member · ‎2005 Dec 30

can u paste ur code

Former Member · ‎2005 Dec 30

hi use

Function module

ALSM_EXCEL_TO_INTERNAL_TABLE

regards

vijay

Former Member · ‎2005 Dec 30

Hi,

Check this link

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

Regards,

Sakthi.

Former Member · ‎2005 Dec 30

hi,

An option is to use FM 'ALSM_EXCEL_TO_INTERNAL_TABLE'

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = xls_file

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 6

I_END_ROW = 500

TABLES

INTERN = INTERTAB

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2.

Another way is to the office intergration utilities, especialy the speadsheet interface "i_oi_spreadsheet" and "i_oi_document_proxy".

here is a sample code which imports and exports from excel to R/3 and R/3 to excel.

hope this might help you.

*&---------------------------------------------------------------------*
*& Report  YTEST5                                                      *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*&                                                                     *
*&---------------------------------------------------------------------*
 
                       .
 
 
 
 
 
*----------------------------------------------------------------------
*      N Overton : Created ( T/Code ZTUD)
*
*      Dynamic table export / import facility.
 
*----------------------------------------------------------------------
* TEXTS
*
* List Header...: For SAP Table: &1............................
*
* Column Heading: | Function    Status(48 spaces)# Records  |
*
* Selection Texts: PATH         Folder  Path for file
*                  P_CLEAR      Delete Existing Records
*                  P_EXP        Export to PC File
*                  P_IMP        Import from PC File
*                  TABNAME      SAP table name
*
* Text Symbols:    B00  Dynamic Import/Export of SAP data
*                  B01  SAP Table Name
*                  B02  Program Function
*                  B03  Folder For Data File
*                  B04  Existing Records Deletion Selection
*                  ER2  File not found. Please check.
*                  PGE  Page
*----------------------------------------------------------------------
* This program once created will allow you to download or upload table
* data from any SAP table. It has the functionality to allow you to
* select whether data should be appended or original data cleaed before
* inserting new data.
* This is very useful when attempting to transfer data from one client
* to another
*----------------------------------------------------------------------
 
REPORT ytest5 LINE-SIZE 80
                LINE-COUNT 65
                NO STANDARD PAGE HEADING.
 
TABLES: dd02l, dd03l.
 
* selection screen
SELECTION-SCREEN BEGIN OF BLOCK b00 WITH FRAME TITLE text-b00.
*
SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME TITLE text-b01.
PARAMETERS: tabname     LIKE dd02l-tabname OBLIGATORY.
SELECTION-SCREEN END OF BLOCK b01.
*
SELECTION-SCREEN BEGIN OF BLOCK b03 WITH FRAME TITLE text-b03.
PARAMETERS: path(30)    TYPE c DEFAULT 'C:SAPWorkdir'.
SELECTION-SCREEN END OF BLOCK b03.
*
SELECTION-SCREEN BEGIN OF BLOCK b04 WITH FRAME TITLE text-b04.
PARAMETERS: p_exp RADIOBUTTON GROUP radi,
            p_imp RADIOBUTTON GROUP radi,
            p_clear     AS CHECKBOX.
SELECTION-SCREEN END OF BLOCK b04.
 
SELECTION-SCREEN END OF BLOCK b00.
 
* data
DATA: q_return     LIKE syst-subrc,
      err_flag(1)  TYPE c,
      answer(1)    TYPE c,
      w_text1(62)  TYPE c,
      w_text2(40)  TYPE c,
      winfile(128) TYPE c,
      w_system(40) TYPE c,
      winsys(7)    TYPE c,
      zname(8)     TYPE c,
      w_line(80)   TYPE c.
 
* internal tables
DATA : BEGIN OF textpool_tab OCCURS 0.
        INCLUDE STRUCTURE textpool.
DATA : END OF textpool_tab.
 
* table for subroutine pool
DATA : itab(80) OCCURS 0.
 
* events
INITIALIZATION.
  PERFORM check_system.
*
AT SELECTION-SCREEN ON tabname.
  PERFORM check_table_exists.
*
START-OF-SELECTION.
  PERFORM init_report_texts.
  PERFORM request_confirmation.
*
END-OF-SELECTION.
  IF answer = 'J'.
    PERFORM execute_program_function.
  ENDIF.
*
TOP-OF-PAGE.
  PERFORM process_top_of_page.
 
* forms
*---------------------------------------------------------------------*
*       FORM CHECK_TABLE_EXISTS                                      *
*---------------------------------------------------------------------*
FORM check_table_exists.
  SELECT SINGLE * FROM dd02l
  INTO CORRESPONDING FIELDS OF dd02l
  WHERE tabname = tabname.
  CHECK syst-subrc NE 0.
  MESSAGE e402(mo) WITH tabname.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM INIT_REPORT_TEXTS                                        *
*---------------------------------------------------------------------*
FORM init_report_texts.
  READ TEXTPOOL syst-repid
  INTO textpool_tab LANGUAGE syst-langu.
  LOOP AT textpool_tab
  WHERE id EQ 'R' OR id EQ 'T'.
    REPLACE '&1............................'
    WITH tabname INTO textpool_tab-entry.
    MODIFY textpool_tab.
  ENDLOOP.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM REQUEST_CONFIRMATION                                     *
*---------------------------------------------------------------------*
FORM request_confirmation.
 
* import selected, confirm action
  IF p_imp = 'X'.
*   build message text for popup
    CONCATENATE 'Data for table'
                 tabname
                 'will be imported' INTO w_text1 SEPARATED BY space.
*   check if delete existing selected, and change message text
    IF p_clear = ' '.
      w_text2 = 'and appended to the end of existing data'.
    ELSE.
      w_text2 = 'Existing Data will be deleted'.
    ENDIF.
 
    CALL FUNCTION 'POPUP_TO_CONFIRM_STEP'
         EXPORTING
              defaultoption  = 'N'
              textline1      = w_text1
              textline2      = w_text2
              titel          = 'Confirm Import of Data'
              cancel_display = ' '
         IMPORTING
              answer         = answer
         EXCEPTIONS
              OTHERS         = 1.
  ELSE.
*   export selected, set answer to yes so export can continue
    answer = 'J'.
  ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM EXECUTE_PROGRAM_FUNCTION                                 *
*---------------------------------------------------------------------*
FORM execute_program_function.
  PERFORM build_file_name.
  CLEAR: q_return,err_flag.
 
  IF p_imp = 'X'.
    PERFORM check_file_exists.
    CHECK err_flag = ' '.
    PERFORM func_import.
  ELSE.
    PERFORM func_export.
  ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM BUILD_FILE_NAME                                          *
*---------------------------------------------------------------------*
FORM build_file_name.
  MOVE path TO winfile.
  WRITE '' TO winfile+30.
  WRITE tabname TO winfile+31.
  WRITE '.TAB' TO winfile+61(4).
  CONDENSE winfile NO-GAPS.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM CHECK_FILE_EXISTS                                        *
*---------------------------------------------------------------------*
FORM check_file_exists.
 
  CALL FUNCTION 'WS_QUERY'
       EXPORTING
            filename = winfile
            query    = 'FE'
       IMPORTING
            return   = q_return
       EXCEPTIONS
            OTHERS   = 1.
 
  IF syst-subrc NE 0 OR q_return NE 1.
    err_flag = 'X'.
  ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
*     FORM func_export                                              *
*---------------------------------------------------------------------*
FORM func_export.
  CLEAR itab. REFRESH itab.
 
  APPEND 'PROGRAM SUBPOOL.' TO itab.
 
  APPEND 'FORM DOWNLOAD.' TO itab.
  APPEND 'DATA: BEGIN OF IT_TAB OCCURS 0.' TO itab.
  CONCATENATE 'INCLUDE STRUCTURE'
              tabname
              '.' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'DATA: END OF IT_TAB.' TO itab.
 
  CONCATENATE 'SELECT * FROM'
              tabname
              'INTO TABLE IT_TAB.' INTO w_line  SEPARATED BY space.
  APPEND w_line TO itab.
 
  APPEND 'CALL FUNCTION ''WS_DOWNLOAD''' TO itab.
  APPEND 'EXPORTING' TO itab.
  CONCATENATE 'filename = ' ''''
              winfile '''' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'filetype = ''DAT''' TO itab.
  APPEND 'TABLES' TO itab.
  APPEND 'DATA_TAB = IT_TAB.' TO itab.
 
  APPEND 'DESCRIBE TABLE IT_TAB LINES sy-index.' TO itab.
 
  APPEND 'FORMAT COLOR COL_NORMAL INTENSIFIED OFF.' TO itab.
  APPEND 'WRITE: /1 syst-vline,' TO itab.
  APPEND '''EXPORT'',' TO itab.
  APPEND '15 ''data line(s) have been exported'',' TO itab.
  APPEND '68 syst-index,' TO itab.
  APPEND '80 syst-vline.' TO itab.
  APPEND 'ULINE.' TO itab.
 
  APPEND 'ENDFORM.' TO itab.
 
  GENERATE SUBROUTINE POOL itab NAME zname.
  PERFORM download IN PROGRAM (zname).
ENDFORM.
*---------------------------------------------------------------------*
*       FORM func_import                                              *
*---------------------------------------------------------------------*
FORM func_import.
  CLEAR itab. REFRESH itab.
  APPEND 'PROGRAM SUBPOOL.' TO itab.
 
  APPEND 'FORM UPLOAD.' TO itab.
  APPEND 'DATA: BEGIN OF IT_TAB OCCURS 0.' TO itab.
  CONCATENATE 'INCLUDE STRUCTURE'
              tabname
              '.' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'DATA: END OF IT_TAB.' TO itab.
  APPEND 'DATA: BEGIN OF IT_TAB2 OCCURS 0.' TO itab.
  CONCATENATE 'INCLUDE STRUCTURE'
              tabname
              '.' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'DATA: END OF IT_TAB2.' TO itab.
 
  APPEND 'CALL FUNCTION ''WS_UPLOAD''' TO itab.
  APPEND 'EXPORTING' TO itab.
  CONCATENATE 'filename = ' ''''
              winfile '''' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'filetype = ''DAT''' TO itab.
  APPEND 'TABLES' TO itab.
  APPEND 'DATA_TAB = IT_TAB.' TO itab.
 
  IF p_clear = 'X'.
    CONCATENATE 'SELECT * FROM'
                tabname
                'INTO TABLE IT_TAB2.' INTO w_line SEPARATED BY space.
    APPEND w_line TO itab.
 
    APPEND 'LOOP AT IT_TAB2.' TO itab.
    CONCATENATE 'DELETE'
                tabname
                'FROM IT_TAB2.' INTO w_line SEPARATED BY space.
    APPEND w_line TO itab.
    APPEND 'ENDLOOP.' TO itab.
    APPEND 'COMMIT WORK.' TO itab.
  ENDIF.
 
  APPEND 'LOOP AT IT_TAB.' TO itab.
  CONCATENATE 'MODIFY'
              tabname
              'FROM IT_TAB.' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'ENDLOOP.' TO itab.
 
  APPEND 'DESCRIBE TABLE IT_TAB LINES sy-index.' TO itab.
 
  APPEND 'FORMAT COLOR COL_NORMAL INTENSIFIED OFF.' TO itab.
  APPEND 'WRITE: /1 syst-vline,' TO itab.
  APPEND '''IMPORT'',' TO itab.
  APPEND '15 ''data line(s) have been imported'',' TO itab.
  APPEND '68 syst-index,' TO itab.
  APPEND '80 syst-vline.' TO itab.
  APPEND 'ULINE.' TO itab.
 
  APPEND 'ENDFORM.' TO itab.
 
  GENERATE SUBROUTINE POOL itab NAME zname.
  PERFORM upload IN PROGRAM (zname).
ENDFORM.
*---------------------------------------------------------------------*
*       Form  CHECK_SYSTEM
*            Check users workstation is running
*            WINDOWS 95, or WINDOWS NT.
*            OS/2 uses 8.3 file names which are no good for
*            this application as filenames created are 30 char
*            same as table name.
*            You could change the logic to only use the first 8 chars
*            of the table name for the filename, but you could possibly
*            get problems if users had exported already with a table
*            with the same first 8 chars.
*            As an alternate method you could request the user to input
*            the full path including filename and remove the logic to
*            build the path using the table name.
*---------------------------------------------------------------------*
FORM check_system.
  CALL FUNCTION 'WS_QUERY'
       EXPORTING
            query  = 'WS'
       IMPORTING
            return = winsys.
 
  IF winsys NE 'WN32_95'.
    WRITE: 'Windows NT or Windows 95/98 is required'.
    EXIT.
  ENDIF.
 
ENDFORM.                               " CHECK_SYSTEM
*---------------------------------------------------------------------*
*       FORM PROCESS_TOP_OF_PAGE                                      *
*---------------------------------------------------------------------*
FORM process_top_of_page.
  FORMAT COLOR COL_HEADING INTENSIFIED ON.
  ULINE.
 
  CONCATENATE syst-sysid
              syst-saprl
              syst-host INTO w_system SEPARATED BY space.
 
  WRITE : AT /1(syst-linsz) w_system CENTERED.
  WRITE : AT 1 syst-vline, syst-uname.
  syst-linsz = syst-linsz - 11.
  WRITE : AT syst-linsz syst-repid(008).
  syst-linsz = syst-linsz + 11.
  WRITE : AT syst-linsz syst-vline.
 
  LOOP AT textpool_tab WHERE id EQ 'R'.
    WRITE : AT /1(syst-linsz) textpool_tab-entry CENTERED.
  ENDLOOP.
  WRITE : AT 1 syst-vline, syst-datum.
  syst-linsz = syst-linsz - 11.
  WRITE : AT syst-linsz syst-tcode(004).
  syst-linsz = syst-linsz + 11.
  WRITE : AT syst-linsz syst-vline.
 
  LOOP AT textpool_tab WHERE id EQ 'T'.
    WRITE : AT /1(syst-linsz) textpool_tab-entry CENTERED.
  ENDLOOP.
  WRITE : AT 1 syst-vline, syst-uzeit.
  syst-linsz = syst-linsz - 11.
  WRITE : AT syst-linsz 'Page', syst-pagno.
  syst-linsz = syst-linsz + 11.
  WRITE : AT syst-linsz syst-vline.
  ULINE.
 
  FORMAT COLOR COL_HEADING INTENSIFIED OFF.
  LOOP AT textpool_tab WHERE id EQ 'H'.
    WRITE : AT /1(syst-linsz) textpool_tab-entry.
  ENDLOOP.
 
  ULINE.
ENDFORM.

regs,

jaga

Former Member · ‎2005 Dec 30

hi

use WS_EXCEL-The TXXL function group uses the DLLs and other files installed with SAPGUI in directory: ...\Program Files\SAP\FrontEnd\SAPgui\xxl

you could find the sol here

http://www.geocities.com/victorav15/sapr3/abapfun.html

regs

Arun

Message was edited by: arun a v

Former Member · ‎2005 Dec 30

Hi Praveen

DATA: ITAB TYPE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = 'C:\TEST.XLS'

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 6

I_END_ROW = 500

TABLES

INTERN = ITAB

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.

in above example, the excel sheet can even contain first row as row for headings, as it will read data starting from row 2 :

I_BEGIN_ROW = 2

cheers,

Vijay Raheja

Former Member · ‎2005 Dec 30

Hi Vijay

Thanks for your updation. here I have a problem when working with your program. If we run as per your program I will migrate all date to internal table as which you are given ALSM_EXCEL_TO_INTERNAL_TABLE. That internal table contins each row as row no, column no and value.

Now I want to store these values into a internal table. as per my structure. If you know about this please help me.

Regars

Praveen

Former Member · ‎2005 Dec 30

you could use this interface code

REPORT ZZBGS700 MESSAGE-ID Z1.

PARAMETERS: XLSFILE(64) TYPE C DEFAULT

'C:\BGS\SAP\MODUL-BC\OLE\ZZBGS700.XLS'.

TABLES USR03.

DATA: USERS LIKE USR03 OCCURS 100 WITH HEADER LINE

,ITEMS TYPE I.

CONSTANTS: OK TYPE I VALUE 0.

INCLUDE OLE2INCL.

DATA: EXCEL TYPE OLE2_OBJECT,

WORKBOOK TYPE OLE2_OBJECT,

SHEET TYPE OLE2_OBJECT,

CELL TYPE OLE2_OBJECT,

ROW TYPE OLE2_OBJECT.

----

You find SAP OLE programs under development Class 'SOLE' *
*
MSTAPPL Table Maintenance APPL *
RSOLEDOC Document list *
RSOLEIN0 OLE Load Type Information *
RSOLEINT Type Info Loaded *
RSOLETI0 OLE Object Browser *
RSOLETI1 OLE Object Browser *
RSOLETI2 OLE Object Browser *
RSOLETI3 F4 Help For OLE Objects *
RSOLETT1 OLE 2.0 Automation Demo Program *
*
Transactions: *
SOLE *
SOLO - List of OLE applcations with loaded type info *
*
*
You will find the decription of possible objects and methods in the *
windows help file for excel. *

----

Create an Excel object and start Excel.

CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

IF SY-SUBRC NE OK.

MESSAGE I000 WITH SY-MSGLI.

ENDIF.

Create an Excel workbook Object.

CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK .

Put Excel in background

SET PROPERTY OF EXCEL 'VISIBLE' = 0 .

Collect the data to the transfer.

SELECT * FROM USR03 INTO TABLE USERS.

DESCRIBE TABLE USERS LINES ITEMS.

CHECK ITEMS > 0.

Put Excel in front.

SET PROPERTY OF EXCEL 'VISIBLE' = 1 .

Transfer the header line to Excel.

CALL METHOD OF WORKBOOK 'OPEN' EXPORTING #1 = XLSFILE.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'A1'.

SET PROPERTY OF CELL 'VALUE' = 'BNAME' .

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'B1'.

SET PROPERTY OF CELL 'VALUE' = 'NAME1' .

Transfer the internal table values to Excel

LOOP AT USERS.

CALL METHOD OF EXCEL 'ROWS' = ROW EXPORTING #1 = '2' .

CALL METHOD OF ROW 'INSERT' NO FLUSH.

CALL METHOD OF EXCEL 'RANGE' = CELL NO FLUSH EXPORTING #1 = 'A2' .

SET PROPERTY OF CELL 'VALUE' = USERS-BNAME NO FLUSH.

CALL METHOD OF EXCEL 'RANGE' = CELL NO FLUSH EXPORTING #1 = 'B2' .

SET PROPERTY OF CELL 'VALUE' = USERS-NAME1 NO FLUSH.

ENDLOOP.

release and exit Excel.

CALL METHOD OF EXCEL 'SAVE'.

CALL METHOD OF EXCEL 'QUIT'.

Free all objects

FREE OBJECT CELL.

FREE OBJECT WORKBOOK.

FREE OBJECT EXCEL.

EXCEL-HANDLE = -1.

FREE OBJECT ROW.

Message was edited by: arun a v

Former Member · ‎2005 Dec 30

Hi Arun

Thanks for your help, but its not working. Please help me out from this problem.

Regards

Praveen

Former Member · ‎2005 Dec 30

Hi Praveen,

Best way is use the func.'ALSM_EXCEL_TO_INTERNAL_TABLE'

Please see the below sample code for this.


*--Type Pool Declaration declaration for User ID Details
Types : BEGIN OF tp_final ,
          USRID          type /EMN/HR_CU_SGA-USRID,   " User ID
          SG_ID          type /EMN/HR_CU_SGA-SG_ID,   " Securitygroup ID
          AM_ID          type /EMN/HR_CU_ACCMS-AM_ID, " AccessID
          begda          type /EMN/HR_CU_SGA-begda,   " Start date
        END OF tp_final.
*--Internal table declaration for User Details
data : dt_final type tp_final OCCURS 0 WITH HEADER LINE.


  DATA : dl_begin_col TYPE i VALUE 1,     " Col No
         dl_begin_row TYPE i VALUE 1,     " Row no
         dl_end_col   TYPE i VALUE 4,     " Col No
         dl_end_row   TYPE i VALUE 65536, " Row no
         dl_index TYPE i.                 " Index
  FIELD-SYMBOLS <fs> TYPE ANY.     " Field Symbols
* Call function for Upload
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       EXPORTING
            filename                = p_file
            i_begin_col             = dl_begin_col
            i_begin_row             = dl_begin_row
            i_end_col               = dl_end_col
            i_end_row               = dl_end_row
       TABLES
            intern                  = dt_intern
       EXCEPTIONS
            inconsistent_parameters = 1
            upload_ole              = 2
            OTHERS                  = 3.

  IF sy-subrc <> 0.
*   Error Message - Invalid entry
    MESSAGE i999 WITH 'Error in opening File'(004).
    STOP.
  ENDIF.
  IF dt_intern[] IS INITIAL.

*   Error Message - Invalid entry
    MESSAGE i999 WITH 'No data in the file'(005).
    STOP.
  ELSE.
    SORT dt_intern BY row col.
*   Move the data into the internal table.
    LOOP AT dt_intern.
      MOVE dt_intern-col TO dl_index.
*     Assign component index of structure dt_TRUCK_data to field symbol.
      ASSIGN COMPONENT dl_index OF STRUCTURE dt_final TO <fs>.
      IF sy-subrc = 0.
        MOVE dt_intern-value TO <fs>.
      ENDIF.
      AT END OF row.
        APPEND dt_final.
        CLEAR dt_final.
      ENDAT.
    ENDLOOP.
  ENDIF.

Hope this will help you.

Thanks&Regards,

Siri.

Former Member · ‎2005 Dec 30

CREATE EXCEL SHEET 'TEST.XLS' IN FORMAT

PAYROLL EMPNO WAGE NUMBER UNITS DATE DATE

AND THEN ITAB ALSO IN SAME SEQUENCE AND FORMAT.

THEN UPDATE YOUR DATABSE TABLE USING THIS ITERNAL TABLE...I DON'T UNDERSTAND WHERE ARE YOU MISSING.

TRY THIS AND TELL ME IF U STILL HAVE PROBLEM..

REGARDS,

VIJAY RAHEJA

Former Member · ‎2005 Dec 30

hi,

Check the following code.

structure of the xls file is

9 8

7 6

5 4

-

REPORT ZTEST99.

parameters: p_file like RLGRAP-FILENAME.

data: mytab like ALSMEX_TABLINE occurs 0 with header line.

data: begin of newtab occurs 0,

first type c,

secnd type c,

end of newtab.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = 1

i_begin_row = 1

i_end_col = 2

i_end_row = 3

tables

intern = mytab

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 mytab.

*write 😕 mytab-row, mytab-col, mytab-value.

*endloop.

loop at mytab.

at new row.

read table mytab index sy-tabix.

row = mytab-row.

col = mytab-col.

endat.

case mytab-col.

when '0001'.

newtab-first = mytab-value.

when '0002'.

newtab-secnd = mytab-value.

endcase.

at end of row.

append newtab.

endat.

endloop.

loop at newtab.

write 😕 newtab-first, newtab-secnd.

endloop.

Regards,

Sailaja.

Former Member · ‎2005 Dec 30

hi

try this if it doesnt work

give me your mail-id i will send you.

Program code :

-

REPORT Excel.

TABLES:

sflight.

header data................................

DATA :

header1 LIKE gxxlt_p-text VALUE 'Suresh',

header2 LIKE gxxlt_p-text VALUE 'Excel sheet'.

Internal table for holding the SFLIGHT data

DATA BEGIN OF t_sflight OCCURS 0.

INCLUDE STRUCTURE sflight.

DATA END OF t_sflight.

Internal table for holding the horizontal key.

DATA BEGIN OF t_hkey OCCURS 0.

INCLUDE STRUCTURE gxxlt_h.

DATA END OF t_hkey .

Internal table for holding the vertical key.

DATA BEGIN OF t_vkey OCCURS 0.

INCLUDE STRUCTURE gxxlt_v.

DATA END OF t_vkey .

Internal table for holding the online text....

DATA BEGIN OF t_online OCCURS 0.

INCLUDE STRUCTURE gxxlt_o.

DATA END OF t_online.

Internal table to hold print text.............

DATA BEGIN OF t_print OCCURS 0.

INCLUDE STRUCTURE gxxlt_p.

DATA END OF t_print.

Internal table to hold SEMA data..............

DATA BEGIN OF t_sema OCCURS 0.

INCLUDE STRUCTURE gxxlt_s.

DATA END OF t_sema.

Retreiving data from sflight.

SELECT * FROM sflight

INTO TABLE t_sflight.

Text which will be displayed online is declared here....

t_online-line_no = '1'.

t_online-info_name = 'Created by'.

t_online-info_value = 'SURESH KUMAR PARVATHANENI'.

APPEND t_online.

Text which will be printed out..........................

t_print-hf = 'H'.

t_print-lcr = 'L'.

t_print-line_no = '1'.

t_print-text = 'This is the header'.

APPEND t_print.

t_print-hf = 'F'.

t_print-lcr = 'C'.

t_print-line_no = '1'.

t_print-text = 'This is the footer'.

APPEND t_print.

Defining the vertical key columns.......

t_vkey-col_no = '1'.

t_vkey-col_name = 'MANDT'.

APPEND t_vkey.

t_vkey-col_no = '2'.

t_vkey-col_name = 'CARRID'.

APPEND t_vkey.

t_vkey-col_no = '3'.

t_vkey-col_name = 'CONNID'.

APPEND t_vkey.

t_vkey-col_no = '4'.

t_vkey-col_name = 'FLDATE'.

APPEND t_vkey.

Header text for the data columns................

t_hkey-row_no = '1'.

t_hkey-col_no = 1.

t_hkey-col_name = 'PRICE'.

APPEND t_hkey.

t_hkey-col_no = 2.

t_hkey-col_name = 'CURRENCY'.

APPEND t_hkey.

t_hkey-col_no = 3.

t_hkey-col_name = 'PLANETYPE'.

APPEND t_hkey.

t_hkey-col_no = 4.

t_hkey-col_name = 'SEATSMAX'.

APPEND t_hkey.

t_hkey-col_no = 5.

t_hkey-col_name = 'SEATSOCC'.

APPEND t_hkey.

t_hkey-col_no = 6.

t_hkey-col_name = 'PAYMENTSUM'.

APPEND t_hkey.

populating the SEMA data..........................

t_sema-col_no = 1.

t_sema-col_typ = 'STR'.

t_sema-col_ops = 'DFT'.

APPEND t_sema.

t_sema-col_no = 2.

APPEND t_sema.

t_sema-col_no = 3.

APPEND t_sema.

t_sema-col_no = 4.

APPEND t_sema.

t_sema-col_no = 5.

APPEND t_sema.

t_sema-col_no = 6.

APPEND t_sema.

t_sema-col_no = 7.

APPEND t_sema.

t_sema-col_no = 8.

APPEND t_sema.

t_sema-col_no = 9.

APPEND t_sema.

t_sema-col_no = 10.

t_sema-col_typ = 'NUM'.

t_sema-col_ops = 'ADD'.

APPEND t_sema.

CALL FUNCTION 'XXL_FULL_API'

EXPORTING

DATA_ENDING_AT = 54
DATA_STARTING_AT = 5

filename = 'TESTFILE'

header_1 = header1

header_2 = header2

no_dialog = 'X'

no_start = ' '

n_att_cols = 6

n_hrz_keys = 1

n_vrt_keys = 4

sema_type = 'X'

SO_TITLE = ' '

TABLES

data = t_sflight

hkey = t_hkey

online_text = t_online

print_text = t_print

sema = t_sema

vkey = t_vkey

EXCEPTIONS

cancelled_by_user = 1

data_too_big = 2

dim_mismatch_data = 3

dim_mismatch_sema = 4

dim_mismatch_vkey = 5

error_in_hkey = 6

error_in_sema = 7

file_open_error = 8

file_write_error = 9

inv_data_range = 10

inv_winsys = 11

inv_xxl = 12

OTHERS = 13

.

IF sy-subrc <> 0.

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

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

ENDIF.

regs

Arun

Message was edited by: arun a v

Former Member · ‎2005 Dec 30

this must work

REPORT ZZBGS010 .

----

Example: Interface between Microsoft Excel and ABAP/4 with up- and *
downloading of data plus executing Microsoft Excel. *

----

TABLES: USR04.

DATA: SIZE TYPE I.

DATA: BEGIN OF USER OCCURS 100.

INCLUDE STRUCTURE USR04.

DATA: END OF USER.

---------------------------------------------------------------------*
Example: Select some data into an internal table. *
---------------------------------------------------------------------*

SELECT * FROM USR04 INTO TABLE USER .

---------------------------------------------------------------------*
Example: Downloading data in Microsoft Excel Format with automatic *
prompt popup dialog. *
---------------------------------------------------------------------*

CALL FUNCTION 'DOWNLOAD'

EXPORTING

FILENAME = 'C:\tmp\SAPEXL1.XLS'

FILETYPE = 'WK1' "ASC, WK1, DBF, DAT, bin

MODE = ' ' "Mode ' ' = Rewrite Mode 'A' = Appending

TABLES

DATA_TAB = USER.

---------------------------------------------------------------------*
Example: Downloading data in Microsoft Excel Format without automatic*
prompt popup. *
---------------------------------------------------------------------*

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

FILENAME = 'C:\tmp\SAPEXL2.XLS'

FILETYPE = 'WK1' "ASC, WK1, DBF, DAT, bin

MODE = ' ' "Mode ' ' = Rewrite Mode 'A' = Appending

TABLES

DATA_TAB = USER.

CLEAR USER. "Clear buffer

REFRESH USER. "Refresh, empty internal table

----

Example: Uploading Microsoft Excel to ABAP/4 internal table. *

----

CALL FUNCTION 'UPLOAD'

EXPORTING

FILENAME = 'C:\tmp\SAPEXL.prn'

FILETYPE = 'ASC'

IMPORTING

FILESIZE = SIZE

TABLES

DATA_TAB = USER.

----

Example: Starting Microsoft Excel and load sheet. *

----

CALL FUNCTION 'WS_EXECUTE'

EXPORTING

COMMANDLINE = 'C:\tmp\SAPEXL1.XLS'

PROGRAM = 'F:\APPL\WINDOWS\EXCEL5DK\EXCEL.EXE'

.

----

Example: Starting Microsoft Excel and load internal table as sheet *

----

CALL FUNCTION 'WS_EXCEL'

EXPORTING

FILENAME = 'C:\tmp\SAPEXL.XLS'

SYNCHRON = ' '

TABLES

DATA = USER.

regs

Arun

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Excel to Internal Table