topic Re: csv with leading zero in Application Development and Automation Discussions

csv with leading zero

Former Member — Wed, 04 Mar 2009 21:03:41 GMT

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,

Re: csv with leading zero

naimesh_patel — Wed, 04 Mar 2009 21:53:28 GMT

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

Re: csv with leading zero

former_member156446 — Wed, 04 Mar 2009 21:55:16 GMT

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.

Re: csv with leading zero

Former Member — Wed, 04 Mar 2009 22:23:00 GMT

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.

Re: csv with leading zero

Former Member — Wed, 04 Mar 2009 22:23:36 GMT

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.

Re: csv with leading zero

Former Member — Wed, 04 Mar 2009 22:29:30 GMT

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

Re: csv with leading zero

Former Member — Thu, 05 Mar 2009 03:18:21 GMT

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,

Re: csv with leading zero

former_member156446 — Thu, 05 Mar 2009 03:24:37 GMT

Try this :

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

Re: csv with leading zero

Former Member — Thu, 05 Mar 2009 03:30:27 GMT

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

Re: csv with leading zero

Former Member — Thu, 05 Mar 2009 03:49:26 GMT

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,

Re: csv with leading zero

Former Member — Thu, 05 Mar 2009 04:17:56 GMT

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

Re: csv with leading zero

former_member189059 — Thu, 05 Mar 2009 04:20:39 GMT

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

Re: csv with leading zero

Former Member — Thu, 05 Mar 2009 04:53:36 GMT

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

Re: csv with leading zero

Former Member — Thu, 05 Mar 2009 06:23:06 GMT

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.

Re: csv with leading zero

Former Member — Thu, 05 Mar 2009 16:08:35 GMT

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?

Re: csv with leading zero

former_member194669 — Thu, 05 Mar 2009 16:41:45 GMT


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®

Re: csv with leading zero

Former Member — Thu, 05 Mar 2009 17:17:22 GMT

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,

Re: csv with leading zero

Former Member — Thu, 05 Mar 2009 17:17:55 GMT

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

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

Re: csv with leading zero

Former Member — Wed, 13 Mar 2013 13:07:54 GMT

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