topic Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE in Application Development and Automation Discussions

Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Thu, 09 Apr 2009 11:09:50 GMT

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

after using this function module i am getting like this

ANLKL

BUKRS

NASSETS

1000

0005

1000

0005

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

1000

0005

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

Thanks ,

Laxmi.

Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Thu, 09 Apr 2009 11:17:40 GMT

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

Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Thu, 09 Apr 2009 11:21:21 GMT


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 .

Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Thu, 09 Apr 2009 11:22:49 GMT

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.

Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

former_member537489 — Thu, 09 Apr 2009 11:24:36 GMT

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

Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Thu, 09 Apr 2009 11:24:55 GMT

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,

Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Thu, 09 Apr 2009 11:27:41 GMT

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,

Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Thu, 09 Apr 2009 11:30:03 GMT

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

Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Thu, 09 Apr 2009 11:31:25 GMT

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 : 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

Re: Doubt Regarding Function Module ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Thu, 09 Apr 2009 11:32:12 GMT

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