Convert to Excel

[Former Member]

How Do everyone!

I am using the FM SAP_CONVERT_TO_XLS_FORMAT to download

an internal table into an excel spreadsheet. Everything

is working fine apart from the leading zeroes disappearing

on one of the columns

i.e. in ABAP the document no is 0051100001 but after the

import into excel it becomes 51100001.

Does anybody know how this can be prevented on the

ABAP side of things?

Cheers

[Former Member]

by default, excel will remove leading zeroes.

so overcome this, just we should make that field as char.

for that we can do the below code in the ABAP program,

DATA : V_CHAR(1) TYPE C VALUE ''''. "this variable will have singlequote as value

CONCATENATE V_CHAR

V_FIELD "This is the field which has to

" appear with leading zeroes.

INTO V_FIELD.

then pass this field to ur internal table.

revert,if you still have concerns.

srikanth

added description to V_CHAR

[vinod_gunaware2]

Hi

Check data type of document no. It should be <b>Char</b>.

WS_EXCEL

Download internal table into file and start Excel for it [and upload changed file into the internal table]. See also see FTBU_START_EXCEL

WS_DOWNLOAD

GUI_DOWNLOAD

regards

vinod

[RichHeilman]

This is Excel formatting the column. I beleive that there is little that you can do on the ABAP side. You may be able to do something using OLE, but I'm not sure that it is worth the time and effort.

Regards,

Rich Heilman

[Former Member]

Hi andy

take the document number as a char field.If it is a char field then it will not disappear leading zeors.

Thanks

Khimavath Vikranth

[RichHeilman]

To clarify, this is not working in my system. Notice that the material number field that is char 18 field and in cases of internal number assignment, is zero filled left, but in excel the zeros are stripped off. I don't believe that there is anything that you can do to correct this using the gui_download function.

report zrich_0004 .

data: imara type table of mara with header line.


select * into table imara from mara up to 100 rows.

call function 'GUI_DOWNLOAD'
     exporting
          write_field_separator = 'X'
          filename              = 'C:test.xls'
     tables
          data_tab              = imara.

Regards,

Rich Heilman

[Former Member]

by default, excel will remove leading zeroes.

so overcome this, just we should make that field as char.

for that we can do the below code in the ABAP program,

DATA : V_CHAR(1) TYPE C VALUE ''''. "this variable will have singlequote as value

CONCATENATE V_CHAR

V_FIELD "This is the field which has to

" appear with leading zeroes.

INTO V_FIELD.

then pass this field to ur internal table.

revert,if you still have concerns.

srikanth

added description to V_CHAR

[RichHeilman]

Srikanth, That doesn't work in my system.

Regards,

Rich Heilman

[Former Member]

HI ANDY

1.just try to enter the zeros now manually and then hit an enter , the values will not be there .

2.goto the excel colomn(where the problem is) and right click and say format cells

in here u'll get a sublist of categories , in that choose text ,say ok

and repeat the procedure of entering the values with zeros manually and now hit enter

. now it will hold .

the thing is in excel it will not hold the values by download process

even though the o/p is showing(in abap) me the padded with zero values.

reverse is possible, when i want to extract from excel it is coming into abap .

regards,

vijay.

[Former Member]

Hi Andy,

This is done on the excel side as pointed out by Rich.

I dont think we can do anything on abap side to control this. (Even if you take the field as char, still you find the leading zeroes getting truncated in excel)

Regards,

Raj

[Former Member]

hi rich,

here is the test program, i have developed. in the output i got matnr (1st & 2nd records) with leading zeroes.

you can check this.

REPORT ZSRIM_TEMP1 .

DATA : V_CHAR(1) TYPE C VALUE ''''.

data : begin of itab occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

end of itab.

concatenate V_CHAR

'00000123'

into itab-matnr.

itab-mtart = 'abcd'.

append itab.

concatenate V_CHAR

'00000124'

into itab-matnr.

itab-mtart = 'abcd'.

append itab.

itab-matnr = '000125'.

itab-mtart = 'abcd'.

append itab.

itab-matnr = '126'.

itab-mtart = 'abcd'.

append itab.

itab-matnr = '127'.

itab-mtart = 'abcd'.

append itab.

data : v_fname type rlgrap-filename value 'c:\temp.xls'.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

• I_FIELD_SEPERATOR =

• I_LINE_HEADER =

I_FILENAME = v_fname

• I_APPL_KEEP = ' '

TABLES

I_TAB_SAP_DATA = itab

• CHANGING

• I_TAB_CONVERTED_DATA =

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.

[Former Member]

hi,

is this logic working or not..pl let me know.

its working for me.

srikanth

[Former Member]

Thanks guys for all your help and comments. It seems

Srikanth had the answer in that concantenating the field

with '''' worked after the import into excel.

Thanks again

Andy