Home » SQL & PL/SQL » Client Tools » Sql*Plus Spool file issue with .csv file format (oracle)
icon5.gif  Sql*Plus Spool file issue with .csv file format [message #414594] Wed, 22 July 2009 18:12 Go to next message
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.

Re: Sql*Plus Spool file issue with .csv file format [message #414595 is a reply to message #414594] Wed, 22 July 2009 18:19 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
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?
Re: Sql*Plus Spool file issue with .csv file format [message #414596 is a reply to message #414595] Wed, 22 July 2009 18:24 Go to previous messageGo to next message
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.
Re: Sql*Plus Spool file issue with .csv file format [message #414597 is a reply to message #414594] Wed, 22 July 2009 19:06 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
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.
Re: Sql*Plus Spool file issue with .csv file format [message #414605 is a reply to message #414596] Wed, 22 July 2009 22:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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

Re: Sql*Plus Spool file issue with .csv file format [message #414853 is a reply to message #414792] Thu, 23 July 2009 16:30 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
See attachement (the link listed is dead).
Re: Sql*Plus Spool file issue with .csv file format [message #414856 is a reply to message #414594] Thu, 23 July 2009 17:14 Go to previous message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>when we try to load this .csv file into a database and its failing.

Into what make & model of database is this data being loaded?
Previous Topic: Oracle ADI - Force Excel 2003
Next Topic: sqlplus instant client
Goto Forum:
  


Current Time: Fri Aug 29 19:43:34 CDT 2014

Total time taken to generate the page: 0.27172 seconds