asked on

Save Output of SQL Query in CSV file Using SQL *Plus

Right Now I have done

sql > Spool xyz.txt
sql > <<my Query >>

output in file xyz.txt stored as

Column_Name1 Column_Name2
------------------------- ---
ssss66666 PNR
sdfgs6666 PNR
sfdgfsdg6s PNR
sdfg326sd PNR
sdfg30713 PNR
sdfgfsdfg PNR
sdfgsdfgs PNR
sdfgfgfds PNR
G22178 PNR
G22402 PNR

Column_Name1 Column_Name2
------------------------- ---
sdfgsdfgsdf PNR
sfgfsdgfggf PNR
sdfgsdfgdf PNR

Would like to have the output as a comma seperated values like

column1o/p, column2o/p, column1o/p, column2o/p, column1o/p, column2o/p, column1o/p, column2o/pcolumn1o/p, column2o/p, column1o/p, column2o/pcolumn1o/p, column2o/p, column1o/p, column2o/pcolumn1o/p, column2o/p, column1o/p, column2o/pcolumn1o/p, column2o/p, column1o/p, column2o/pcolumn1o/p, column2o/p, column1o/p, column2o/p............so on//

Please help me with such a query so that I can see the output as CSV file or also into an excel file where the entire value can be given as input to SQL Loader and load into another table in another schema

Sean Stuber

you can use colsep to create csv, but it won't concatenate all the values onto a single line

Sean Stuber

set colsep ,
select column1, column2 from yourtable

Ajay Chowdary Kandula

ASKER

good to have you back...sdstuber...

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool distinct_cont_iss.csv
select distinct column1 || ',' || nvl(trim(column2),'PNR')||','
from contract;

spool off

Problem - Column1 defined as char(25 Byte) and data size is only 8 Byte

Giving output of '12345678 '<< How to avoid the spaces and have output as 12345678,PNR,12345678,PNR,12345678,PNR,12345678,PNR... instead of 12345678 ,PNR,12345678 ,PNR,12345678 ,PNR....

please help...........please

Ajay Chowdary Kandula

ASKER

I think the space is fine as the new table in which I would load this data has also the same space defined so no constraints

Thanks for your comment...

Ajay Chowdary Kandula

ASKER

Would like to know any tools that can help to assist me in loading this new CSV file apart from SQL Loader to load the data as I am not able to find SQL Loader.exe in C:\oracle\product\10.2.0\client_1\BIN

Please help

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ajay Chowdary Kandula

ASKER

Solutions frm Experts Exchange always help...