tabla - ¿Cómo puedo hacer un spool en un archivo con formato CSV usando SQLPLUS?

spool off oracle sqlplus

Question

Quiero extraer algunas consultas a un formato de salida CSV. Lamentablemente, no puedo usar ningún cliente SQL sofisticado ni ningún lenguaje para hacerlo. Debo usar SQLPLUS.

¿Cómo lo hago?

Answer 1

Con las versiones más nuevas de las herramientas del cliente, existen múltiples opciones para formatear el resultado de la consulta. El resto es ponerlo en un archivo o guardar el resultado como un archivo dependiendo de la herramienta del cliente. Estas son algunas de las formas:

• SQL * Plus

Usando los comandos SQL * Plus, puede formatear para obtener el resultado deseado. Use SPOOL para transferir la salida a un archivo.

Por ejemplo,

SQL> SET colsep ,
SQL> SET pagesize 20
SQL> SET trimspool ON
SQL> SET linesize 200
SQL> SELECT * FROM scott.emp;

     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE ,       SAL,      COMM,    DEPTNO
----------,----------,---------,----------,---------,----------,----------,----------
      7369,SMITH     ,CLERK    ,      7902,17-DEC-80,       800,          ,        20
      7499,ALLEN     ,SALESMAN ,      7698,20-FEB-81,      1600,       300,        30
      7521,WARD      ,SALESMAN ,      7698,22-FEB-81,      1250,       500,        30
      7566,JONES     ,MANAGER  ,      7839,02-APR-81,      2975,          ,        20
      7654,MARTIN    ,SALESMAN ,      7698,28-SEP-81,      1250,      1400,        30
      7698,BLAKE     ,MANAGER  ,      7839,01-MAY-81,      2850,          ,        30
      7782,CLARK     ,MANAGER  ,      7839,09-JUN-81,      2450,          ,        10
      7788,SCOTT     ,ANALYST  ,      7566,09-DEC-82,      3000,          ,        20
      7839,KING      ,PRESIDENT,          ,17-NOV-81,      5000,          ,        10
      7844,TURNER    ,SALESMAN ,      7698,08-SEP-81,      1500,          ,        30
      7876,ADAMS     ,CLERK    ,      7788,12-JAN-83,      1100,          ,        20
      7900,JAMES     ,CLERK    ,      7698,03-DEC-81,       950,          ,        30
      7902,FORD      ,ANALYST  ,      7566,03-DEC-81,      3000,          ,        20
      7934,MILLER    ,CLERK    ,      7782,23-JAN-82,      1300,          ,        10

14 rows selected.

SQL>

• Versión de desarrollador SQL pre 4.1

Alternativamente, puede usar el nuevo /*csv*/ hint en SQL Developer .

/*csv*/

Por ejemplo, en mi SQL Developer Version 3.2.20.10 :

Ahora puede guardar la salida en un archivo.

• SQL Developer versión 4.1

Nuevo en SQL Developer versión 4.1, use lo siguiente al igual que el comando sqlplus y ejecútelo como script. No hay necesidad de la pista en la consulta.

SET SQLFORMAT csv

Ahora puede guardar la salida en un archivo.

Answer 2

Debe tener en cuenta que los valores de los campos podrían contener comas y caracteres de comillas, por lo que algunas de las respuestas sugeridas no funcionarían, ya que el archivo de salida CSV no sería correcto. Para reemplazar caracteres de cita en un campo y reemplazarlo por el carácter de cita doble, puede usar la función REEMPLAZAR que proporciona oracle, para cambiar una comilla simple por una comilla doble.

set echo off
set heading off
set feedback off
set linesize 1024   -- or some other value, big enough
set pagesize 50000
set verify off
set trimspool on

spool output.csv
select trim(
       ''"''   || replace(col1, ''"'', ''""'') || 
       ''","'' || replace(col2, ''"'', ''""'') ||
       ''","'' || replace(coln, ''"'', ''""'') || ''"'' ) -- etc. for all the columns
from   yourtable
/
spool off

O bien, si desea el carácter de comillas simples para los campos:

set echo off
set heading off
set feedback off
set linesize 1024   -- or some other value, big enough
set pagesize 50000
set verify off
set trimspool on

spool output.csv
select trim(
''"''   || replace(col1, '''''''', '''''''''''') || 
''","'' || replace(col2, '''''''', '''''''''''') ||
''","'' || replace(coln, '''''''', '''''''''''') || ''"'' ) -- etc. for all the columns
from   yourtable
/
spool off

Answer 3

Es crudo, pero:

set pagesize 0 linesize 500 trimspool on feedback off echo off

select ''"'' || empno || ''","'' || ename || ''","'' || deptno || ''"'' as text
from emp

spool emp.csv
/
spool off

Answer 4

Prefiere usar "set colsep" en sqlplus prompt en lugar de editar nombre de columna uno a uno. Usa sed para editar el archivo de salida.

set colsep ''","''     -- separate columns with a comma
sed ''s/^/"/;s/$/"/;s//s *"/"/g;s/"/s */"/g'' $outfile > $outfile.csv

Answer 5

Puede formatear explícitamente la consulta para producir una cadena delimitada con algo como:

select ''"''||foo||''","''||bar||''"''
  from tab

Y configure las opciones de salida según corresponda. Como opción, la variable COLSEP en SQLPlus le permitirá producir archivos delimitados sin tener que generar explícitamente una cadena con los campos concatenados juntos. Sin embargo, tendrá que poner comillas alrededor de las cadenas en cualquier columna que pueda contener caracteres de coma incrustados.

Answer 6

Puede usar csv hint. Vea el siguiente ejemplo:

select /*csv*/ table_name, tablespace_name
from all_tables
where owner = ''SYS''
and tablespace_name is not null;

Answer 7

Sé que este es un hilo viejo, sin embargo, noté que nadie mencionó la opción de subrayado, que puede eliminar los subrayados debajo de los encabezados de las columnas.

set pagesize 50000--50k is the max as of 12c
set linesize 10000   
set trimspool on  --remove trailing blankspaces
set underline off --remove the dashes/underlines under the col headers
set colsep ~

select * from DW_TMC_PROJECT_VW;

Answer 8

Si usa 12.2, simplemente puede decir

set markup csv on

Answer 9

También podría usar lo siguiente, aunque introduce espacios entre campos.

set colsep ,     -- separate columns with a comma
set pagesize 0   -- No header rows
set trimspool on -- remove trailing blanks
set headsep off  -- this may or may not be useful...depends on your headings.
set linesize X   -- X should be the sum of the column widths
set numw X       -- X should be the length you want for numbers (avoid scientific notation on IDs)

spool myfile.csv

select table_name, tablespace_name 
  from all_tables
 where owner = ''SYS''
   and tablespace_name is not null;

La salida será como:

    TABLE_PRIVILEGE_MAP           ,SYSTEM                        
    SYSTEM_PRIVILEGE_MAP          ,SYSTEM                        
    STMT_AUDIT_OPTION_MAP         ,SYSTEM                        
    DUAL                          ,SYSTEM 
...

Esto sería mucho menos tedioso que escribir todos los campos y concatenarlos con las comas. Podría seguir con un simple script sed para eliminar el espacio en blanco que aparece antes de una coma, si lo desea.

Algo como esto podría funcionar ... (mis habilidades sed son muy oxidadas, por lo que es probable que esto necesite trabajo)

sed ''s//s+,/,/'' myfile.csv 

Answer 10

Una vez escribí un pequeño script SQL * Plus que usa dbms_sql y dbms_output para crear un csv (en realidad un ssv). Puedes encontrarlo en mi repositorio githup .

Answer 11

Use vi o vim para escribir el sql, use colsep con un control-A (en vi y vim preceden al ctrl-A con ctrl-v). Asegúrese de establecer lineizesize y pagesize en algo racional y active trimspool y trimout.

arrójalo a un archivo. Entonces...

sed -e ''s/,/;/g'' -e ''s/ *{ctrl-a} */,/g''  {spooled file}  > output.csv

Esa cosa sed puede convertirse en una secuencia de comandos. El "*" antes y después del ctrl-A exprime todos los espacios inútiles. ¿No es genial que se molestaron en habilitar la salida html de sqlplus pero no csv nativa ?????

Lo hago de esta manera porque maneja comas en los datos. Los cambio a punto y coma.

Answer 12

Utilizo este comando para scripts que extraen datos para tablas dimensionales (DW). Entonces, uso la siguiente sintaxis:

set colsep ''|''
set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''''
set trimspool on
set headsep off

spool output.dat

select ''|'', <table>.*, ''|''
  from <table>
where <conditions>

spool off

Y funciona No uso sed para formatear el archivo de salida.

Answer 13

Veo un problema similar ...

Necesito colocar el archivo CSV desde SQLPLUS, pero el resultado tiene 250 columnas.

Lo que hice para evitar el molesto formato de salida SQLPLUS:

set linesize 9999
set pagesize 50000
spool myfile.csv
select x
from
(
select col1||'';''||col2||'';''||col3||'';''||col4||'';''||col5||'';''||col6||'';''||col7||'';''||col8||'';''||col9||'';''||col10||'';''||col11||'';''||col12||'';''||col13||'';''||col14||'';''||col15||'';''||col16||'';''||col17||'';''||col18||'';''||col19||'';''||col20||'';''||col21||'';''||col22||'';''||col23||'';''||col24||'';''||col25||'';''||col26||'';''||col27||'';''||col28||'';''||col29||'';''||col30 as x
from (  
      ...  here is the "core" select
     )
);
spool off

el problema es que perderá los nombres de encabezado de columna ...

puedes agregar esto:

set heading off
spool myfile.csv
select col1_name||'';''||col2_name||'';''||col3_name||'';''||col4_name||'';''||col5_name||'';''||col6_name||'';''||col7_name||'';''||col8_name||'';''||col9_name||'';''||col10_name||'';''||col11_name||'';''||col12_name||'';''||col13_name||'';''||col14_name||'';''||col15_name||'';''||col16_name||'';''||col17_name||'';''||col18_name||'';''||col19_name||'';''||col20_name||'';''||col21_name||'';''||col22_name||'';''||col23_name||'';''||col24_name||'';''||col25_name||'';''||col26_name||'';''||col27_name||'';''||col28_name||'';''||col29_name||'';''||col30_name from dual;

select x
from
(
select col1||'';''||col2||'';''||col3||'';''||col4||'';''||col5||'';''||col6||'';''||col7||'';''||col8||'';''||col9||'';''||col10||'';''||col11||'';''||col12||'';''||col13||'';''||col14||'';''||col15||'';''||col16||'';''||col17||'';''||col18||'';''||col19||'';''||col20||'';''||col21||'';''||col22||'';''||col23||'';''||col24||'';''||col25||'';''||col26||'';''||col27||'';''||col28||'';''||col29||'';''||col30 as x
from (  
      ...  here is the "core" select
     )
);
spool off

Sé que es un poco hardcore, pero funciona para mí ...