topic Re: Problem in uploading excel in Application Development and Automation Discussions

Problem in uploading excel

Former Member — Mon, 01 Jun 2009 07:19:16 GMT

Hi,

I am using fm GUI_UPLOAD.

When i use a text file then the program is working fine.

But if i use an excel file for same data then the table returned by GUI_UPLOAD (T_UPLOAD)doesnt have correct entries.Instead of having the correct data, it has some symbols (ÐÏ#à¡±#á|#################4 etc).

Please correct me where i am wrong in the approach.

The code is :

TYPES:

Structure for the upload table.
--------------------------------

BEGIN OF TP_UPLOAD,

STLNR TYPE CHAR8, "Bill of material

MATNR TYPE CHAR18, "Material number

WERKS TYPE CHAR4, "Plant

STLTY TYPE CHAR1, "BOM category

STLAN TYPE CHAR1, "BOM usage

STLST TYPE CHAR2, "BOM status

POSTP TYPE CHAR1, "Item category

BMENG TYPE CHAR17, "Base quantity

IDNRK TYPE CHAR18, "BOM component

MENGE TYPE CHAR17, "Component quantity

IDENT TYPE CHAR8, "Item ID

DATUV TYPE CHAR10, "Valid-From Date

LKENZ TYPE CHAR1, "Deletion indicator

END OF TP_UPLOAD,

For uploading the file
------------------------

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = L_PATHUP "Long path name

FILETYPE = C_FILE_TYPE "File type ASC

HAS_FIELD_SEPARATOR = C_CHK

READ_BY_LINE = 'X'

DAT_MODE = 'X'
REPLACEMENT =

TABLES

DATA_TAB = T_UPLOAD

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.

Re: Problem in uploading excel

Former Member — Mon, 01 Jun 2009 07:25:20 GMT

Hi,

Check these link : http://www.sap-img.com/bdc.htm

uploading data : use these class ,CL_GUI_FRONTEND_SERVICES under these invoke the method

GUI_UPLOAD...

example :

DATA: l_filename TYPE string.

l_filename = source_file.

CONCATENATE transdir subdir fname

INTO filename

SEPARATED BY p_sepr.

REFRESH datatab.

CLEAR flen.

CALL METHOD cl_gui_frontend_services=>gui_upload

EXPORTING

filename = l_filename

filetype = 'BIN'

IMPORTING

filelength = flen

CHANGING

data_tab = datatab[]

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

not_supported_by_gui = 17

error_no_gui = 18

OTHERS = 19.

IF sy-subrc NE 0.

WRITE: / 'Error uploading file'(003), l_filename.

EXIT.

ENDIF.

reg

siva

Re: Problem in uploading excel

Former Member — Mon, 01 Jun 2009 07:28:44 GMT

hi,

try this link: http://www.sap-img.com/abap/upload-direct-excel.htm

Re: Problem in uploading excel

Former Member — Mon, 01 Jun 2009 07:30:10 GMT

Hi Napsterr,

try this program out might be it is usefull for you.

With Regards,

S.Saravanan (EKS).

report zsdu_flash_performance_upload

no standard page heading line-size 255.

include bdcrecx1.

data: int type i.

data: begin of it_tab occurs 0,

vkorg like zsd_flash_per-vkorg,

vkbur like zsd_flash_per-vkbur,

matnr like zsd_flash_per-matnr,

zmonth type char6,

zfkimg type char13,

zkzwi1 type char13,

matkl like zsd_flash_per-matkl,

prodh like zsd_flash_per-prodh,

mvgr1 like zsd_flash_per-zmvgr1,

end of it_tab.

data: begin of it_mara occurs 0,

matnr like mara-matnr,

matkl like mara-matkl,

end of it_mara.

data: begin of it_mvke occurs 0,

matnr like mvke-matnr,

prodh like mvke-prodh,

mvgr1 like mvke-mvgr1,

end of it_mvke.

data : begin of it_dummy occurs 0,

temp(8000),

end of it_dummy.

selection-screen : begin of block b1 with frame title text-001 .

parameters: p_flname type rlgrap-filename,

p_down like rlgrap-filename.

selection-screen : end of block b1.

data: it_bdcdata like bdcdata occurs 0 with header line.

data: begin of t_col occurs 0,

col like alsmex_tabline-col,

size type i.

data: end of t_col.

data: begcol type i value 1,

begrow type i value 1,

endcol type i value 100,

endrow type i value 32000.

data: zwlen type i,

zwlines type i,

tind(4) type n,

zwfeld(20) type c,

id type n,

in type n.

data: begin of fieldnames occurs 3,

title(60),

table(6),

field(10),

kz(1),

end of fieldnames.

BEGIN OF INSERTION OF CODE VERSION-2 SARAVANAN DV1K908323

data: g_matnr like mara-matnr,

g_matkl like mara-matkl,

g_prodh like vbrp-prodh,

g_mvgr1 like vbrp-mvgr1.

END OF INSERTION OF CODE VERSION-2 SARAVANAN DV1K908323

types:begin of ty_file,

value_0001(18),

value_0002(18),

value_0003(18),

value_0004(18),

value_0005(18),

value_0006(18),

value_0007(18),

value_0008(18),

value_0009(18),

end of ty_file.

data: wa_file type ty_file.

data: lcl_down type string.

data: int_table type standard table of alsmex_tabline with header line ,

int_table1 type standard table of alsmex_tabline with header line.

field-symbols: <fs1> type any.

at selection-screen on value-request for p_down.

call function 'F4_FILENAME'

importing

file_name = p_down.

lcl_down = p_down.

at selection-screen on value-request for p_flname.

call function 'F4_FILENAME'

importing

file_name = p_flname.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = p_flname

i_begin_col = begcol

i_begin_row = begrow

i_end_col = endcol

i_end_row = endrow

tables

intern = int_table.

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

int_table1 = int_table.

clear int_table1-row.

append int_table1.

endloop.

sort int_table1 by col.

loop at int_table1.

at new col.

t_col-col = int_table1-col.

append t_col.

endat.

zwlen = strlen( int_table1-value ).

read table t_col with key col = int_table1-col.

if sy-subrc eq 0.

if zwlen > t_col-size.

t_col-size = zwlen.

modify t_col index sy-tabix.

endif.

endloop.

describe table t_col lines zwlines.

sort int_table by row col.

do zwlines times.

write sy-index to fieldnames-title.

append fieldnames.

enddo.

sort int_table by row col.

loop at int_table.

tind = int_table-col.

concatenate 'wa_FILE-VALUE_' tind into zwfeld.

assign (zwfeld) to <fs1>.

<fs1> = int_table-value.

at end of row.

condense wa_file-value_0001 no-gaps.

condense wa_file-value_0002 no-gaps.

condense wa_file-value_0003 no-gaps.

condense wa_file-value_0004 no-gaps.

condense wa_file-value_0005 no-gaps.

condense wa_file-value_0006 no-gaps.

condense wa_file-value_0007 no-gaps.

condense wa_file-value_0008 no-gaps.

condense wa_file-value_0009 no-gaps.

concatenate wa_file-value_0001

wa_file-value_0002

wa_file-value_0003

wa_file-value_0004

wa_file-value_0005

wa_file-value_0006

wa_file-value_0007

wa_file-value_0008

wa_file-value_0009

into it_dummy-temp separated by space.

append it_dummy.

clear: it_dummy, wa_file-value_0001, wa_file-value_0002, wa_file-value_0003,wa_file-value_0004,wa_file-value_0005.

endat.

endloop.

start-of-selection.

loop at it_dummy.

split it_dummy-temp at ' ' into it_tab-vkorg

it_tab-vkbur

it_tab-matnr

it_tab-zmonth

it_tab-zfkimg

it_tab-zkzwi1.

int = strlen( it_tab-matnr ).

case int.

when '5'.

concatenate '0000000000000' it_tab-matnr into it_tab-matnr.

when '6'.

concatenate '000000000000' it_tab-matnr into it_tab-matnr.

when '7'.

concatenate '00000000000' it_tab-matnr into it_tab-matnr.

when '8'.

concatenate '0000000000' it_tab-matnr into it_tab-matnr.

when '9'.

concatenate '000000000' it_tab-matnr into it_tab-matnr.

when '10'.

concatenate '00000000' it_tab-matnr into it_tab-matnr.

when '11'.

concatenate '0000000' it_tab-matnr into it_tab-matnr.

when '12'.

concatenate '000000' it_tab-matnr into it_tab-matnr.

when '13'.

concatenate '00000' it_tab-matnr into it_tab-matnr.

when '14'.

concatenate '0000' it_tab-matnr into it_tab-matnr.

when '15'.

concatenate '000' it_tab-matnr into it_tab-matnr.

when '16'.

concatenate '00' it_tab-matnr into it_tab-matnr.

when '17'.

concatenate '0' it_tab-matnr into it_tab-matnr.

endcase.

clear int.

int = strlen( it_tab-zmonth ).

case int.

when '5'.

concatenate '0' it_tab-zmonth into it_tab-zmonth.

endcase.

BEGIN OF INSERTION OF CODE VERSION-2 SARAVANAN DV1K908323

select single matkl from mara into g_matkl where matnr eq it_tab-matnr.

it_tab-matkl = g_matkl.

select single prodh mvgr1 from mvke into (g_prodh,g_mvgr1) where matnr eq it_tab-matnr.

it_tab-prodh = g_prodh.

it_tab-mvgr1 = g_mvgr1.

END OF INSERTION OF CODE VERSION-2 SARAVANAN DV1K908323

append it_tab.

clear it_tab.

BEGIN OF INSERTION OF CODE VERSION-2 SARAVANAN DV1K908323

clear: g_matkl,

g_prodh,

g_mvgr1.

END OF INSERTION OF CODE VERSION-2 SARAVANAN DV1K908323

endloop.

sort it_tab by matnr.

BEGIN OF COMMENT OF CODE VERSION-2 SARAVANAN DV1K908323
select matnr
matkl

from mara into corresponding fields of table it_mara
FOR ALL ENTRIES IN IT_TAB
where matnr eq it_tab-matnr.

select matnr
prodh
mvgr1

from mvke into corresponding fields of table it_mvke
for all entries in it_mara
where matnr eq it_mara-matnr.
.

loop at it_tab.

read table it_mara with key matnr = it_tab-matnr.

if sy-subrc eq 0.
it_tab-matkl = it_mara-matkl.
endif.

read table it_mvke with key matnr = it_tab-matnr.

if sy-subrc eq 0.
it_tab-prodh = it_mvke-prodh.
it_tab-mvgr1 = it_mvke-mvgr1.
endif.

modify it_tab.

endloop.

END OF COMMENT OF CODE VERSION-2 SARAVANAN DV1K908323

perform open_group.

loop at it_tab.

perform bdc_dynpro using 'ZSDR_FLASH_MAINTAIN_TABLES' '1000'.

perform bdc_field using 'BDC_CURSOR'

'TABNAME'.

perform bdc_field using 'BDC_OKCODE'

'=ONLI'.

perform bdc_field using 'TABNAME'

'ZSD_FLASH_PER'.

perform bdc_dynpro using 'SAPLZSD_FLASH_PER' '0001'.

perform bdc_field using 'BDC_CURSOR'

'ZSD_FLASH_PER-VKORG(01)'.

perform bdc_field using 'BDC_OKCODE'

'=AEND'.

perform bdc_dynpro using 'SAPLZSD_FLASH_PER' '0001'.

perform bdc_field using 'BDC_CURSOR'

'ZSD_FLASH_PER-VKORG(01)'.

perform bdc_field using 'BDC_OKCODE'

'=NEWL'.

perform bdc_dynpro using 'SAPLZSD_FLASH_PER' '0001'.

perform bdc_field using 'BDC_CURSOR'

'ZSD_FLASH_PER-ZKZWI1(01)'.

perform bdc_field using 'BDC_OKCODE'

'=SAVE'.

perform bdc_field using 'ZSD_FLASH_PER-VKORG(01)' it_tab-vkorg.

'DOM'.

perform bdc_field using 'ZSD_FLASH_PER-VKBUR(01)' it_tab-vkbur.

'TN02'.

perform bdc_field using 'ZSD_FLASH_PER-MATNR(01)' it_tab-matnr.

'30303'.

perform bdc_field using 'ZSD_FLASH_PER-MATKL(01)' it_tab-matkl.

'NONSTICK'.

perform bdc_field using 'ZSD_FLASH_PER-PRODH(01)' it_tab-prodh.

'OMSEL'.

perform bdc_field using 'ZSD_FLASH_PER-ZMVGR1(01)' it_tab-mvgr1.

'FYP'.

perform bdc_field using 'ZSD_FLASH_PER-ZMONTH(01)' it_tab-zmonth.

'07.2007'.

perform bdc_field using 'ZSD_FLASH_PER-ZFKIMG(01)' it_tab-zfkimg.

'20.000'.

perform bdc_field using 'ZSD_FLASH_PER-ZKZWI1(01)' it_tab-zkzwi1.

'2040.00'.

perform bdc_dynpro using 'SAPLZSD_FLASH_PER' '0001'.

perform bdc_field using 'BDC_OKCODE'

'/EABR'.

perform bdc_field using 'BDC_CURSOR'

'ZSD_FLASH_PER-VKORG(02)'.

perform bdc_dynpro using 'SAPLZSD_FLASH_PER' '0001'.

perform bdc_field using 'BDC_OKCODE'

'/EABR'.

perform bdc_field using 'BDC_CURSOR'

'ZSD_FLASH_PER-ZKZWI1(01)'.

perform bdc_dynpro using 'ZSDR_FLASH_MAINTAIN_TABLES' '1000'.

perform bdc_field using 'BDC_OKCODE'

'/EECAN'.

perform bdc_field using 'BDC_CURSOR'

'TABNAME'.

perform bdc_transaction using 'ZSD014'.

endloop.

call function 'GUI_DOWNLOAD'

exporting

BIN_FILESIZE =

filename = lcl_down

filetype = 'ASC'

APPEND = ' '

write_field_separator = 'X'

HEADER = '00'
TRUNC_TRAILING_BLANKS = ' '
WRITE_LF = 'X'
COL_SELECT = ' '
COL_SELECT_MASK = ' '
DAT_MODE = ' '
CONFIRM_OVERWRITE = ' '
NO_AUTH_CHECK = ' '
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
WRITE_BOM = ' '
TRUNC_TRAILING_BLANKS_EOL = 'X'
WK1_N_FORMAT = ' '
WK1_N_SIZE = ' '
WK1_T_FORMAT = ' '
WK1_T_SIZE = ' '
WRITE_EOL = ABAP_TRUE
IMPORTING
FILELENGTH =

tables

data_tab = it_tab

FIELDNAMES =
EXCEPTIONS
FILE_WRITE_ERROR = 1
NO_BATCH = 2
GUI_REFUSE_FILETRANSFER = 3
INVALID_TYPE = 4
NO_AUTHORITY = 5
UNKNOWN_ERROR = 6
HEADER_NOT_ALLOWED = 7
SEPARATOR_NOT_ALLOWED = 8
FILESIZE_NOT_ALLOWED = 9
HEADER_TOO_LONG = 10
DP_ERROR_CREATE = 11
DP_ERROR_SEND = 12
DP_ERROR_WRITE = 13
UNKNOWN_DP_ERROR = 14
ACCESS_DENIED = 15
DP_OUT_OF_MEMORY = 16
DISK_FULL = 17
DP_TIMEOUT = 18
FILE_NOT_FOUND = 19
DATAPROVIDER_EXCEPTION = 20
CONTROL_FLUSH_ERROR = 21
OTHERS = 22

if sy-subrc <> 0.

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

endif.

perform close_group.

Re: Problem in uploading excel

Former Member — Mon, 01 Jun 2009 07:31:11 GMT

One thing i forgot to mention in the post is I have to use GUI_UPLOAD only and not any other FM.

@ Siva --

I have tried using the class.....its not solving my problem.

Regards

Re: Problem in uploading excel

Former Member — Mon, 01 Jun 2009 08:29:13 GMT

u cane use fm 'GUI_UPLOAD' only to upload the text file data in the legacy system to SAP

internal table.

if u want to upload excel data from legacy system to SAP internal table

u have to use fm 'TEXT_CONVERT_XLS_TO_SAP' only then it will work fine

if u go for using fm 'GUI_UPLOAD' u will get some junk values.

so u have to use only fm 'TEXT_CONVERT_XLS_TO_SAP' to upload excel data to sap.

before calling function module use TYPE-POOLS : TRUXS.

Regards

Re: Problem in uploading excel

Former Member — Mon, 01 Jun 2009 08:33:32 GMT

Hi,

Try using below FM instead of GUI_UPLOAD

ALSM_EXCEL_TO_INTERNAL_TABLE

TEXT_CONVERT_XLS_TO_SAP

Re: Problem in uploading excel

Former Member — Mon, 01 Jun 2009 08:49:48 GMT

Hi,

I think while using Excel file you need to save that file with .CSV format. i.e. format as csv in excel.

Hope this will solve your problem with the same FM.

Regards,

Vijay

Re: Problem in uploading excel

Former Member — Mon, 01 Jun 2009 10:06:50 GMT

Hi Napsterr,

Below code may be useful for you try using this it may sort your problem,

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

FORM upload_from_excel .

DATA: filename TYPE rlgrap-filename,

TYPE-POOLS: kcde.

FIELD-SYMBOLS: <fs1>.

DATA: it_text_new TYPE kcde_intern WITH HEADER LINE.

i_begin_col TYPE i VALUE 1,

i_begin_row TYPE i VALUE 1,

i_end_col TYPE i VALUE 64,

i_end_row TYPE i VALUE 5000.

DATA: index TYPE i,

counter TYPE i VALUE 0.

*UPLOADING THE FILE INTO SAP

MOVE p_file TO filename.

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = filename

i_begin_col = i_begin_col

i_begin_row = i_begin_row

i_end_col = i_end_col

i_end_row = i_end_row

TABLES

intern = it_text_new

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.

SORT it_text_new BY row col.

CLEAR it_text_new.

*TO REMOVE THE HEADER

IF p_header = 'X'.

LOOP AT it_text_new.

AT END OF row.

EXIT.

ENDAT.

DELETE TABLE it_text_new.

ENDLOOP.

DELETE it_text_new INDEX 1.

ENDIF.

ENDFORM. " upload_from_excel

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

Regard,

Jana

Re: Problem in uploading excel

Former Member — Tue, 02 Jun 2009 08:37:09 GMT

closing the quest as didnt get any appropriate reply...

Re: Problem in uploading excel

Former Member — Tue, 02 Jun 2009 09:36:30 GMT

You have to use only fm 'TEXT_CONVERT_XLS_TO_SAP' to upload excel data to sap and calling function module before yoiu have to use TYPE-POOLS : TRUXS

regards,

Munibabu.K