Dynamically determining the number of columns in a...

Former Member · ‎05-22-2006

Hi all..

I wanted help on this..i am loading data from an excel file to an internal table using the fm aslm_excel_to_internal_table..

however using this one has to specify the number of cols before hand..

however the reqmt is as follows..

The program needs to be flexible enough to determine how many columns of characteristics are filled and adjust accordingly.

So is there a F.M that would allow me to only use the cols that are filled..

thanks in advance..

former_member480923 · ‎05-22-2006

Hi arup,

The function module aslm_excel_to_internal_table though requires the number of columns, you can give any number of value for example ZZ to that field it will pick up those columns that have valid values and will discard teh rest. That will remove ur requirement of determining the number of columns in excel file

Hope it helps

Anirban

Former Member · ‎05-22-2006

Hai Arup

&----

*& Report ZK_REPORT *

*& *

&----

*& *

&----

REPORT ZK_REPORT.

internal table declarations

DATA: BEGIN OF ITAB OCCURS 0,

NAME(20) TYPE C,

ADDR(20) TYPE C,

END OF ITAB.

DATA: ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA: K1 TYPE I VALUE 1,

M1 TYPE I VALUE 1,

K2 TYPE I VALUE 100,

M2 TYPE I VALUE 9999.

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

use FM for uploading data from EXCEL to internal table

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = 'C:\book1.xls'

I_BEGIN_COL = K1

I_BEGIN_ROW = M1

I_END_COL = K2

I_END_ROW = M2

TABLES

INTERN = ITAB1

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.

LOOP AT ITAB1.

WRITE:/ ITAB1.

ENDLOOP.

Thanks & regards

Sreenivasulu P

Former Member · ‎05-22-2006

Hai

Look at this Code Also

&----

*& Report Y_TEST *

*& *

&----

*& *

&----

REPORT Y_TEST .

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

*

PROGRAM : ZMMR0076 *

Description : This program reads an input file and uploads the data *

into MR21 transaction *

AUTHOR : A.Sudhakar Reddy *

DATE : 03/22/06 *

APPLICATION : *

PROGRAM Type: Report *

BDC Group Details **************************

BDC GROUP: For MR21 Transaction

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

*

MODIFICATION HISTORY: Correction# *

Date Author Description Scan Key Modi. Key *

-------- ----------- ----------------------- --------- ----------- *

Sudhakar

*

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

REPORT ZMMR0076

*

NO STANDARD PAGE HEADING

*

LINE-SIZE 255

*

MESSAGE-ID ZMM.

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

S T A N D A R D I N C L U D E S *

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

T A B L E S D E C L A R A T I O N S *

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

TABLES: MARA, "General Material Data,

MBEW, "Material Valuation.

T001K, "Valuation area.

MARC. "Plant Data for Material

----

P A R A M E T E R S *

----

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

*select-options: s_bukrs like too1k-bukrs NO-EXTENSION NO INTERVALS,

s_werks like marc-werks NO-EXTENSION NO INTERVALS.

SELECT-OPTIONS: P_WERKS FOR MARC-WERKS OBLIGATORY.

PARAMETERS:

P_WERKS LIKE MARC-WERKS OBLIGATORY,

P_PFILE LIKE RLGRAP-FILENAME OBLIGATORY.

SELECTION-SCREEN END OF BLOCK B1.

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

V A R I A B L E S D E C L A R A T I O N S *

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

DATA: V_DATE LIKE SY-DATUM,

L_DATE(10),

V_CMPCODE LIKE T001K-BUKRS,

V_COUNT TYPE N,

V_COUNT1 TYPE N,

V_TEMP1(15) ,

V_MATNR(25),

V_NEWVALPR(25),

V_DATE1 LIKE SY-DATUM,

V_PFILE TYPE STRING.

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

I N T E R N A L T A B L E D E C L A R A T I O N S

***

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

**

**....DATA FROM FIle

DATA : BEGIN OF IT_INPUT OCCURS 0,

MATNR LIKE MBEW-MATNR,

STPRS LIKE MBEW-STPRS,

END OF IT_INPUT.

*"INTERNAL TAB TO TAKE EXCEL SHEET.

DATA : IT_EXCEL LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE,

*"INTERNAL TAB TO give Messages.

IT_MESSAGE LIKE BUS0MSG1 OCCURS 0 WITH HEADER LINE,

*"INTERNAL TAB TO give Key Values.

IT_KEYVALUE LIKE BUSSKEYVAL OCCURS 0 WITH HEADER LINE.

*"INTERNAL TAB TO have the required

DATA : BEGIN OF IT_EXCEL1 OCCURS 0 ,

ROW1(4) TYPE C,

COL1(4) TYPE C,

VALUE(10) TYPE C,

END OF IT_EXCEL1.

FIELD-SYMBOLS: <FS>.

DATA : W_FIELD(50) TYPE C,

CNT(3) TYPE N.

DATA: BEGIN OF ITAB OCCURS 0,

COL(4) TYPE N,

END OF ITAB.

*"INTERNAL TAB which contains the actual

DATA : BEGIN OF IT_SAP OCCURS 0,

VALUE_0001(20) TYPE C,

VALUE_0002(20) TYPE C,

END OF IT_SAP.

*----Internal table for BDCDATA.

DATA: IT_BDCDATA TYPE STANDARD TABLE OF BDCDATA WITH HEADER LINE.

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

Selection Screen Validations

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

**

AT SELECTION-SCREEN.

PERFORM SCREEN_VALIDATION.

**----

A T S E L E C T I O N S C R E E N

**----

File path

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_PFILE.

PERFORM GET_VAL_HELP USING P_PFILE.

**----

S T A R T O F S E L E C T I O N

**----

START-OF-SELECTION.

Form to upload data from presentation server

PERFORM UPLOAD_PC.

Form to Process Input Data

Perform PROCESS_DATA.

Form to Deploy data into MR21 Transaction

PERFORM BDC_MR21.

**----

S U B R O U T I N E S

**----

&----

*& Form Screen_validation

&----

text

----

--> p1 text

<-- p2 text

----

FORM SCREEN_VALIDATION.

Checking if Filename is Entered

IF P_PFILE IS INITIAL.

Message : Filename cannot be Initial

MESSAGE S101(ZMM1).

ENDIF.

*

IF NOT P_WERKS IS INITIAL.

SELECT SINGLE *

FROM MARC INTO MARC

WHERE WERKS IN P_WERKS.

ELSE.

MESSAGE S011(ZMM1).

ENDIF.

ENDFORM. " Screen_validation

**----

**

Form GET_VAL_HELP

**----

**

Get F4 help for the presentation server file name

**----

**

FORM GET_VAL_HELP USING P_PFILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = ' '

IMPORTING

FILE_NAME = P_PFILE.

If Unable to Display File Dialog box

IF SY-SUBRC NE 0.

Unable to Display File Dialog box

MESSAGE E103(ZMM1).

ENDIF.

ENDFORM. " GET_VAL_HELP

**----

**

Form UPLOAD_PC

**----

**

Uploading data from presentation server

**----

*

FORM UPLOAD_PC.

CALL FUNCTION 'WS_UPLOAD'

EXPORTING

FILENAME = P_PFILE

FILETYPE = 'DAT'

TABLES

DATA_TAB = it_input

EXCEPTIONS

CONVERSION_ERROR = 1

FILE_OPEN_ERROR = 2

FILE_READ_ERROR = 3

INVALID_TYPE = 4

NO_BATCH = 5

UNKNOWN_ERROR = 6

INVALID_TABLE_WIDTH = 7

GUI_REFUSE_FILETRANSFER = 8

CUSTOMER_ERROR = 9

OTHERS = 10.

*clear v_pfile.

*v_pfile = p_pfile.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_PFILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 2

I_END_ROW = 3000

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.

If Error Opening File

IF SY-SUBRC NE 0.

Message : Unable to Open File

MESSAGE E102(ZMM1).

STOP.

ENDIF.

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

IT_EXCEL1 CONTAINS DATA IN THE FORM OF ROW, COL, VALUE *

CONVERTING THAT INTERNAL TABLE TO FORMAT THAT OF EXCEL SHEET *

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

CNT = 1.

SORT IT_EXCEL BY ROW COL.

LOOP AT IT_EXCEL WHERE ROW GT 1.

CONCATENATE 'it_sap-value_' IT_EXCEL-COL INTO W_FIELD.

ASSIGN (W_FIELD) TO <FS>.

<FS> = IT_EXCEL-VALUE.

CNT = CNT + 1.

AT END OF ROW.

APPEND IT_SAP.

CLEAR IT_SAP.

CNT = 1.

ENDAT.

ENDLOOP.

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

PASSING datafrom it_sap TO it_sap1 since it_sap contains headings *

with VALUE_0001 etc.. *

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

LOOP AT IT_SAP.

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

*loop at it_sap.

replace all OCCURRENCES of ',' in it_sap-value_0002 with ''.
modify it_sap.

*endloop.

REPLACE ALL OCCURRENCES OF ',' IN IT_SAP-VALUE_0002 WITH ''.

MODIFY IT_SAP.

IT_INPUT-MATNR = IT_SAP-VALUE_0001.

IT_INPUT-STPRS = IT_SAP-VALUE_0002.

APPEND IT_INPUT.

CLEAR IT_INPUT.

ENDLOOP.

ENDFORM. " UPLOAD_PC

**&----

**

**& Form PROCESS_DATA

**&----

**

text

**----

**

--> p1 text

<-- p2 text

**----

**

*FORM PROCESS_DATA.

*

*ENDFORM. " PROCESS_DATA

*&----

*

*& Form BDC_MR21

*&----

*

text

*----

*

--> p1 text

<-- p2 text

*----

*

FORM BDC_MR21.

SELECT SINGLE BUKRS FROM T001K INTO V_CMPCODE

WHERE BWKEY IN P_WERKS.

CLEAR: V_DATE.

V_DATE1 = SY-DATUM.

DATA: L_DATE(10).

CALL FUNCTION 'CONVERSION_EXIT_PDATE_OUTPUT'

EXPORTING

INPUT = V_DATE1

IMPORTING

OUTPUT = L_DATE.

PERFORM BDC_DYNPRO USING 'SAPRCKM_MR21' '0201'.

PERFORM BDC_FIELD USING 'BDC_CURSOR'

'MR21HEAD-BUDAT'.

PERFORM BDC_FIELD USING 'BDC_OKCODE'

'=ENTR'.

PERFORM BDC_FIELD USING 'MR21HEAD-BUDAT'

L_DATE.

PERFORM BDC_FIELD USING 'MR21HEAD-BUKRS'

V_CMPCODE.

PERFORM BDC_FIELD USING 'MR21HEAD-WERKS'

P_WERKS.

LOOP AT IT_INPUT.

V_COUNT = V_COUNT + 1.

V_COUNT1 = V_COUNT1 + 1.

*--- Passing the amount to a character field

V_TEMP1 = IT_INPUT-STPRS.

*--- Call Main Screen

IF V_COUNT = 1.

PERFORM BDC_DYNPRO USING 'SAPRCKM_MR21' '0201'.

ENDIF.

*--- Passing the Dynpro fields into the Trasaction

CONCATENATE 'CKI_MR21_0250-MATNR(' V_COUNT1 ')' INTO V_MATNR.

CONCATENATE 'CKI_MR21_0250-NEWVALPR(' V_COUNT1 ')' INTO V_NEWVALPR

.

PERFORM BDC_FIELD USING V_MATNR IT_INPUT-MATNR.

PERFORM BDC_FIELD USING V_NEWVALPR V_TEMP1.

IF V_COUNT = 5.

PERFORM BDC_FIELD USING 'BDC_OKCODE' '=DOWN'.

CLEAR V_COUNT.

ENDIF.

ENDLOOP.

PERFORM BDC_FIELD USING 'BDC_OKCODE' '=SAVE'.

IF NOT IT_BDCDATA[] IS INITIAL.

CALL TRANSACTION 'MR21' USING IT_BDCDATA MODE 'A'.

ENDIF.

CLEAR IT_BDCDATA.

REFRESH IT_BDCDATA.

CLEAR: V_COUNT, V_COUNT1.

CLEAR IT_INPUT.

REFRESH IT_INPUT.

ENDFORM. " BDC_MR21

*----

*

Start new screen

*

*----

*

FORM BDC_DYNPRO USING PROGRAM DYNPRO.

CLEAR IT_BDCDATA.

IT_BDCDATA-PROGRAM = PROGRAM.

IT_BDCDATA-DYNPRO = DYNPRO.

IT_BDCDATA-DYNBEGIN = 'X'.

APPEND IT_BDCDATA.

CLEAR IT_BDCDATA.

ENDFORM. "BDC_DYNPRO

*----

*

Insert field

*

*----

*

FORM BDC_FIELD USING FNAM FVAL.

IF FVAL <> NODATA.

IF FVAL <> ''.

CLEAR IT_BDCDATA.

IT_BDCDATA-FNAM = FNAM.

IT_BDCDATA-FVAL = FVAL.

APPEND IT_BDCDATA.

CLEAR IT_BDCDATA.

ENDIF.

ENDFORM. "BDC_FIELD

Thanks & regards

Sreeni

Dynamically determining the number of columns in an excel file