Jak skopiować plik w formacie CSV za pomocą SQLPLUS?

Używam tego polecenia do skryptów, które wyodrębniają dane do tabel wymiarowych (DW). Używam więc następującej składni:

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

Widzę podobny problem...

Muszę skopiować plik CSV z SQLPLUS, ale wyjście ma 250 kolumn.

Co zrobiłem, aby uniknąć irytującego formatowania wyjścia 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

Problem polega na tym, że stracisz nazwy nagłówków kolumn...

Możesz dodać to:

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

W nowszych wersjach narzędzi klienckich istnieje wiele opcji formatowania wyjścia zapytania. Reszta polega na skopiowaniu go do pliku lub zapisaniu wyjścia jako pliku w zależności od Narzędzia klienta. Oto kilka sposobów:

• SQL * Plus

Za pomocą poleceń SQL * Plus możesz sformatować, aby uzyskać pożądane wyniki. Użyj SPOOL , aby skopiować wyjście do pliku.

Na przykład,

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>

• SQL Developer Version pre 4.1

Alternatywnie, można użyć nowego /*csv*/ hint in SQL Developer .

/*csv*/

Na przykład w mojej SQL Developer Version 3.2.20.10:

Teraz możesz zapisać wyjście do pliku.

• SQL Developer Version 4.1

Nowość w SQL Developer w wersji 4.1, użyj następującego polecenia podobnie jak polecenie sqlplus i uruchom jako skrypt. Nie ma potrzeby podpowiedzi w zapytaniu.

SET SQLFORMAT csv

Teraz możesz zapisać wyjście do pliku.

Jeśli używasz 12.2, możesz po prostu powiedzieć

set markup csv on

Jest prymitywne, ale:

set pagesize 0 linesize 500 trimspool on feedback off echo off

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

spool emp.csv
/
spool off

Wiem, że to stary wątek, ale zauważyłem, że nikt nie wspomniał o opcji podkreślenia, która może usunąć podkreślenia pod nagłówkami kolumn.

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;

Możesz jawnie sformatować zapytanie, aby utworzyć rozdzielony ciąg znaków z czymś w linii:

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

I ustawić odpowiednie opcje wyjścia. Jako opcja, zmienna COLSEP w SQLPlus pozwoli Ci tworzyć rozdzielone Pliki bez konieczności jawnego generowania łańcucha z polami połączonymi ze sobą. Należy jednak umieszczać cudzysłowy wokół ciągów znaków na kolumnach, które mogą zawierać osadzone znaki przecinków.

Wolą używać "set colsep" w wierszu polecenia sqlplus zamiast edytować nazwę col jeden po drugim. Użyj sed do edycji pliku wyjściowego.

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

Napisałem kiedyś mały skrypt SQL * Plus, który używa dbms_sql i dbms_output do tworzenia pliku csv (właściwie ssv). Możesz go znaleźć w moim repozytorium githup .

Użyj vi lub vim do zapisu sql, użyj colsep Z control-a (w vi i vim poprzedzają ctrl-A Z ctrl-v). Pamiętaj, aby ustawić linesize i pagesize na coś racjonalnego i włączyć trimspool i trimout.

Skopiuj go do pliku. Więc...

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

Robię to w ten sposób, ponieważ obsługuje przecinki w danych. Zamieniam je na półkolonie.

Należy pamiętać, że wartości pól mogą zawierać przecinki i znaki cudzysłowu, więc niektóre sugerowane odpowiedzi nie będą działać, ponieważ plik wyjściowy CSV nie będzie poprawny. Aby zastąpić znaki cudzysłowu w polu i zastąpić je podwójnym znakiem cudzysłowu, można użyć funkcji zastąp, którą zapewnia oracle, aby zmienić pojedynczy cudzysłów na podwójny.

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

Lub, jeśli chcesz pojedynczy znak cudzysłowu dla pól:

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

Możesz użyć podpowiedzi csv. Zobacz następujący przykład:

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