Excel to Internal Table

[Former Member]

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]

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]

Hi Chandra

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

Regards

Praveen

[Former Member]

can u paste ur code

[Former Member]

hi use

Function module

ALSM_EXCEL_TO_INTERNAL_TABLE

regards

vijay

[Former Member]

Hi,

Check this link

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

Regards,

Sakthi.

[Former Member]

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]

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]

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]

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]

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]

Hi Arun

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

Regards

Praveen

[Former Member]

Hi Praveen,

Best way is use the func.<b>'ALSM_EXCEL_TO_INTERNAL_TABLE'</b>

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]

CREATE EXCEL SHEET <b>'TEST.XLS'</b> IN FORMAT

<b>PAYROLL EMPNO WAGE NUMBER UNITS DATE DATE</b>

AND THEN <b>ITAB</b> 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]

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]

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]

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