Como eu faço spool para um arquivo formatado em CSV usando o SQLPLUS?

Eu quero extrair algumas consultas para um formato de saída CSV. Infelizmente, não posso usar nenhum cliente SQL sofisticado ou qualquer linguagem para fazê-lo. Eu devo usar o SQLPLUS.

Como eu faço isso?

    Você também pode usar o seguinte, embora introduza espaços 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; 

    Saída será como:

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

    Isso seria muito menos tedioso do que digitar todos os campos e concatená-los com as vírgulas. Você poderia seguir com um script sed simples para remover o espaço em branco que aparece antes de uma vírgula, se você quisesse.

    Algo como isso pode funcionar … (minhas habilidades sed são muito enferrujadas, então isso provavelmente vai precisar de trabalho)

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

    Eu uso este comando para scripts que extrai dados para tabelas dimensionais (DW). Então, eu uso a seguinte syntax:

     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 '|', .*, '|' from 
    where spool off

    E funciona. Eu não uso sed para formatar o arquivo de saída.

    Eu vejo um problema semelhante …

    Eu preciso spool arquivo CSV do SQLPLUS, mas a saída tem 250 colunas.

    O que eu fiz para evitar a formatação de saída chata do 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 

    o problema é que você perderá os nomes dos headers das colunas …

    você pode adicionar isto:

     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 

    Eu sei que é meio hardcore, mas funciona pra mim …

    Com versões mais recentes de ferramentas do cliente, existem várias opções para formatar a saída da consulta. O restante é fazer spool para um arquivo ou salvar a saída como um arquivo, dependendo da ferramenta do cliente. Aqui estão algumas das maneiras:

    • SQL * Plus

    Usando os comandos do SQL * Plus você pode formatar para obter a saída desejada. Use SPOOL para fazer o spool da saída para um arquivo.

    Por exemplo,

     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> 
    • Versão do desenvolvedor do SQL pré 4.1

    Como alternativa, você pode usar o novo /*csv*/ hint no SQL Developer .

     /*csv*/ 

    Por exemplo, no meu SQL Developer Version 3.2.20.10 :

    insira a descrição da imagem aqui

    Agora você pode salvar a saída em um arquivo.

    • SQL Developer versão 4.1

    Novo no SQL Developer versão 4.1, use o seguinte como o comando sqlplus e execute como script. Não há necessidade da dica na consulta.

     SET SQLFORMAT csv 

    Agora você pode salvar a saída em um arquivo.

    Se você estiver usando 12.2, você pode simplesmente dizer

     set markup csv on 

    Eu sei que este é um segmento antigo, no entanto, notei que ninguém mencionou a opção de sublinhado, que pode remover os sublinhados sob os títulos das colunas.

     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; 

    É bruto, mas:

     set pagesize 0 linesize 500 trimspool on feedback off echo off select '"' || empno || '","' || ename || '","' || deptno || '"' as text from emp spool emp.csv / spool off 

    Você pode formatar explicitamente a consulta para produzir uma string delimitada com algo ao longo das linhas de:

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

    E configure as opções de saída conforme apropriado. Como uma opção, a variável COLSEP no SQLPlus permite produzir arquivos delimitados sem ter que gerar explicitamente uma string com os campos concatenados juntos. No entanto, você terá que colocar aspas em torno de seqüências de caracteres em quaisquer colunas que possam conter caracteres de vírgula incorporados.

    prefira usar “set colsep” no prompt do sqlplus ao invés de editar o nome do arquivo um por um. Use sed para editar o arquivo de saída.

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

    Uma vez eu escrevi um pequeno script SQL * Plus que usa dbms_sql e dbms_output para criar um csv (na verdade, um ssv). Você pode encontrá-lo no meu repository githup .

    Use o vi ou o vim para escrever o sql, use o colsep com um controle-A (no vi e vim preceda o ctrl-A com um ctrl-v). Certifique-se de definir o tamanho da linha e tamanho da página para algo racional e ativar trimspool e trimout.

    spool-lo para um arquivo. Então…

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

    Essa coisa sed pode ser transformada em um script. O “*” antes e depois do ctrl-A espreme todos os espaços inúteis. Não é ótimo que eles se incomodaram em habilitar a saída html do sqlplus, mas NÃO csv nativo ?????

    Eu faço desta maneira porque ele trata vírgulas nos dados. Eu os transformo em ponto e vírgula.

    Você deve estar ciente de que os valores dos campos podem conter vírgulas e caracteres de cotação, portanto, algumas das respostas sugeridas não funcionariam, pois o arquivo de saída CSV não estaria correto. Para replace caracteres de cotação em um campo e substituí-lo pelo caractere de aspas duplas, é possível usar a function REPLACE fornecida pelo oracle para alterar uma aspa simples para aspas duplas.

     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 

    Ou, se você quiser o caractere de aspas simples para os 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 

    Você poderia usar csv dica. Veja o seguinte exemplo:

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