Home » SQL & PL/SQL » SQL & PL/SQL » Spool decimal value to csv file
Spool decimal value to csv file [message #351431] Tue, 30 September 2008 16:26 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

HI,

I need to spool amount field to the csv file in such away that If the amount field has zeros after decimal, it should load that amount value in the csv file with zeros after decimal..
I am successfully able to spool those decimal values which contain digits other than zeros after decimals. (like 1234...)

i am trying something like this...

spool file.csv
select
'"'col1||'","'||
to_char(amount_field,'9999.99')||'"'
from table1



any hint would appreciated !!!

Re: Spool decimal value to csv file [message #351449 is a reply to message #351431] Tue, 30 September 2008 21:02 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Not sure I understand the problem you are having:

SQL> select * from t1;

AMOUNT_FIELD
------------
        1234
     1234.99

SQL> spool /tmp/file.csv
SQL> select to_char(amount_field, '9999.99') from t1;

TO_CHAR(
--------
 1234.00
 1234.99

SQL> spool off
SQL> !cat /tmp/file.csv
SQL> select to_char(amount_field, '9999.99') from t1;

TO_CHAR(
--------
 1234.00
 1234.99

SQL> spool off


Re: Spool decimal value to csv file [message #351537 is a reply to message #351449] Wed, 01 October 2008 07:52 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Oracle 9.2.0.8.0 window professional
I am creating output file on window.. So, after spool i am getting the output amount field value in the csv file like this....

Amount_field
--------
1234
1234.99


The field value which i am currently getting is 1234 but somehow it should be 1234.00
Re: Spool decimal value to csv file [message #351539 is a reply to message #351537] Wed, 01 October 2008 07:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
shoaib123 wrote on Wed, 01 October 2008 08:52
Oracle 9.2.0.8.0 window professional
I am creating output file on window.. So, after spool i am getting the output amount field value in the csv file like this....

Amount_field
--------
1234
1234.99


The field value which i am currently getting is 1234 but somehow it should be 1234.00

If you ran the code ebrian gave you, you will have what you need. What's the problem? Obviously you ran some other incorrect code.
Re: Spool decimal value to csv file [message #351540 is a reply to message #351537] Wed, 01 October 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*PLus, post a description of your table (desc mytable) AND the query you executed.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Spool decimal value to csv file [message #351541 is a reply to message #351540] Wed, 01 October 2008 08:25 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

SQL> desc test;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
COL1 VARCHAR2(12) Y                         
COL2 DATE         Y                         

SQL> desc test;
Name         Type         Nullable Default Comments 
------------ ------------ -------- ------- -------- 
COL1         VARCHAR2(12) Y                         
AMOUNT_FIELD NUMBER       Y                        

SQL> spool C:\temp\file.csv
Started spooling to C:\file.csv

SQL> set head off;
SQL> select
'"'||col1||'","'||
trim(to_char(amount_field,'9999.99'))||'"'
from test
"Hoe","23.00"
"Joe","123.99"

SQL> spool off
Stopped spooling to C:\temp\file.csv 


Upto here everything looks perfectly ok.. But when i go to C:\temp folder and double click on the file.csv and open it up. Here is what i am getting....

Hoe	         123
Joe	         123.99


The amount field which i am expecting to be 123.00 in the csv file, it is 123. The two 0's after the decimal sign is missing..
Re: Spool decimal value to csv file [message #351542 is a reply to message #351541] Wed, 01 October 2008 08:31 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
That's a problem with excel or lotus 1-2-3 or whatever you are using, not Oracle. Just so you know, 23 is the same as 23.00.
Re: Spool decimal value to csv file [message #351543 is a reply to message #351541] Wed, 01 October 2008 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your Excell cells to show you number with 2 decimals.

Regards
Michel
Re: Spool decimal value to csv file [message #351544 is a reply to message #351542] Wed, 01 October 2008 08:40 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Joy..

Appreaciate your time on my question..

I knew that there is no different between, 23 and 23.00,0.00 and 0..

But the business peroples is not getting that.. they want to see there two 0's after the decimal inthe output csv file...

Like for exampke...
For 0 it should be 0.00, for 123 it should be 123.00...

Re: Spool decimal value to csv file [message #351546 is a reply to message #351543] Wed, 01 October 2008 08:44 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Mike...

Apreacite your valuable time on my question..

I can defintely change the format of that cell in the csv file now, as long as i am staying in UAT... But this process need to go in the prod, and as we know, in prod everything needs to be automate... I cannot go there and change the format manually...

Once again thanks for you time..
Re: Spool decimal value to csv file [message #351547 is a reply to message #351544] Wed, 01 October 2008 08:47 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
they want to see there two 0's after the decimal inthe output csv file

There are, if you open the file with notepad, you will see there are the two 0.

Regards
Michel
Previous Topic: User vs System Function Execution Time
Next Topic: Rowtype Declaration Problem Within Multiple Schemas (merged)
Goto Forum:
  


Current Time: Fri Dec 02 20:57:19 CST 2016

Total time taken to generate the page: 0.08144 seconds