topic Re: excel to internal table in Application Development and Automation Discussions

excel to internal table

Former Member — Tue, 09 Sep 2008 06:12:04 GMT

I am using the below code to upload the excel data into internal table but only one record is stored in internal table but excel shheet contains more

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_raw
i_filename = p_file
TABLES
i_tab_converted_data = it_datatab[]
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.

what could be the reason is there any other FM to do this

thanks

sai

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 06:13:43 GMT

Hi,

Use the following FM.

Excel to Internal table ---ALSM_EXCEL_TO_INTERNAL_TABLE

Internal table to Excel --- GUI_DOWNLOAD.

Regards,

Harish

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 06:14:16 GMT

Hi,

Try removing the Header = 'X'.

or use FM GUI_DOWNLOAD...

Regards,

Kunjal

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 06:15:01 GMT

Try like this

call method cl_gui_frontend_services=>gui_upload

exporting

filename = l_fname

filetype = 'ASC'

has_field_separator = 'X'

header_length = 0

importing

filelength = filelength

changing

data_tab = lt_input[]

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.

Re: excel to internal table

GauthamV — Tue, 09 Sep 2008 06:15:01 GMT

hi,

use this.

data : t_itab like ALSMEX_TABLINE occurs 0 with header line .

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = 'C:\book1.xls'

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 1

I_END_ROW = 278

TABLES

INTERN = t_itab

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 06:17:38 GMT

TYPE-POOLS: truxs .
PARAMETER: pfile LIKE rlgrap-filename." OBLIGATORY.
DATA: it_raw TYPE truxs_t_text_data.
DATA: BEGIN OF record,
* data element: BLDAT
        bldat_001(010),   "Invoice Date
* data element: BLART
        blart_002(002),   " Doc Type DR or DG if credit
* data element: BUKRS
        bukrs_003(004),   " Company Code
.
.
.

DATA: BEGIN OF it_excel OCCURS 0,
* data element: XBLNR1
      xblnr_007(016),   "Invoice Number
* data element: BLDAT
      bldat_001(010),   "Invoice Date
* data element: WRBTR
      wrbtr_012(016),   "Amount
.
.
.
 CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR          =
      i_line_header              = 'X'
      i_tab_raw_data             = it_raw
      i_filename                 = pfile
    TABLES
      i_tab_converted_data       = it_excel[]
*   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.

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 06:20:16 GMT

hi,

dont give as 'it_datatab[] ' giv as 'it_datatab'...

use this way

TYPE-POOLS : TRUX.

DATA : IT_TYPE TYPE TRUXS_T_TEXT_DATA.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR = 'X'

I_LINE_HEADER = 'X'

I_TAB_RAW_DATA = IT_TYPE

I_FILENAME = P_FPATH

TABLES

I_TAB_CONVERTED_DATA = T_0585

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.

Edited by: Thyagu on Sep 9, 2008 8:20 AM

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 07:15:49 GMT

Hi Guatham,

How to loop the table and get the data while using ALSM Function module

if you provide any sample code it is helpfull

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 07:20:46 GMT

No Need of loop.

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 07:22:32 GMT

Hi sai,

your problem may be occuring bcoz of the header usage in your FM.

instead of specifying itab[], try using itab without square brackets.

I hope your problem would be solved.

Regards,

Kiran

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 07:24:38 GMT

hii

you can loop like below code

*Upload data from Excel sheet to internal table.
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_pfile
      i_begin_col             = 1
      i_begin_row             = 2
      i_end_col               = 13
      i_end_row               = 8
    TABLES
      intern                  = it_excel
    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.


*Populate data to internal tables and structures

  SORT it_excel BY row col.

  LOOP AT it_excel INTO ls_excel.

    CASE ls_excel-col.
      WHEN 1.
        ls_data-rec_no = ls_excel-value.
        IF ls_data-rec_no NE w_docno.
          WRITE:
            'You Have Entered wrong Document Number'.
        ELSE.
          WRITE:
            'Document Number Same'.
        ENDIF.                         " IF ls_data-rec_no NE w_docno.
      WHEN 2.
        ls_data-doc_type = ls_excel-value.
      WHEN 3.
        ls_data-doc_part = ls_excel-value.
      WHEN 4.
        ls_data-doc_ver = ls_excel-value.
      WHEN 5.
        ls_data-application = ls_excel-value.
      WHEN 6.
        ls_data-data_car = ls_excel-value.
      WHEN 7.
        ls_data-file_path = ls_excel-value.
      WHEN 8.
        ls_data-matnr = ls_excel-value.

    ENDCASE.                           " CASE ls_excel-col.

regards

twinkal

Re: excel to internal table

Former Member — Tue, 09 Sep 2008 07:32:39 GMT



TYPE-POOLS: truxs.
TYPES:
  BEGIN OF ty_Line,
    vbeln LIKE vbap-vbeln,
    posnr LIKE vbap-posnr,
  END OF ty_Line.
  ty_Lines TYPE STANDARD TABLE of ty_Line WITH DEFAULT KEY.
DATA: itab   TYPE ty_Lines.
DATA: itab1  TYPE truxs_t_text_data.

SELECT
  vbeln
  posnr
  UP TO 10 ROWS
  FROM vbap
  INTO TABLE itab.

CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'
  EXPORTING
    i_field_seperator    = ';'
  TABLES
    i_tab_sap_data       = itab
  CHANGING
    i_tab_converted_data = itab1
  EXCEPTIONS
    conversion_failed    = 1
    OTHERS               = 2.
IF sy-subrc &lt;&gt; 0.
  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
  WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
    filename = 'C:TEMP	est.txt'
  TABLES
    data_tab = itab1
  EXCEPTIONS
    OTHERS   = 1.