Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to Upload Excel File to Internal Table?

former_member502730
Participant
0 Kudos

Deatr all,

How to Upload Excel File data to Internal Table?

Regards,

9 REPLIES 9

Former Member
0 Kudos

Please check this sample code.

data: itab like alsmex_tabline occurs 0 with header line.

TYPES: Begin of t_record,

name1 like itab-value,

name2 like itab-value,

age like itab-value,

End of t_record.

DATA: it_record type standard table of t_record initial size 0,

wa_record type t_record.

DATA: gd_currentrow type i.

*Selection Screen Declaration

*----


PARAMETER p_infile like rlgrap-filename.

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

*START OF SELECTION

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = p_infile

i_begin_col = '1'

i_begin_row = '2' "Do not require headings

i_end_col = '14'

i_end_row = '31'

tables

intern = itab

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

if sy-subrc <> 0.

message e010(zz) with text-001. "Problem uploading Excel Spreadsheet

endif.

  • Sort table by rows and colums

sort itab by row col.

  • Get first row retrieved

read table itab index 1.

  • Set first row retrieved to current row

gd_currentrow = itab-row.

loop at itab.

  • Reset values for next row

if itab-row ne gd_currentrow.

append wa_record to it_record.

clear wa_record.

gd_currentrow = itab-row.

endif.

case itab-col.

when '0001'. "First name

wa_record-name1 = itab-value.

when '0002'. "Surname

wa_record-name2 = itab-value.

when '0003'. "Age

wa_record-age = itab-value.

endcase.

endloop.

append wa_record to it_record.

*!! Excel data is now contained within the internal table IT_RECORD

  • Display report data for illustration purposes

loop at it_record into wa_record.

write:/ sy-vline,

(10) wa_record-name1, sy-vline,

(10) wa_record-name2, sy-vline,

(10) wa_record-age, sy-vline.

endloop.

Hope this will help.

Former Member
0 Kudos

Use FM ALSM_EXCEL_TO_INTERNAL_TABLE for uploading excel file to the internal table

Try the sample program

REPORT ztest_upload.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS: p_ifile TYPE RLGRAP-FILENAME.

SELECTION-SCREEN END OF BLOCK b1.

data : itab like table of ALSMEX_TABLINE with header line.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_ifile

i_begin_col = 1

i_begin_row = 1

i_end_col = 2

i_end_row = 2

tables

intern = itab

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

Message was edited by: mukesh kumar

Former Member
0 Kudos

Hi davabap,

1. One way is

a) save the excel file as TAB Delimited file

(using excel)

b) then using GUI_UPLOAD,

simply upload this file into internal table

(this internal table should be defined

as per the data/columns seqence of file)

2. Another method is

using FM ALSM_EXCEL_TO_INTERNAL_TABLE.

3. But We cannot do this direclty !

the FM uploads data of excel

in the intenal table,

CELL BY CELL

9. afTER THAT , we have to convert this cell by cell data,

into our format of internal table.

10. use this code (just copy paste in new program)

(it is tried wit T001 structure data)

(it will AUTOMATICALLY based upon the

fields of internal table,

convert data from cell by cell,

to that of internal table fields)

REPORT abc.

*----


DATA : ex LIKE TABLE OF alsmex_tabline WITH HEADER LINE.

DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.

DATA : cmp LIKE TABLE OF rstrucinfo WITH HEADER LINE.

DATA : col TYPE i.

DATA : col1 TYPE i.

FIELD-SYMBOLS : <fs> .

DATA : fldname(50) TYPE c.

*----


CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = 'd:\def.xls'

i_begin_col = 1

i_begin_row = 1

i_end_col = 100

i_end_row = 100

TABLES

intern = ex

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

BREAK-POINT.

*----


CALL FUNCTION 'GET_COMPONENT_LIST'

EXPORTING

program = sy-repid

fieldname = 'T001'

TABLES

components = cmp.

*----


LOOP AT ex.

AT NEW row.

IF sy-tabix <> 1.

APPEND t001.

CLEAR t001.

ENDIF.

ENDAT.

col = ex-col.

col1 = col + 1.

READ TABLE cmp INDEX col.

CONCATENATE 'T001-' cmp-compname INTO fldname.

ASSIGN (fldname) TO <fs>.

<fs> = ex-value.

ENDLOOP.

BREAK-POINT.

regards,

amit m.

Former Member
0 Kudos

Hai

Go through the following Code

&----


*& 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 '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

Sreenivasulu P

Former Member
0 Kudos

Use the function module 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

The itab to store the xl data can be of structure 'KCDE_CELLS'.p_xlfile is a parameter that contain the xl file path.

The itab will store the values using the row and column number.like (1,1),(2,1).....

if any field in the xl file contains nothing,then that colmn and row number will not be stored in the itab.

Then field which has value in the xl file,only contains in the internal table.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_xlfile

i_begin_col = 1

i_begin_row = 1

i_end_col = 11

i_end_row = p_endrow

TABLES

intern = int_excel_data

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

ENDIF.

0 Kudos

ALSM_EXCEL_TO_INTERNAL_TABLE

Reward points if it helps

Regards

Gunjan

Former Member
0 Kudos

check this link :

http://www.sapdevelopment.co.uk/file/file_upexcel.htm

TEXT_CONVERT_XLS_TO_SAP

regards

srikanth

Former Member
0 Kudos

hi,

this is naveen