topic Re: converting excel file into internal table in Application Development and Automation Discussions

converting excel file into internal table

Former Member — Fri, 26 Oct 2007 08:38:47 GMT

hi,

please send me the code for uploading the excel file into the internal table

thanks in advance.

Re: converting excel file into internal table

former_member386202 — Fri, 26 Oct 2007 08:41:36 GMT

Hi,

I think its not possible, first u have to convert it into tabl delimitted format text file and then you can upload using FM GUI_UPLOAD.

Regards,

Prashant

Re: converting excel file into internal table

varma_narayana — Fri, 26 Oct 2007 08:47:27 GMT

Hi..

Try the FM KCD_EXCEL_OLE_TO_INT_CONVERT

Sample:

call function 'KCD_EXCEL_OLE_TO_INT_CONVERT'

exporting

filename = i_filename

i_begin_col = l_begin_col

i_begin_row = l_begin_row

i_end_col = l_end_col

i_end_row = l_end_row

tables

intern = xt_intern

exceptions

INCONSISTENT_PARAMETERS = 201

UPLOAD_OLE = 201.

if sy-subrc <> 0.

e_subrc = sy-subrc.

exit.

endif.

reward if Helpful.

Re: converting excel file into internal table

Former Member — Fri, 26 Oct 2007 08:49:22 GMT

Hi,

Use FM ALSM_EXCEL_TO_INTERNAL_TABLE

Check this link for the code.

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

Regards,

Ruthra

Re: converting excel file into internal table

Former Member — Fri, 26 Oct 2007 08:49:52 GMT

CALL FUNCTION 'EXCEL_OLE_STANDARD_DAT'

EXPORTING

FILE_NAME = 'C:\USRtst.XLS'

DATA_SHEET_NAME = 'USERLIST'

TABLES

DATA_TAB = OTAB01_SALES

FIELDNAMES = FLDITAB

EXCEPTIONS

FILE_NOT_EXIST = 1

FILENAME_EXPECTED = 2

COMMUNICATION_ERROR = 3

OLE_OBJECT_METHOD_ERROR = 4

OLE_OBJECT_PROPERTY_ERROR = 5

INVALID_FILENAME = 6

INVALID_PIVOT_FIELDS = 7

DOWNLOAD_PROBLEM = 8

OTHERS = 9.

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. " SHOW123

Re: converting excel file into internal table

Former Member — Fri, 26 Oct 2007 08:50:04 GMT

Hi,

File manuplations...............................

1. At the presentation level:

->GUI_UPLOAD

->GUI_DOWNLOAD

->CL_GUI_FRONTEND

2. At the application server level:

->OPEN DATASET : open a file in the application server for reading or writing.

->READ DATASET : used to read from a file on the application server that has been opened for reading

-> TRANSFER DATASET : writing data to a file.

-> CLOSE DATASET : closes the file

-> DELETE DATASET : delete file

************************************************************************************************

If file is on the local PC,use the function module GUI_UPLOAD to upload it into an internal table by passing the given parameters......

call function 'GUI_UPLOAD'

exporting

filename = p_file

filetype = 'ASC'

has_field_separator = '#'

tables

data_tab = t_data

******************************************************************************

p_file : excel file path.

t_data : internal table

*******************************************************************************

reward points if useful.

regards,

Vinod Samuel.

Re: converting excel file into internal table

Former Member — Fri, 26 Oct 2007 08:51:41 GMT

FILE_READ_AND_CONVERT_SAP_DATA

USe this function Module for your Purpose...

for more info revert back..

if helpful plz rewards,,,,

Re: converting excel file into internal table

Former Member — Fri, 26 Oct 2007 08:54:47 GMT

File transfer from frontend PC to an internal backend table.

A format conversion is possible. The functions are similar to the module WS_UPLOAD, however the data provider is used for the upload and not GMUX.

Example

CALL FUNCTION 'GUI_UPLOAD'

exporting

filetype = 'BIN'

filename = 'C:\DOWNLOAD.BIN'

tables

data_tab = itab.

loads the contents of the file 'C:\DOWNLOAD.BIN' on the frontend PC into the internal backend table itab. The transferred data is not converted.

CALL FUNCTION 'GUI_UPLOAD'

exporting

filetype = 'ASC'

filename = 'C:\DOWNLOAD.TXT'

tables

data_tab = itab.

loads the contents of the file 'C:\DOWNLOAD.TXT' on the frontend PC line by line into the internal backend table itab. The transferred data is converted.

Or else

You can try with this fm also TEXT_CONVERT_XLS_TO_SAP

TYPE-POOLS truxs.
 
DATA: Begin of i_articles occurs 0,
        matnr like mara-matnr,
        flag(6) type c,
      End of i_articles.
 
DATA: v_file type string,
      i_temp type truxs_t_text_data.
 
selection-screen begin of block b1.
parameters: fl_file like rlgrap-filename lower case.
selection-screen end of block b1.
 
FORM UploadData.
 
  v_file = fl_file.
 
  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      FILENAME                      = v_file
     FILETYPE                      = 'ASC'
     HAS_FIELD_SEPARATOR           = 'X'
*     HEADER_LENGTH                 = 0
*     READ_BY_LINE                  = 'X'
     DAT_MODE                      = ' '
*   IMPORTING
*     FILELENGTH                    =
*     HEADER                        =
    TABLES
      DATA_TAB                      = i_temp
   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.
 
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
     I_FIELD_SEPERATOR          = 'X'
*     I_LINE_HEADER              =
      I_TAB_RAW_DATA             = i_temp
      I_FILENAME                 = fl_file
    TABLES
      I_TAB_CONVERTED_DATA       = i_articles
   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.
 
 
ENDFORM.                    " UploadData

Check my previous replies here u can get answer

Regards

Pavan

Re: converting excel file into internal table

Former Member — Fri, 26 Oct 2007 08:58:33 GMT

Hi surya,

Check this simple program...

REPORT zupload_excel_to_itab.

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE rlgrap-filename.

TYPES: BEGIN OF t_datatab,

col1(30) TYPE c,

col2(30) TYPE c,

col3(30) TYPE c,

END OF t_datatab.

DATA: it_datatab type standard table of t_datatab,

wa_datatab type t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

At selection screen

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

***********************************************************************

*START-OF-SELECTION.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = it_datatab[] "ACTUAL DATA

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.

***********************************************************************

END-OF-SELECTION.

END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3.

ENDLOOP.