OraFAQ Forum: Client Tools » Sql*Plus Spool file issue with .csv file format

Home » Other » Client Tools » Sql*Plus Spool file issue with .csv file format (oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sql*Plus Spool file issue with .csv file format [message #414594]

Wed, 22 July 2009 18:12

kumar29
Messages: 6
Registered: July 2009

Junior Member

Hi,
I executed a SQL statement and capturing the results to a spool file. The file is .csv format. After complete the execution I open the csv file and observed that an extra space is padding for each column
I tried to put the set operations as
Set serveroutput on
Set lines 5000
Set feedback off;
Set heading off;
Set trimspool off;
Set pages 0;
Set trim on;
Set trims on;
Set colsep ","
Spool c:\Result.csv
Select col1, col2 .......
/
Spool off
/
Exit
/

Could you please advise what might be causing this problem with .csv format and how to handle it? Thanks for your time.

Report message to a moderator

Re: Sql*Plus Spool file issue with .csv file format [message #414595 is a reply to message #414594]

Wed, 22 July 2009 18:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I open the csv file and observed that an extra space is padding for each column
Why is this a problem that needs to be solved?

Report message to a moderator

Re: Sql*Plus Spool file issue with .csv file format [message #414596 is a reply to message #414595]

Wed, 22 July 2009 18:24

kumar29
Messages: 6
Registered: July 2009

Junior Member

I don't want to see the extra blank spaces after each field.when we try to load this .csv file into a database and its failing.

Report message to a moderator

Re: Sql*Plus Spool file issue with .csv file format [message #414597 is a reply to message #414594]

Wed, 22 July 2009 19:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed

>its failing.
If you say so.
Most folks, including me, can't solve a problem we can't see.

Report message to a moderator

Re: Sql*Plus Spool file issue with .csv file format [message #414605 is a reply to message #414596]

Wed, 22 July 2009 22:23

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL*Plus pads the column to tha max size by default, you can use COL command to give a lentgh but anyway all values will be padded to this length.
If you don't want the columne to be padded you have to build the expression by yourself col1||';'||col2...

Regards
Michel

[Edit: Fix typo]

[Updated on: Thu, 23 July 2009 08:33]

Report message to a moderator

Re: Sql*Plus Spool file issue with .csv file format [message #414792 is a reply to message #414605]

Thu, 23 July 2009 08:20

kumar29
Messages: 6
Registered: July 2009

Junior Member

Thank you very much.
I tried your option and its worked as expected.I would like to tweak litte more to get more accurancy in data. I used comma(,) as concatination.There may be chance in my data like address fields has , . I thought of use any other character while doing concatination.
Any thoughts with out preparing own string as column1||','||column2||','||column3 ... and remove the padding space for each column while spooling to a .csv file.
Please let me know if you any input from my side.
Thanks again for your time.

[Updated on: Thu, 23 July 2009 08:22]

Report message to a moderator