how can i load excel file into itab?

[Former Member]

how can i load excel file into itab?

[shishupalreddy]

Hi,

Use ALSMEXCEL in S37 and search for the Function module

also u can search for KCDCELLEXCEL*

Regards,

[shishupalreddy]

Hi ,

ALSM_EXCEL_TO_ITAB is the correct FM

Actually it should work , because there is an OSS note 933420 which tells how to handle such situtation.

Also try the options mentioned by others of converting it to CSV file.

Regards

[Former Member]

hi.

here is sample code to upload db table. instead of db table use this to update itab.

REPORT  ZTN_UPLOAD_FILE_IN_DB.
tables ztn_record.

data : begin of itab occurs 100,

 row(200) type c,

end of itab.
data : mfilename(128) type c , count type i.

SELECTION-SCREEN begin of BLOCK abc WITH FRAME TITLE text-001.
PARAMETERS fl_name(128) DEFAULT 'C:\Documents and Settings\tahir.naqqash\Desktop\22.csv'.
  SELECTION-SCREEN END OF BLOCK abc.

  AT SELECTION-SCREEN on VALUE-REQUEST FOR fl_name.
    PERFORM get_file.

start-of-selection.

perform upload_itab.

clear ztn_record.
 loop at itab.
 split itab-row at ',' INTO ztn_record-mandt
                            ztn_record-empid
                            ztn_record-empname
                            ztn_record-empphno
                            ztn_record-newfield.


 insert ztn_record.

    if sy-subrc = 0 .
      commit work.
      count = count + 1.
    else.
      write: / , 'Insert Failed for',ztn_record-empid ,ztn_record-empname ,ztn_record-empphno , ztn_record-newfield .
    endif.

  endloop.

  write: count , 'Records were inserted'.


    form get_file.
      CALL FUNCTION 'WS_FILENAME_GET'
       EXPORTING
*         DEF_FILENAME           = ' '
*         DEF_PATH               = ' '
         MASK                   = ',CSV file,*.CSV,*.TXT.'
         MODE                   = 'O'
*         TITLE                  = ' '
       IMPORTING
         FILENAME               = mfilename
*         RC                     =
       EXCEPTIONS
         INV_WINSYS             = 1
         NO_BATCH               = 2
         SELECTION_CANCEL       = 3
         SELECTION_ERROR        = 4
         OTHERS                 = 5
                .
      IF sy-subrc <> 0.
 MESSAGE 'File not found '  TYPE 'I'.
      else.
        MOVE mfilename to fl_name.
      ENDIF.

      ENDFORM.


   form upload_itab.
     CALL FUNCTION 'WS_UPLOAD'
      EXPORTING
*        CODEPAGE                      = ' '
        FILENAME                      = fl_name
        FILETYPE                      = 'DAT'
*        HEADLEN                       = ' '
*        LINE_EXIT                     = ' '
*        TRUNCLEN                      = ' '
*        USER_FORM                     = ' '
*        USER_PROG                     = ' '
*        DAT_D_FORMAT                  = ' '
*      IMPORTING
*        FILELENGTH                    =
       TABLES
         data_tab                      = itab
*      EXCEPTIONS
*        CONVERSION_ERROR              = 1
*        FILE_OPEN_ERROR               = 2
*        FILE_READ_ERROR               = 3
*        INVALID_TYPE                  = 4
*        NO_BATCH                      = 5
*        UNKNOWN_ERROR                 = 6
*        INVALID_TABLE_WIDTH           = 7
*        GUI_REFUSE_FILETRANSFER       = 8
*        CUSTOMER_ERROR                = 9
*        NO_AUTHORITY                  = 10
*        OTHERS                        = 11
               .
     IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
     ENDIF.



     ENDFORM.

[faisal_altaf2]

Hi,

Please Use the following FM

ALSM_EXCEL_TO_INTERNAL_TABLE

Hope will help you.

Kind Regards,

Faisal

[faisal_altaf2]

>

> how can i load excel file into itab?

Hi, Ami

Test the following Sample Code To test it you must need a 'test.xls' on your local Drive C. this file must be in the following format

a1 to a10

1

2

3

4

5

6

7

8

9

10

b1 to b10

a

b

c

d

e

f

g

h

i

j

TYPES: BEGIN OF t_fields,
  f1 TYPE i,
  f2(10),
END OF t_fields.

DATA: it_get_data LIKE STANDARD TABLE OF alsmex_tabline WITH HEADER LINE,
      it_fields TYPE STANDARD TABLE OF t_fields WITH HEADER LINE,
      file TYPE rlgrap-filename.

file = 'c:\test.xls'.
CONDENSE file.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                = file
    i_begin_col             = '1'
    i_begin_row             = '1'
    i_end_col               = '2'
    i_end_row               = '10'
  TABLES
    intern                  = it_get_data
  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 it_get_data INTO it_get_data.

  CASE it_get_data-col.
    WHEN '0001'.
      it_fields-f1 = it_get_data-value.
    WHEN '0002'.
      it_fields-f2 = it_get_data-value.
    WHEN OTHERS.
  ENDCASE.

  AT END OF row.
    APPEND it_fields TO it_fields.
    CLEAR it_fields.
  ENDAT.

ENDLOOP.

LOOP AT it_fields.

  WRITE: / it_fields-f1, it_fields-f2.

ENDLOOP.

Please Replay if any Issue,

Kind Regards,

Faisal

[I355602]

Hi,

Use the function module 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

The itab to store the excel data can be of structure 'KCDE_CELLS'. p_xlfile is a parameter that contain the excel file path.

The itab will store the values using the row and column number.like (1,1),(2,1).....

if any field in the excel file contains nothing,then that colmn and row number will not be stored in the itab.

Then field which has value in the excel file,only contains in the internal table.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
 EXPORTING
   filename = p_xlfile
   i_begin_col = 1
   i_begin_row = 1
   i_end_col = 11
   i_end_row = p_endrow
 TABLES
   intern = int_excel_data
 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.

Hope this helps you.

Regards,

Tarun

[Former Member]

Hi,

Check the below Thread

Hope this helps.

Regards,

Anki Reddy

[Former Member]

Hi Ami,

Check this code, it_excel here is your itab.

DATA : tab_record TYPE  alsmex_tabline OCCURS 0 WITH HEADER LINE.
  DATA : l_start_col TYPE i VALUE '2',
         l_start_row TYPE i VALUE '2',
         l_end_col   TYPE i VALUE '256',
         l_end_row   TYPE i VALUE '65536',
         count        TYPE i,
         l_index TYPE i,
         l_ordered_prod TYPE char40.

  FIELD-SYMBOLS : <fs>.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = dataset
      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                  = tab_record
    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.


  IF sy-subrc <> 0.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE : / 'File Error'.
    EXIT.
  ENDIF.

  IF tab_record[] IS INITIAL.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE : / 'No Data Uploaded'.
    EXIT.
  ELSE.
    SORT tab_record BY row col.
    LOOP AT tab_record.
      MOVE tab_record-col TO l_index.
      ASSIGN COMPONENT l_index OF STRUCTURE wa_excel TO <fs>.
      MOVE tab_record-value TO <fs>.

      AT END OF row.
        APPEND wa_excel TO it_excel.
        CLEAR wa_excel.
      ENDAT.
    ENDLOOP.
  ENDIF.

Regards,

Kezia

[Former Member]

Hi,

try this. its working

TYPE-POOLS: truxs.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*    I_FIELD_SEPERATOR          = 'X'
     i_line_header              = 'X'
      i_tab_raw_data             = it_raw
      i_filename                 = 'c:\file.xls'   " file name.......
    TABLES
      i_tab_converted_data       = it_final[]  " internal table name
   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.

Thanks

Arun

[awin_prabhu]

Hi Friend,

Check this simple code for uploading data from excel into internal table and displaying it.

REPORT zdemodownload .

*Types

TYPES: BEGIN OF g_r_mara,

matnr LIKE mara-matnr,

ersda LIKE mara-ersda,

laeda LIKE mara-laeda,

mtart LIKE mara-mtart,

mbrsh LIKE mara-mbrsh,

END OF g_r_mara.

*Data

DATA: g_t_mara1 TYPE TABLE OF g_r_mara,

filename TYPE string,

g_r_wa TYPE g_r_mara.

*Tables

TABLES: mara.

*Selection Screen

SELECT-OPTIONS: s_matnr FOR mara-matnr.

START-OF-SELECTION.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = 'C:\Testing.xls'

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

tables

data_tab = g_t_mara1

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

WRITE: / 'Uploaded data' COLOR = 1.

WRITE:/.

CLEAR g_r_wa.

LOOP AT g_t_mara1 INTO g_r_wa.

WRITE:/ g_r_wa-matnr, g_r_wa-ersda, g_r_wa-laeda, g_r_wa-mtart, g_r_wa-mbrsh.

ENDLOOP.

Have your excel file in C: as Testing.xls

Thanks......

[Former Member]

Hi,

Try this .

TYPES

type_s_raw(4096) TYPE c.

data : fs_tab TYPE type_s_raw,

t_tab LIKE

STANDARD TABLE

OF fs_tab.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

• I_LINE_HEADER =

i_tab_raw_data = t_tab

i_filename = 'File path'

TABLES

i_tab_converted_data = itab(declare)

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.

This works fine.

[Former Member]

Hi

Use the function module 'GUI_UPLOAD'.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = xname

filetype = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

• HEADER_LENGTH = 0

• READ_BY_LINE = 'X'

• DAT_MODE = ' '

• CODEPAGE = ' '

• IGNORE_CERR = ABAP_TRUE

REPLACEMENT = '#'

• CHECK_BOM = ' '

• IMPORTING

• FILELENGTH =

• HEADER =

TABLES

data_tab = g_t_mara1

• 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

.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

"xname" is the path in which the excel sheet is stored.

"g_t_mara1" is the internal table name.

[Former Member]

Hi,

Below code will solve your problem.

data: gi_datatab type standard table of gt_datatab,

gi_raw_data type truxs_t_text_data,

gv_file type rlgrap-filename.

call function 'TEXT_CONVERT_XLS_TO_SAP'

exporting

i_line_header = '1'

i_tab_raw_data = gi_raw_data

i_filename = gv_file

tables

i_tab_converted_data = gi_datatab

exceptions

conversion_failed = 1

others = 2.

hope it will help

Regards

Natasha Garg

[Former Member]

Hi,

Try with this function module:

TEXT_CONVERT_XLS_TO_SAP

ALSM_EXCEL_TO_INTERNAL_TABLE.

Regards,

Rajani