topic Re: UPLOADING EXCEL DATA in Application Development and Automation Discussions

UPLOADING EXCEL DATA

Former Member — Thu, 04 Jan 2007 13:49:36 GMT

Hi experts...

am uploading data from excel sheet to r/3....but i have written a code for

document date(08/31/2006) to be converted to 08.31.2006..

data v1(10).

V1 = RECORD-BLDAT_001.

concatenate v10(2) '.' v13(2) '.' v1+6(4) into v1.

so when should i call the fm ws_upload..before or after this code...

pls check the code coz it works for txt files but not for xls..also please suggest me the fm with all options to upload..

Re: UPLOADING EXCEL DATA

Former Member — Thu, 04 Jan 2007 13:53:10 GMT

Hi Sampath,

Refer sample code below:

Use FM ALSM_EXCEL_TO_INTERNAL_TABLE

TYPES:

BEGIN OF ty_upload,

field1 TYPE c length 12,

field2 TYPE c length 12,

field3 TYPE c length 12,

END OF ty_upload.

DATA it_upload TYPE STANDARD TABLE OF ty_upload WITH DEFAULT KEY.

DATA wa_upload TYPE ty_upload.

DATA itab TYPE STANDARD TABLE OF alsmex_tabline WITH DEFAULT KEY.

FIELD-SYMBOLS: <wa> type alsmex_tabline.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 3

i_end_row = 65535

TABLES

intern = itab.

LOOP AT itab ASSIGNING <wa>.

CASE <wa>-col.

WHEN '0001'.

wa_upload-field1 = <wa>-value.

WHEN '0002'.

wa_upload-field2 = <wa>-value.

WHEN '0003'.

wa_upload-field3 = <wa>-value.

ENDCASE.

APPEND wa_upload TO it_upload.

CLEAR wa_upload.

ENDLOOP.

**********another way*******

TYPE-POOLS truxs.

tables : ztable.

types: begin of t_tab,

col1(5) type c,

col2(5) type c,

col3(5) type c,

end of t_tab.

data : itab type standard table of t_tab,

wa type t_tab.

data it_type type truxs_t_text_data.

parameter p_file type rlgrap-filename.

data ttab type tabname.

at selection-screen on value-request for p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = 'P_FILE'

IMPORTING

FILE_NAME = p_file

start-of-selection.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER = 'X'

i_tab_raw_data = it_type

i_filename = p_file

tables

i_tab_converted_data = itab[]

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.

end-of-selection.

loop at itab into wa.

ztable-col1 = wa-col1.

ztable-col2 = wa-col2.

ztable-col3 = wa-col3.

modify ztable.

endloop.

Reward points if this Helps.

Manish

Re: UPLOADING EXCEL DATA

Former Member — Thu, 04 Jan 2007 13:56:09 GMT

Hi Sampath,

Your part of code is correct and you need to place this code after ws_upload as your excel data will sits in internal table.

You can change the format of date aftering getting data into internal table only.

As WS_UPLOAD is absolette, use function module GUI_UPLOAD instead of WS_UPLOAD.

Thanks,

Vinay

Re: UPLOADING EXCEL DATA

Former Member — Thu, 04 Jan 2007 14:01:42 GMT

THANKS FOR TH REPLY VINAY..but my code goes as following but when i execute only ( . .) appears on the screen...do i have to append record ?

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'C:\Documents and Settings\sampath\Desktop\FILE.XLS'

FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = ' '
HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = ' '
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
CHECK_BOM = ' '
VIRUS_SCAN_PROFILE =
NO_AUTH_CHECK = ' '
IMPORTING
FILELENGTH =
HEADER =

TABLES

DATA_TAB = RECORD

EXCEPTIONS
FILE_OPEN_ERROR = 1
FILE_READ_ERROR = 2
NO_BATCH = 3
GUI_REFUSE_FILETRANSFER = 4
INVALID_TYPE = 5
NO_AUTHORITY = 6
UNKNOWN_ERROR = 7
BAD_DATA_FORMAT = 8
HEADER_NOT_ALLOWED = 9
SEPARATOR_NOT_ALLOWED = 10
HEADER_TOO_LONG = 11
UNKNOWN_DP_ERROR = 12
ACCESS_DENIED = 13
DP_OUT_OF_MEMORY = 14
DISK_FULL = 15
DP_TIMEOUT = 16
OTHERS = 17

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

data v1(10).

V1 = RECORD-BLDAT_001.

concatenate v10(2) '.' v13(2) '.' v1+6(4) into v1.

Re: UPLOADING EXCEL DATA

Former Member — Thu, 04 Jan 2007 14:11:58 GMT

Hi Sampath,

LOOP the internal table and change the format as per your requirement and use MODIFY statement to get changes updated.

DATA V TYPE SY-DATUM.

DATA V1 TYPE SY-DATUM.

LOOP AT RECORD.

V1 = RECORD-BLDAT_001.

concatenate v10(2) '.' v13(2) '.' v1+6(4) into V.

RECORD-BLDAT_001 = V.

MODIFY RECORD INDEX SY-TABIX.

CLEAR RECORD.

ENDLOOP.

Note: Plz mark all helpful answers

Thanks,

Vinay

Re: UPLOADING EXCEL DATA

Former Member — Thu, 04 Jan 2007 14:22:19 GMT

thankx for that vinay it works the same but still not for excel sheet....i have written that code after upload...[pls help on this]

Re: UPLOADING EXCEL DATA

Former Member — Thu, 04 Jan 2007 14:52:23 GMT

check whether u have saved it as tab delimited excel file else GUI_UPLOAD wont work

Re: UPLOADING EXCEL DATA

Former Member — Fri, 05 Jan 2007 03:20:43 GMT

Dear Sampath,

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 *

Re: UPLOADING EXCEL DATA

Former Member — Thu, 06 Mar 2008 09:48:31 GMT

Hi Sampath,

Try This.....

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'C:\Documents and Settings\sampath\Desktop\FILE.XLS'

FILETYPE = 'BIN'

*HAS_FIELD_SEPARATOR = ' '

*HEADER_LENGTH = 0

*DAT_MODE = ' '

*CODEPAGE = ' '

*IGNORE_CERR = ABAP_TRUE

*REPLACEMENT = '#'

*CHECK_BOM = ' '

*VIRUS_SCAN_PROFILE =

*NO_AUTH_CHECK = ' '

*IMPORTING

*FILELENGTH =

*HEADER =

TABLES

DATA_TAB = RECORD

EXCEPTIONS

FILE_OPEN_ERROR = 1

FILE_READ_ERROR = 2

NO_BATCH = 3

GUI_REFUSE_FILETRANSFER = 4

INVALID_TYPE = 5

NO_AUTHORITY = 6

UNKNOWN_ERROR = 7

BAD_DATA_FORMAT = 8

HEADER_NOT_ALLOWED = 9

SEPARATOR_NOT_ALLOWED = 10

HEADER_TOO_LONG = 11

UNKNOWN_DP_ERROR = 12

ACCESS_DENIED = 13

DP_OUT_OF_MEMORY = 14

DISK_FULL = 15

DP_TIMEOUT = 16

OTHERS = 17

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 RECORD.

concatenate

RECORD-BLDAT_001+0(2) '.'

RECORD-BLDAT_001+3(2) '.'

RECORD-BLDAT_001+6(4) into RECORD-BLDAT_001.

Modify Record transporting BLDAT_001.

Endloop.

Cheers!!

Lokesh