topic Re: Excel to internal table formatting errors. in Application Development and Automation Discussions

Excel to internal table formatting errors.

Former Member — Sat, 03 Oct 2009 06:25:02 GMT

Hi,

I have a requirement where I need to take data from an excel doc and put it into an internal table.

The excel doc has got only one active sheet and one column . I am using the methods FILE_OPEN_DIALOG and GUI_UPLOAD for the same. However the data that is getting stored in the internal table is some garbage data.

I am passing the following parameters in GUI_UPLOAD:-

CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD

EXPORTING

FILENAME = p_file

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = SPACE
CODEPAGE = SPACE
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
VIRUS_SCAN_PROFILE =
IMPORTING
FILELENGTH =
HEADER =

CHANGING

DATA_TAB = lt_itab

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
NOT_SUPPORTED_BY_GUI = 17
ERROR_NO_GUI = 18
others = 19

The excel file that I am using is a tab delimited one.I am working in CRM 6.0 so as such cannot use the function module 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

Can anyone provide some idea how to go sbout this?

Thanks,

Samrat.

Re: Excel to internal table formatting errors.

Former Member — Sat, 03 Oct 2009 06:27:01 GMT

Use this Fm to upload exel to internal table

KCD_EXCEL_OLE_TO_INT_CONVERT

кu03B1ятu03B9к

Edited by: Kartik Tarla on Oct 3, 2009 11:58 AM

Re: Excel to internal table formatting errors.

faisalatsap — Sat, 03 Oct 2009 06:47:03 GMT

Hi, Dutta

Please Check this -->

Faisal

Re: Excel to internal table formatting errors.

Former Member — Sat, 03 Oct 2009 06:50:18 GMT

try other FMs.

http://www.sdn.sap.com/irj/scn/advancedsearch?query=exceltointernal+table

ALSM_EXCEL_TO_INTERNAL_TABLE

Re: Excel to internal table formatting errors.

Former Member — Sat, 03 Oct 2009 07:24:33 GMT

Hi,

Use this FM-

CALL FUNCTION 'FILE_READ_AND_CONVERT_SAP_DATA'

EXPORTING

I_FILENAME = PATH

I_SERVERTYP = 'PRS'

I_FILEFORMAT = 'XLS'

I_FIELD_SEPERATOR =

I_LINE_HEADER = 'X'

IMPORTING
E_BIN_FILELENGTH =

TABLES

I_TAB_RECEIVER = IUPLOAD

Abhishek

Re: Excel to internal table formatting errors.

Clemenss — Sat, 03 Oct 2009 17:08:58 GMT

Hi,

what is the structure of your DATA_TAB and what is the data type in your excel.

I just tried whatr you described and everything works fine, even with Microsoft Office Excel 2007.

Regards,

Clemens

Re: Excel to internal table formatting errors.

Former Member — Sun, 04 Oct 2009 08:08:59 GMT

Hi Clemens,

Thanks for your response. Below is the code that I am using:-

types: begin of t_itab,
bpid(10) type c, 
 end of t_itab. 
data: lt_file_table type FILETABLE,
      ls_file_table type file_table,
      ls_window_title type string value 'Data Conversion',
      lv_rc type i,
      lt_itab type table of t_itab,
      ls_itab type t_itab.

PARAMETERS: p_file type string.
at SELECTION-SCREEN on VALUE-REQUEST FOR p_file.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
  EXPORTING
     WINDOW_TITLE            = ls_window_title
*    DEFAULT_EXTENSION       =
*    DEFAULT_FILENAME        =
*    FILE_FILTER             =
*    WITH_ENCODING           =
*    INITIAL_DIRECTORY       =
*    MULTISELECTION          =
  CHANGING
    FILE_TABLE              = LT_FILE_TABLE
    RC                      = lv_RC
*    USER_ACTION             =
*    FILE_ENCODING           =
*  EXCEPTIONS
*    FILE_OPEN_DIALOG_FAILED = 1
*    CNTL_ERROR              = 2
*    ERROR_NO_GUI            = 3
*    NOT_SUPPORTED_BY_GUI    = 4
*    others                  = 5
        .
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

read table lt_file_table into ls_file_table index 1.
if sy-subrc = 0.
 p_file = ls_file_table-FILENAME.
endif.
START-OF-SELECTION.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD
  EXPORTING
     FILENAME                = p_file
     FILETYPE                = 'ASC'
     HAS_FIELD_SEPARATOR     = 'X'
*    HEADER_LENGTH           = 0
*    READ_BY_LINE            = 'X'
*    DAT_MODE                = SPACE
*    CODEPAGE                = SPACE
*    IGNORE_CERR             = ABAP_TRUE
*    REPLACEMENT             = '#'
*    VIRUS_SCAN_PROFILE      =
*  IMPORTING
*    FILELENGTH              =
*    HEADER                  =
  CHANGING
    DATA_TAB                = lt_itab
*  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
*    NOT_SUPPORTED_BY_GUI    = 17
*    ERROR_NO_GUI            = 18
*    others                  = 19
        .
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,

Samrat.

Edited by: SAMRAT DUTTA on Oct 4, 2009 10:09 AM

Re: Excel to internal table formatting errors.

faisalatsap — Sun, 04 Oct 2009 08:11:43 GMT

Hi, Dutta

Please Check this one too -->

Faisal

Re: Excel to internal table formatting errors.

Clemenss — Sun, 04 Oct 2009 08:58:05 GMT

Hi Samrat,

please try again, use the Preview button before posting, and finally: First use google before annoying people here, thank you.

BTW: I used the SE37 Test function to try the upload, may be that's a way for you to try.

Regards,

Clemens

Re: Excel to internal table formatting errors.

venkat_o — Sun, 04 Oct 2009 09:31:19 GMT

Hi Samrat, Try this way to upload excel to Internal table .\


REPORT ztest_program.
TYPES: BEGIN OF t_itab,
        bpid TYPE string,
       END OF t_itab.
DATA:lt_file_table   TYPE filetable,
     ls_file_table   TYPE file_table,
     ls_window_title TYPE string VALUE 'Data Conversion',
     lv_rc           TYPE i,
     lt_itab         TYPE TABLE OF t_itab,
     ls_itab         TYPE t_itab.

PARAMETERS: p_file   TYPE string.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title = ls_window_title
    CHANGING
      file_table   = lt_file_table
      rc           = lv_rc.
  READ TABLE lt_file_table INTO ls_file_table INDEX 1.
  IF sy-subrc = 0.
    p_file = ls_file_table-filename.
  ENDIF.

START-OF-SELECTION.
  TYPE-POOLS:TRUXS.
  DATA:it_text    TYPE TRUXS_T_TEXT_DATA.
  DATA:i_filename LIKE  rlgrap-filename.
  i_filename = p_file.
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_tab_raw_data       = it_text
      i_filename           = i_filename
    TABLES
      i_tab_converted_data = lt_itab
    EXCEPTIONS
      conversion_failed    = 1
      OTHERS               = 2.
  IF sy-subrc NE 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ELSE.
    LOOP AT lt_itab INTO ls_itab.
      WRITE:/ ls_itab-bpid.
    ENDLOOP.
  ENDIF.

Thanks Venkat.O

Re: Excel to internal table formatting errors.

aris_hidalgo — Sun, 04 Oct 2009 10:02:58 GMT

Hi Samrat,

You may try reading about desktop office integration or better yet check out the demos in transaction DWDM.

Hope it helps...

Re: Excel to internal table formatting errors.

Clemenss — Sun, 04 Oct 2009 10:30:03 GMT

Hi,

sometimes it really makes sense to read the question:

"The excel file that I am using is a tab delimited one"

any clues?

I tried creating an excel and saved it as tab delimited text.

Regards,

Clemens