Skip to Content
avatar image
Former Member

Excel file is not considering some PH6 codes as TEXT format!

Hello,

I wrote a report program for reading a custom table and downloading into an excel file of LAN folder, I'm doing as below,

1) Reading custom table, for example, fields are MATNR, Material description, Plant, PH6 code, Type etc.

2) We want to download above Z table's data into a LAN folder, its path as below

\\hoeeafsh\sha$\out\MM\PUR\MATNR_DETAILS.XLS

3) I'm accessing the file for to WRITE operation from a LAN folder by using OPEN DATASET as below,

CATCH SYSTEM-EXCEPTIONS open_dataset_no_authority = 1.  
  OPEN DATASET lv_physical_file  FOR OUTPUT  IN TEXT MODE  WITH 
  WINDOWS LINEFEED  ENCODING DEFAULT.  
  IF sy-subrc <> 0.  
    MESSAGE e000(zz) WITH TEXT-010 sy-subrc.  
    RETURN.  
  ENDIF.  
ENDCATCH.  
IF sy-subrc = 1.  
  EXIT.  
ENDIF. 

4) And sending/downloading data by using the TRANSFER syntax as below,

TRANSFER lv_line TO lv_physical_file.

5) After downloading the data into excel file (MATNR_DETAILS) in LAN folder, some PH6 code data is going bad, as below,

a) If PH6 code is 201520603030, then its printing in excel file as-is, which is fine

b) If PH6 is 2015101010E0 then in excel file its printing as '2.02EE+09'

I guess its because of having some alphabet at the 11th position

Pl. let us know how we can fix this issue (Excel file is not treating some some PH6 codes as TEXT format)

In my custom table the PH6 code is STRING type field

Thank you

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 29, 2017 at 12:36 PM

    I think this is more an excel issue then a SAP issue.

    You can either instruct the person who uses the excel file to open it and then choose all columns as text.

    Or you can show the data as you want in the excel by concatenating

    =" in front and " after the value. So in your case, rather then to place the value 2015101010E0 you need to put ="2015101010E0" in the string/column/field. Now when you opne the excel you will see 2015101010E0 rather then 2.02EE+09. (you won't see the =" and " signs)

    regards,

    Peter

    Add comment
    10|10000 characters needed characters exceeded

    • You're exactly right - it's the Excel thing. Excel sees a bunch of numbers and E0 at the end, so it thinks this is a number with exponent. Unless instructed otherwise, Excel just picks the format that it thinks is best suitable and sometimes it guesses wrong. Excel does not know or care what the field format was in the ABAP program.

      @OP - in general, please understand that when you create the files for another application you need to "speak the language" of that application. In this case Excel just had to be told it was a text/string, not number. And how to do this can be found in the Excel documentation.

      As a side note - you might want to use class-based exceptions, they've been available for a long time already. Look it up.