csv with leading zero

[Former Member]

Hi guys,

My client is requiring a csv file so they can open up in Excel. The issue is.

I have a field value 01234 and it only shows 1234 when open in excel, when opened with notepad or ultra edit, it's 01234. This is because excel hide the leading zero if it's a number.

Does anybody know what should I do to go around this? in program I have the following syntax

concatenate A B C D separated by ",".

Should I concatenate something before A to keep the first 0 to show up in excel? I think it maybe a sing quote " ' ". But maybe I'm wrong.

Thanks,

[naimesh_patel]

Yes, you can concatenate single quote to the number to prevent leading zero.

Or you can educate your users to follow the steps mentioned in this document:

[Preserve Downloaded data formatting in Excel|http://help-abap.blogspot.com/2008/09/preserve-downloaded-data-formatting-in.html]

Regards,

Naimesh Patel

[Former Member]

Hi,

I tried string type but it didn't work

@Naimesh, I tried concatenate sing quote " ' " before concatenate everything, but single quote is special character in ABAP, it can not be used as a hardcoded value, what should I do now?

Thanks again,

[former_member156446]

Try this :

data: ....
      data(1000) type c.
....

[Former Member]

Yeah, I did try type c but didn't work too.

Ok, I think I will tell client to format cell and change settings in excel, I know it sounds silly but they have reached technical limit.

Cheers guys,

[former_member189059]

Hello,

Use the escape sequence for single quotes

concatenate '''' myfield into myfield.

ensure that your field can contain one more character than originally intended to accommodate for the apostrophe

[former_member156446]

Hi check this code, I can see the leading zeros, there is some thing to do with the data type you are using... they need to be of type char or type string as I did..

REPORT  zj_test LINE-SIZE 162.

TYPES: BEGIN OF itab,
         data TYPE string,
       END OF itab.
DATA: string TYPE STANDARD TABLE OF itab,
      wa_string TYPE itab.

CONCATENATE '0000A' 'DCF932' INTO wa_string-data SEPARATED BY ','.

APPEND wa_string TO string.

CALL METHOD cl_gui_frontend_services=>gui_download
  EXPORTING
*    bin_filesize              =
    filename                  = 'C:\Documents and Settings\jay\test.csv'
*    filetype                  = 'ASC'
*    append                    = SPACE
*    write_field_separator     = SPACE
*    header                    = '00'
*    trunc_trailing_blanks     = SPACE
*    write_lf                  = 'X'
*    col_select                = SPACE
*    col_select_mask           = SPACE
*    dat_mode                  = SPACE
*    confirm_overwrite         = SPACE
*    no_auth_check             = SPACE
*    codepage                  = SPACE
*    ignore_cerr               = ABAP_TRUE
*    replacement               = '#'
*    write_bom                 = SPACE
*    trunc_trailing_blanks_eol = 'X'
*    wk1_n_format              = SPACE
*    wk1_n_size                = SPACE
*    wk1_t_format              = SPACE
*    wk1_t_size                = SPACE
*  IMPORTING
*    filelength                =
  CHANGING
    data_tab                  = string
  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
    not_supported_by_gui      = 22
    error_no_gui              = 23
    OTHERS                    = 24.
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]

Jay, I will try type string out. Will let you know how's it going. Thanks

[Former Member]

Hii Jay,

The above is working fine in case of alpha numeric. but  i'm concatenating only numeric variables than in that case leading zeros are removed in csv. Zeros removed in case of Numeric...

[Former Member]

Thanks Naimesh, unfortunately they do not want to change settings in Excel (format cell with fix length).

I was thinking about that single quote thing, but try to see if there's more things coming up since they requires fix lenth.

[Former Member]

Thanks Naimesh, unfortunately they do not want to change settings in Excel (format cell with fix length).

I was thinking about that single quote thing, but try to see if there's more things coming up since they requires fix lenth.

[Former Member]

Hi Ben,

This question is being asked a number of times, even I also asked once...

Please check in forums before wasting your time.

This can't be passible, as you also know its a default property of XL that leading zeros will not be shown.

Thanks,

Krishna..

[Former Member]

hiii

if you are using csv format then all cells of excel will be in text format by default.so if you want leading zeros with any numeric value then just convert that value in type of character then pass it into internal table then pass that internal table in to FM.

Regards

twinkal

[Former Member]

hi,

I had that same problem before and the only solution was to concatenate a ' ' ' in front of the numbers or any other signs.

There is no other solutions to that unless the user format the cells each time the file is downloaded.

thanks

[Former Member]

Hi Ben,

Use ' ' ' in front of the numbers excel will take it as text instead of numeric.

eg: concatenate ' ' ' in front of the numbers .

Regards,

Kiran.

[Former Member]

oh man, guys, I tried that and failed, did you try concatenate ' ' ' ?

Again single quote is system special character, when you type a single quote, it will start a new hardcode area, next single quote will close that area, and then third single quote will start new hardcode area.

Try it and compile, it won't. Am I wrong?

[former_member194669]

Again single quote is system special character, when you type a single quote, it will start a new hardcode area, next single quote will close that area, and then third single quote will start new hardcode area. 

Test this sample code first

report  zars no standard page heading
        line-size 170
        line-count 65(4).
constants : c_quote type c value ''''.
data : v_text(20) type c.
concatenate c_quote 'test' into v_text.
write : v_text.

a®

[Former Member]

ARS, to be hornest, That was a GREAT WAY TO CHEAT

congratulations, you solved it, I wish I could give you more than 10 points.

CHEERS,

[Former Member]

Who can say this is impossible then? have a great day guys!

Edited by: Ben Boman on Mar 5, 2009 6:17 PM