Import data to CSV file

[venur]

Hi,

I am trying to load data from tables into a CSV file. I am able to do it but single table at a time. Is there a way I can import multiple tables using single procedure or script.

This is what I am doing

Table 1 :

set term off;
set heading off;
set linesize 400;
set feedback off;
spool ecashdesposittransaction_sep_04.csv;
SELECT
K.ECASHID ||','|| K.DEPOSITTRANSACTIONID
FROM
ECASHDEPOSITTRANSACTION K
spool off;

Table 2:

set term off;
set heading off;
set linesize 400;
set feedback off;
spool ecashouttran_sep_04.csv;
SELECT
N.ECASHID ||','|| N.CASHOUTTRANID
FROM
ECASHCASHOUTTRAN N
spool off;

and so on .......... executing one script after the other

Expecting some help !!!!

Thanks,
venu

 

[Turkbear]

Hi,
Have you tried:

set term off;
set heading off;
set linesize 400;
set feedback off;
spool ecashdesposittransaction_sep_04.csv;   
SELECT
   K.ECASHID ||','|| K.DEPOSITTRANSACTIONID
FROM
   ECASHDEPOSITTRANSACTION K;
spool off;
spool ecashouttran_sep_04.csv;   
SELECT
   N.ECASHID ||','|| N.CASHOUTTRANID
FROM
   ECASHCASHOUTTRAN N;
spool off;

and so on....

 

[carp]

Or try:

set term off;
set heading off;
set linesize 400;
set feedback off;
SET COLSEP ','
SPOOL table1.csv
SELECT * FROM table_1;
SPOOL OFF
SPOOL table2.csv
SELECT * FROM table_2;
SPOOL OFF
etc

 

[sem]

Just a small adding: you don't need semicolon after sql*plus commands:

set term off
set heading off
set linesize 400
set feedback off
spool ecashdesposittransaction_sep_04.csv



Regards, Dima

 

[Turkbear]

Hi, Just a clarification of the difference between carp's method and mine ( at least what mine should have said)..
With Select * ( using the colsep set to ',') the comma-delimited fields will each be as long as their format definition regardless of how much actual data is in the field , so, a field defined as varchar2(30) that has 'Smith' stored, will be

"Smith                         ",

in the output CSV; this may result in very long line sizes.
My example should have used Trim for each field

SELECT
   Rtrim(K.ECASHID) ||','|| Rtrim(K.DEPOSITTRANSACTIONID)
FROM
   ECASHDEPOSITTRANSACTION K;

and that would eliminate the padding of the field lengths.

Both work, just a difference in size.

 

[venur]

Hi,

Thank you and i appricate. I have one more question. When I am executing the above query the out put is some thing similar to this in the csv file.

SQL> SELECT
2 K.ECASHID ||' '|| K.DEPOSITTRANSACTIONID
3 FROM
4 POKERWRK.ECASHDEPOSITTRANSACTION K;

1 2
2 2
3 2
4 5

and so on and an empty line after every 10th record it prints and at the end SQL> SPOOL off;

Is there are way I can stop the Query getting printed on to the csv file and those empty lines too .

Thanks,
venu

 

[SantaMufasa]

Venu,

From the SQL*Plus prompt enter:

SET PAGESIZE 0

This prevents the page breaking that causes the blank line.

Dave

 

[Turkbear]

HI,
Add the
set pagesize 0
to the code provided to use it as a script.
As to the sql and spool off , I cannot remember if these
can be avoided...