DOWNLOAD into EXCEL -URGENT

[manjunatha_suryaprakash]

Hi,

I am downloading data to excel file.

Some fields has data like 0013, but when it goes to excel cell, it becomes 13 instead of 0013. So, while uploading, it is a problem.... That to the field has check table and it looks for only 0013 and not 13.

If it had been numeric field, then i could have made 0013 by assignment. But it is a character field.

This object is on variant download/upload functionlaity. So, this program can be used for any report program to know its variants. I cannot know i am getting char/numc type of parameters.

Please suggest me whether i can handle somehow while downloading the data on to excel itself.

Can i have some function modules which will control the cell FORMATTING capabilities.

Regards,

Manjunatha.T.S

[Former Member]

Hi,

Try using fm EXCEL_OLE_STANDARD_DAT,

Rgds,

[Former Member]

Hai,

Do one thing. After uploading you file into Internal table Pass that field in Call function 'conversion_exit_alpha_input'.

Then the value will change '13' to '0013'.

Hope this will help you.

_CHEERS

[manjunatha_suryaprakash]

Hi Deepak,

This is kind of dynamic. So, I will not be knowing whether the conversion_exit_alpha_input is there for that particular field or not.

WHen user gives the (any) report and variant name, pgm should download all the variants and its values in excel sheet. User will do some changes here and there and marks as 'X' in excel. So, which identifies X's and upload the value back to variants. At this time, i will not be knowing whether there will be a conversion_exit_alpha_input.

Regards,

Manjunatha.T.S

[Former Member]

Hi,

What I am telling is after changing that 'X' in excel sheet you are going to upload it directly into internal table right?

So after the data has uploaded into internal table itab pass that itab-field into the call function which i mentioned above.

EX:

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = itab-feild

IMPORTING

OUTPUT = itab-field.

Then the value will change from '13' to '0013'.

Hope this will help you.

_CHEERS

If your problem is solved reward the points and close the thread.

[manjunatha_suryaprakash]

Guys,

The problem is still not solved. Even if i do it manually, it will not bring back 0015 again. Becuase while downloading itself, the data has already become 15 from 0015. So next time if i change the cell format to text. It will not bring those 00's back.

But, User cannot know how many 0's come before 15 because there is a check table attached to that field with length 4. If he enters 5zeros before 15, it will not accept i think.

Please if any body has any solution, please suggest us.

[athavanraja]

you want to programatically downlod some data to excel and then later on use the same excel file for upload.

before upload the users have to do the manual step of adding the zeros.

but as you rightly said the users wont know how many zeros to add. but the upload program you have written know the data type and length of the field in which you are going to update the value.

so you have to modify your program to add zeros or wherever the preceeding zeros are required declare those fileds as type n for the system to automatically prefix zeros.

Hope this is clear.

REgards

Raja

[Former Member]

Hi Manjunatha

You do not have a function module for this. What u need to do is to manually change the format of the excel column from general to text. Then the zeroes are no longer truncated from the left.

Hope it helps.

Abhishek

[manjunatha_suryaprakash]

Hi,

THe excel will receive data as 13 instead of 0013. So, Even if you do manually change the format of excel cell, i will not get 00013 .

[Former Member]

Hi,

Can I know after downloading into Excel what are you going to do.I mean any upload into another program?

Please be elaborate...

_CHEERS

If your problem is solved reward the points and close the thread.

Message was edited by: Deepak333 k

[athavanraja]

this is a common issue of excel when using with ABAP.

you need to open the excel and manually modify the column.

how?

for e.g if you have a field with length 5 and you want all the number in the column with preceeding 0 for blanks (5 - 00005, 123 - 00123)

select the whold column and right click

choose format cells

select the catageory custom

and in the type field just type 5 0s (00000).

now click ok and watch the column. just save the file and use it to upload.

Regards

Raja

[Former Member]

Hi,

U can do as Raja Suggested that will be the better way. This is with the <b>formatting in Excel</b> nothing to do with the code.

Choose the column u need to format , right click and choose Format Cells>Number > custom > type 0000

SO dont try to change the code just format ur excel.

Hope u got it.

Kindly reward points and close the thread if ur problem got solved ot revert back.

[Former Member]

Hey MTS!!

This might actually sound very silly at the first look.

But if u can be sure that nothing will be done with the data in the file till the time it gets uploaded back into sap then u can probably do this.

u can't store 0015 as 0015 in excel.

but u can store it as OO15 (english character 'O')

now when u retrieve the data back, retrieve it in a type n variable of the requisite length....it will then be retrieved as 0015(zero zero one five)...

does that make a bit of sense....i know this is too weird...but i tested it works...may b it can help u too...

u can try this sample code...

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

types : begin of n_c,

n1(4) type n,

end of n_c.

types : begin of t_c,

c1(4) type c,

end of t_c.

data : itab type table of t_c with header line,

ntab type table of n_c with header line.

itab-c1 = 'oo12'.

append itab.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = 'C:\num.txt'

FILETYPE = 'ASC'

WRITE_FIELD_SEPARATOR = ' '

tables

data_tab = itab

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.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = 'C:\num.txt'

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = ' '

tables

data_tab = ntab

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.

loop at ntab.

write 😕 ntab-n1.

endloop.

once u have this data back in ur table, then u can convert it again to character and proceed with ur logic...

rgds,

PJ

Message was edited by: Priyank Jain

Message was edited by: Priyank Jain

[Former Member]

Hi,

Maybe I didn't get your requirements right.

But, I in case of download into Excel data with leading zeros use this formatting:

IF <Define if I get leading zeros>.

SET PROPERTY OF CELL 'NumberFormat' = '@'.

SET PROPERTY OF CELL 'VALUE' = ValueToDownload.

ELSE.

SET PROPERTY OF CELL 'NumberFormat' = 'General'.

ENDIF.

Best regards,

Eugene

[manjunatha_suryaprakash]

Hi Guys,

Sorry for the delay,..... But fortunately i could solve this problem by changing the Value field itself.

What i mean to say is, I tried to avoid the value starting from zero which was actually creating the problem.

So, I made the value to look like "Value is: <value>" and the problem is solved.

Regards,

Manjunatha.T.S

[Former Member]

Manjunatha.T.S ,

For this you would have to use basic EXCEL formula.

Type in 00 in one cell of the sheet:

A1 = 00

A2 = 13 (value from SAP)

A3 = concatenate(A1,A2), this will give value as 0013

Copy and paste special values.

Cheers

Vibha