topic Re: uploading dat from excel file in Application Development and Automation Discussions

uploading dat from excel file

Former Member — Mon, 02 Apr 2007 15:35:32 GMT

hi,

I have a excel file with 5 worksheets , every work sheet has different data.

how do i extract data of a particular worksheet from the excel file.

neha.

Re: uploading dat from excel file

Former Member — Mon, 02 Apr 2007 15:39:12 GMT

hi,

Did you try using '<b>ALSM_EXCEL_TO_INTERNAL_TABLE</b>' function module?

Regards,

Santosh

Re: uploading dat from excel file

Former Member — Mon, 02 Apr 2007 15:39:37 GMT

Hi!

In the excelt, choose File - Save as menu. Save the first sheet in CSV format. Choose the second sheet then save it also in CSV format. Do this on all 5 sheets.

Then use the function module GUI_UPLOAD on the 5 CSV type files.

Regards

Tamá

Re: uploading dat from excel file

Former Member — Mon, 02 Apr 2007 15:48:53 GMT

Hi,

Name of my excel file is File.xls which has five work sheets that is table1 to table2

so when i click on each work sheet u wanted me to save in csv format so in that case what will be the CSV filename is it i have to give the worksheet name ? i.e table1.csv , table2.csv , table3.csv.........

neha

Re: uploading dat from excel file

Former Member — Mon, 02 Apr 2007 15:49:14 GMT

hi neha,

Call this FORM as many times with the filenames and the start and end columns and rows..

form table_excel using filename(30)

startcol type i

endcol type i

startrow type i

endrow type i

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = startcol

i_begin_row = startrow

i_end_col = endcol

i_end_row = endrow

i_end_row = 4000

TABLES

intern = t_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.

MESSAGE i010(zn) WITH 'Error in upload Please check the upload file'.

ENDIF.

DESCRIBE TABLE T_EXCEL LINES L_ENDROW1.

L_ENDROW1 = L_ENDROW1 / 2.

LOOP AT t_excel WHERE row = q_count.

IF t_excel-col = 1.

t_type2-gsber = t_excel-value.

ELSEIF t_excel-col = 2.

t_type2-bukrs = t_excel-value.

ENDIF.

ENDLOOP.

l_charlen1 = strlen( t_type2-bukrs ).

l_charlen2 = strlen( t_type2-gsber ).

if ( t_type2-bukrs <> ' ' and t_type2-gsber <> ' ' ).

if ( l_charlen1 = 4 and l_charlen2 = 4 ).

APPEND t_type2.

CLEAR t_type2.

endif.

q_count = q_count + 1.

IF q_count > l_endrow1.

EXIT.

ENDIF.

ENDDO.

data : it_zfi_is_qty like table of zfi_is_qty with header line.

loop at t_type2.

move-corresponding t_type2 to it_zfi_is_qty.

append it_zfi_is_qty.

endloop.

insert zfi_is_qty from TABLE it_zfi_is_qty .

endform.

hope it works..

also check this...

~~Guduri

Re: uploading dat from excel file

Former Member — Mon, 02 Apr 2007 17:47:34 GMT

do like this :;

DATA : BEGIN OF TYP_INPUT ,

MATNR LIKE MARA-MATNR,

WERKS LIKE MARC-WERKS,

LGORT LIKE MARD-LGORT,

LGNUM LIKE MLGN-LGNUM,

LGTYP LIKE MLGT-LGTYP,

LTKZA LIKE MLGN-LTKZA,

LTKZE LIKE MLGN-LTKZE,

LGBKZ LIKE MLGN-LGBKZ,

LGPLA LIKE MLGT-LGPLA,

END OF TYP_INPUT.

*Input File Data

DATA : IT_FILE LIKE TYP_INPUT OCCURS 0 WITH HEADER LINE.

*"INTERNAL TAB TO TAKE EXCEL SHEET.

DATA : IT_EXCEL LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_PFILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 9

I_END_ROW = 6000

TABLES

INTERN = IT_EXCEL

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

If Error Opening File

IF SY-SUBRC NE 0.

Message : Unable to Open File

MESSAGE I102.

STOP.

ENDIF.

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

IT_EXCEL CONTAINS DATA IN THE FORM OF ROW, COL, VALUE *
CONVERTING THAT INTERNAL TABLE TO FORMAT THAT OF EXCEL SHEET *

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

IF NOT IT_EXCEL[] IS INITIAL.

SORT IT_EXCEL BY ROW COL.

LOOP AT IT_EXCEL.

CASE IT_EXCEL-COL.

WHEN 1.

IT_FILE-MATNR = IT_EXCEL-VALUE.

WHEN 2.

IT_FILE-WERKS = IT_EXCEL-VALUE.

WHEN 3.

IT_FILE-LGORT = IT_EXCEL-VALUE.

WHEN 4.

IT_FILE-LGNUM = IT_EXCEL-VALUE.

WHEN 5.

IT_FILE-LGTYP = IT_EXCEL-VALUE.

WHEN 6.

IT_FILE-LTKZA = IT_EXCEL-VALUE.

WHEN 7.

IT_FILE-LTKZE = IT_EXCEL-VALUE.

WHEN 8.

IT_FILE-LGBKZ = IT_EXCEL-VALUE.

WHEN 9.

IT_FILE-LGPLA = IT_EXCEL-VALUE.

ENDCASE.

AT END OF ROW.

APPEND IT_FILE.

CLEAR IT_FILE.

ENDAT.

ENDLOOP.

ELSE.

MESSAGE I000 WITH 'The input file is empty'.

STOP.

ENDIF.

parameters: p_file like rlgrap-filename.

data: it_rows type standard table of t_itab with header line.

data : v_index type i.

field-symbols : <fs>.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = p_file

i_begin_col = 1

i_begin_row = 4

i_end_col = 15

i_end_row = 65000

tables

intern = it_rows

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

if not it_rows[] is initial.

sort it_rows by row col.

loop at it_rows.

move : it_rows-col to v_index.

assign component v_index of structure it_output to <fs>.

move : it_rows-value to <fs>.

at end of row.

append it_output.

clear it_output.

endat.

endloop.

endif.

Have a look at OSS Notes 129994 and 127666. Hope these notes give some inputs.

Please go though the following lines of code:

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

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

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

TABLES: ANEP,

BKPF.

TYPES: BEGIN OF TY_TABDATA,

MANDT LIKE SY-MANDT, " Client

ZSLNUM LIKE ZSHIFTDEPN-ZSLNUM, " Serial Number

ZASSET LIKE ZSHIFTDEPN-ZASSET, " Original asset that was transferred

ZYEAR LIKE ZSHIFTDEPN-ZYEAR, " Fiscal Year

ZPERIOD LIKE ZSHIFTDEPN-ZPERIOD, " Fiscal Period

ZSHIFT1 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 1

ZSHIFT2 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 2

ZSHIFT3 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 3

END OF TY_TABDATA.

*----

Declaration of the Internal Table with Header Line comprising of the uploaded data.

*----

DATA: BEGIN OF IT_FILE_UPLOAD OCCURS 0.

INCLUDE STRUCTURE ALSMEX_TABLINE. " Rows for Table with Excel Data

DATA: END OF IT_FILE_UPLOAD.

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

S E L E C T I O N - S C R E E N *

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

SELECTION-SCREEN: BEGIN OF BLOCK B1 WITH FRAME,

BEGIN OF BLOCK B2 WITH FRAME.

PARAMETERS: P_FNAME LIKE RLGRAP-FILENAME OBLIGATORY.

SELECTION-SCREEN: END OF BLOCK B2,

END OF BLOCK B1.

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

E V E N T : AT S E L E C T I O N - S C R E E N *

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

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FNAME.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '

STATIC = 'X'

MASK = '.'

CHANGING

FILE_NAME = P_FNAME

EXCEPTIONS
MASK_TOO_LONG = 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.

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

E V E N T : S T A R T - O F - S E L E C T I O N *

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

START-OF-SELECTION.

--------------------------------------
Upload Excel file into Internal Table.
--------------------------------------

PERFORM UPLOAD_EXCEL_FILE.

-------------------------------------------------------
Organize the uploaded data into another Internal Table.
-------------------------------------------------------

PERFORM ORGANIZE_UPLOADED_DATA.

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

E V E N T : E N D - O F - S E L E C T I O N *

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

END-OF-SELECTION.

&----

*& Form UPLOAD_EXCEL_FILE

&----

text

----

--> p1 text
<-- p2 text

----

FORM UPLOAD_EXCEL_FILE .

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FNAME

I_BEGIN_COL = 1

I_BEGIN_ROW = 3

I_END_COL = 7

I_END_ROW = 32000

TABLES

INTERN = IT_FILE_UPLOAD

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.

ENDFORM. " UPLOAD_EXCEL_FILE

&----

*& Form ORGANIZE_UPLOADED_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM ORGANIZE_UPLOADED_DATA .

SORT IT_FILE_UPLOAD BY ROW

COL.

LOOP AT IT_FILE_UPLOAD.

CASE IT_FILE_UPLOAD-COL.

....................................................

WHEN 1.

WA_TABDATA-ZSLNUM = IT_FILE_UPLOAD-VALUE.

WHEN 2.

WA_TABDATA-ZASSET = IT_FILE_UPLOAD-VALUE.

WHEN 3.

WA_TABDATA-ZYEAR = IT_FILE_UPLOAD-VALUE.

WHEN 4.

WA_TABDATA-ZPERIOD = IT_FILE_UPLOAD-VALUE.

WHEN 5.

WA_TABDATA-ZSHIFT1 = IT_FILE_UPLOAD-VALUE.

WHEN 6.

WA_TABDATA-ZSHIFT2 = IT_FILE_UPLOAD-VALUE.

WHEN 7.

WA_TABDATA-ZSHIFT3 = IT_FILE_UPLOAD-VALUE.

....................................................

ENDCASE.

AT END OF ROW.

WA_TABDATA-MANDT = SY-MANDT.

APPEND WA_TABDATA TO IT_TABDATA.

CLEAR: WA_TABDATA.

ENDAT.

ENDLOOP.

ENDFORM. " ORGANIZE_UPLOADED_DATA

In the subroutine --> ORGANIZE_UPLOADED_DATA, data are organized as per the structure declared above.

Regards,

Abir

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

Don't forget to award points *

shankar tm

Posts: 75

Questions: 2

Registered: 9/27/06

Forum points: 54

Re: Excel to internal table

Posted: Jan 5, 2007 1:22 PM in response to: yanut Reply E-mail this post

hi yusuf,

the function module used to fill the internal table from the excel file is ALSM_EXCEL_TO_INTERNAL_TABLE'

these are the sample value

Import parameters Value

FILENAME C:\TEST.XLS

I_BEGIN_COL 1

I_BEGIN_ROW 1

I_END_COL 2

I_END_ROW 69

Tables Value

INTERN int_table

u will get the result in the internal table

if it helps reward

Message was edited by:

sunil kumar