Using UTL_FILE to Create CSV File

[TimboA]

I'm not sure which Forum is best suited to my query, but I'll try here first !!

I'm creating a CSV file using UTL_FILE from Oracle 8.1.5 with out any problem. My problem comes when a User attempts to open the CSV and read the data. One of the fields I'm outputting is a Numeric field that is 19 digits in length. Unfortunately, when the file is opened in MS Excel, the field is shown as 1.12345E+18 instead of the full number (e.g.) 1234567890123456789 If the column then formatted in MS Excel to be a number, the data is displayed as 1234567890123450000 (i.e. the last 4 digits get set to zero !!)

Any ideas what I can do to workaround this ??

(PS => I've already thought of enclosing my numeric fields in quote marks - this works, but looks unsightly so I can't really see my users going for this!!)

Thanks.

 

[carp]

Where your 19-digit number appears, you might try
to_char(my_big_number,'9999999999999999999').

Using this approach allows me to get:

select 1234567890123456789
from dual;

1.2346E+18

to display as:

select to_char(1234567890123456789,'9999999999999999999')
from dual;

1234567890123456789

This converts your number to a character string. Using the 9s instead of 0s assures that you won't get leading zeros. I think when Excel sees a string of digits, it will interpret it as a large number.

 

[TimboA]

Unfortunately, I'd thought of this too, but it doesn't work !!

Damn that MS Excel !

Thanks anyway carp.

 

[carp]

Hmm. Does Excel have any function (like Oracle's to_number()) that will accept a character string and convert it to a number? If so, you could just format that column in the spreadsheet and play on. Ugly, but a kill's a kill!