Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

[Former Member]

Hi Friends,

I am Working on this Function Module ALSM_EXCEL_TO_INTERNAL_TABLE. I am using Excel File here and am getting the data into internal table.

This is my Excel file consists of headers and values :

ANLKL BUKRS NASSETS

1000 0005 1

1000 0005 1

after using this function module i am getting like this

ANLKL

BUKRS

NASSETS

1000

0005

1

1000

0005

1

here i want to know dynamically how many colums we are uploading from excel sheet..so that i can split the headings into one internal table and valus into another internal table like this

Internal Table One

ANLKL

BUKRS

NASSETS

Internal Table Two

1000

0005

1

1000

0005

1

any one pls tell me how to get the solution..

Thanks ,

Laxmi.

[Former Member]

Try this.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = pa_path
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = 15
      i_end_row               = 65535
    TABLES
      intern                  = t_itab
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc NE 0.
    MESSAGE e997(yaamessages) WITH text-030 .
  ENDIF.

  LOOP AT t_itab .
    CASE t_itab-col.
	WHEN '1'.
	*if t_itab-row EQ 1*.
                 wa_input2-fname = t_itab-value.
	else.
	wa_input-anln1 = t_itab-value.
	endif.
	WHEN '2'.
	same as above 
	.........
  WHEN OTHERS.
    ENDCASE.
    AT END OF row.
	*if t_itab-row EQ 1*.
      APPEND wa_input TO t_input.
	else.
      APPEND wa_input2 TO t_input2.
         endif.
      CLEAR wa_input,wa_input2.
      cnt_records = cnt_records + 1.
    ENDAT.
  ENDLOOP.

Rhea.

Edited by: rhea on Apr 9, 2009 1:18 PM

[Former Member]

DATA l_count TYPE sy-tabix.

   CONSTANTS: lc_begin_col TYPE i VALUE '1',
              lc_begin_row TYPE i VALUE '2',
              lc_end_col   TYPE i VALUE '2',
              lc_end_row   TYPE i VALUE '3000'.

  CLEAR p_i_excel_data. REFRESH p_i_excel_data.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = pa_path
     EXPORTING
       filename                = p_p_file
       i_begin_col             = lc_begin_col
       i_begin_row             = lc_begin_row
       i_end_col               = lc_end_col
       i_end_row               = lc_end_row
     TABLES
       intern                  = i_data
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc NE 0.
    MESSAGE e997(yaamessages) WITH text-030 .
  ENDIF.
* Error in file upload
   IF sy-subrc NE 0 .
     MESSAGE text-006 TYPE 'E'.
     EXIT.
   ENDIF.
   IF i_data[] IS INITIAL .
     MESSAGE text-007 TYPE 'E'.
     EXIT.
   ELSE.
     SORT i_data BY row col .
* Loop to fill data in Internal Table
     LOOP AT i_data .
       MOVE i_data-col TO l_count .
       ASSIGN COMPONENT l_count OF STRUCTURE p_i_excel_data TO <fs_source> .
       MOVE i_data-value TO <fs_source> .
       AT END OF row .
* Append data into internal table
         APPEND p_i_excel_data.
         CLEAR p_i_excel_data.
       ENDAT .
     ENDLOOP .
   ENDIF .

[Former Member]

DATA : ITAB2 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE,

ROW LIKE ALSMEX_TABLINE-ROW.

DATA : BEGIN OF IT_UPLOAD OCCURS 0,

SBU(3),

MATNR LIKE MARA-MATNR,

WERKS LIKE MARC-WERKS,

END OF IT_UPLOAD.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = PFNAME

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 18

I_END_ROW = 65000

TABLES

INTERN = ITAB2

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

LOOP AT ITAB2.

IF ITAB2-ROW <> ROW.

APPEND IT_POW_MAT.

CLEAR IT_POW_MAT.

ENDIF.

CASE ITAB2-COL.

WHEN '1'.

IT_POW_MAT-SBU = ITAB2-VALUE.

WHEN '2'.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = ITAB2-VALUE

IMPORTING

OUTPUT = IT_POW_MAT-MATNR.

WHEN '3'.

IT_POW_MAT-WERKS = ITAB2-VALUE.

ENDCASE.

ROW = ITAB2-ROW.

ENDLOOP.

APPEND IT_POW_MAT.

CLEAR IT_POW_MAT.

[former_member537489]

Hello Laxmi,

after executing the function module you should get the internal table below:

ROW COL VALUE

0001 0001 header1

0001 0002 header2

0001 0003 header3

0002 0001 1

0002 0002 2

0002 0003 3

0003 0001 11

0003 0002 22

0003 0003 33

as the structure of the internal table : ALSMEX_TABLINE contains row and column position.

So if you count the number of lines with ROW = 1, you should have the number of columns you need.

Please let me know if that helps.

Roxani

[Former Member]

Hi,

The FM ALSM_EXCEL_TO_INTERNAL_TABLE is obselete.

You can use FM TEXT_CONVERT_XLS_TO_SAP. Here make sure that all fields are char type.

Hope it helps,

RJ

[Former Member]

Hi,

The FM ALSM_EXCEL_TO_INTERNAL_TABLE is obselete.

Try FM TEXT_CONVERT_XLS_TO_SAP instead.

You can have the header in a seperate internal table and add it during the output download.

Hope it helps,

RJ

[Former Member]

Hi,

Use this function module

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.

Rgds

siva

[Former Member]

REPORT Zsample.

TYPE-POOLS TRUXS.

data: gi_lfa1 like lfa1 occurs 0 with header line.

data ITAB LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA IT_RAW TYPE TRUXS_T_TEXT_DATA.

DATA: begin of GI_EXEL OCCURS 0,

lifnr like lfb1-lifnr, "Vendor

bukrs like lfb1-bukrs, "Company Code

ekorg like lfm1-ekorg, "Purch. Organization

end of gi_exl.

DATA IT_RAW TYPE TRUXS_T_TEXT_DATA.

DATA : ITAB LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA ROW LIKE ALSMEX_TABLINE-ROW.

INITIALIZATION.

SELECTION-SCREEN: BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-110 .

SELECT-OPTIONS : RECORDS FOR COUNT.

PARAMETER : PFNAME1 LIKE RLGRAP-FILENAME OBLIGATORY.

SELECTION-SCREEN:SKIP.

SELECTION-SCREEN:SKIP.

SELECTION-SCREEN : END OF BLOCK B1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR PFNAME1.

PERFORM SEARCH1.

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = PFNAME1

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 54

I_END_ROW = 65000

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.

ROW = 1.

LOOP AT ITAB.

IF ITAB-ROW NE ROW.

APPEND GI_EXEL.

CLEAR GI_EXEL.

ENDIF.

CASE ITAB-COL.

WHEN '1'.

GI_EXEL-lifnr = itab-value.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = gi_exel-lifnr

IMPORTING

output = gi_exel-lifnr.

WHEN '2'.

GI_EXEL-bukrs = itab-value.

WHEN '3'.

GI_EXEL-ekorg = itab-value.

ENDCASE.

ROW = ITAB-ROW.

ENDLOOP.

APPEND GI_EXEL.

CLEAR GI_EXEL.

IF NOT GI_EXEL[] IS INITIAL.

SORT GI_EXEL BY LIFNR bukrs ekorg .

.

DELETE ADJACENT DUPLICATES FROM GI_EXEL COMPARING LIFNR bukrs ekorg

ktokk .

FORM SEARCH1.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

STATIC = 'X'

CHANGING

FILE_NAME = PFNAME1.

ENDFORM. " search

~linganna

Edited by: katigiri linganna on Apr 9, 2009 1:33 PM

Edited by: katigiri linganna on Apr 9, 2009 1:33 PM

[Former Member]

hi

Edited by: katigiri linganna on Apr 9, 2009 1:32 PM