Chcę wyodrębnić niektóre zapytania do formatu wyjściowego CSV. Niestety, nie mogę użyć do tego żadnego wymyślnego klienta SQL ani żadnego języka. Muszę używać SQLPLUS.
Jak mam to zrobić?
Odpowiedzi:
Jeśli używasz wersji 12.2, możesz po prostu powiedzieć
set markup csv on
spool myfile.csv
Możesz również użyć następującego, chociaż wprowadza spacje między polami.
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;
Wynik będzie następujący:
TABLE_PRIVILEGE_MAP ,SYSTEM
SYSTEM_PRIVILEGE_MAP ,SYSTEM
STMT_AUDIT_OPTION_MAP ,SYSTEM
DUAL ,SYSTEM
...
Byłoby to o wiele mniej uciążliwe niż wpisywanie wszystkich pól i łączenie ich przecinkami. Jeśli chcesz, możesz użyć prostego skryptu seda, aby usunąć białe znaki, które pojawiają się przed przecinkiem.
Coś takiego może zadziałać ... (moje umiejętności seda są bardzo zardzewiałe, więc prawdopodobnie będzie to wymagało pracy)
sed 's/\s+,/,/' myfile.csv set pagesize 1000zamiast 0. W moim poprzednim komentarzu, nie można przekierować do tego samego pliku: cat myfile.csv | sed -e 's/[ \t]*|/|/g ; s/|[ ]*/|/g' > my_other_file.csv.
grepi w trten sposób grep -v -- ----- myfile.csv | tr -d [:blank:] > myfile.csv.
Używam tego polecenia dla skryptów, które wyodrębniają dane do tabel wymiarowych (DW). Więc używam 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
I działa. Nie używam seda do formatowania pliku wyjściowego.
Widzę podobny problem ...
Muszę buforować plik CSV z SQLPLUS, ale wynik ma 250 kolumn.
Co zrobiłem, aby uniknąć irytującego formatowania danych wyjściowych 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
Wiem, że to trochę hardcore, ale na mnie działa ...
||również podzapytań?, nie sądzę, że jest to wymagane dla podzapytań. ale tak, jest to wymagane do podstawowego wyboru.
select x? To powinno działać bez niego. @davidb, masz rację, że konkatenacja nie jest wymagana w podstawowym podzapytaniu wewnętrznym, ale aliasowanie wszystkich kolumn jako col1, col2 ... itd. jest tam wymagany.
W nowszych wersjach narzędzi klienckich istnieje wiele opcji formatowania wyników zapytania. Reszta polega na zbuforowaniu go do pliku lub zapisaniu wyniku jako pliku w zależności od narzędzia klienta. Oto kilka sposobów:
Używając poleceń SQL * Plus, możesz sformatować, aby uzyskać żądany wynik. Użyj SPOOL do buforowania danych wyjściowych 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>
Alternatywnie, można użyć nową wskazówkę w SQL Developer ./*csv*/
/*csv*/Na przykład w mojej wersji SQL Developer 3.2.20.10 :
Teraz możesz zapisać wynik do pliku.
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 csvTeraz możesz zapisać wynik do pliku.
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;
To surowe, 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
Możesz jawnie sformatować zapytanie, aby utworzyć ciąg rozdzielany z czymś w rodzaju:
select '"'||foo||'","'||bar||'"'
from tab
I odpowiednio skonfiguruj opcje wyjściowe. Opcjonalnie, zmienna COLSEP w SQLPlus pozwoli Ci tworzyć pliki rozdzielane bez konieczności jawnego generowania łańcucha z połączonymi polami. Będziesz jednak musiał umieścić cudzysłowy wokół łańcuchów we wszystkich kolumnach, które mogą zawierać osadzone znaki przecinka.
Kiedyś napisałem mały skrypt SQL * Plus, który używa dbms_sqli dbms_outputtworzy csv (właściwie ssv). Możesz go znaleźć w moim repozytorium githup .
Należy pamiętać, że wartości pól mogą zawierać przecinki i znaki cudzysłowu, więc niektóre z sugerowanych odpowiedzi nie zadziałają, ponieważ plik wyjściowy CSV nie byłby poprawny. Aby zamienić znaki cudzysłowu w polu i zastąpić je znakiem podwójnego cudzysłowu, możesz użyć funkcji REPLACE udostępnianej przez Oracle, aby zmienić pojedynczy cudzysłów na podwójny cudzysłów.
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 offLub, jeśli potrzebujesz pojedynczego 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 offtrim()jest to konieczne.
Użyj vi lub vim, aby napisać sql, użyj colsep z control-A (w vi i vim poprzedź ctrl-A ctrl-v). Pamiętaj, aby ustawić rozmiar linii i rozmiar strony na coś racjonalnego i włączyć trimspool i trimout.
umieścić go w pliku. Następnie...
sed -e 's/,/;/g' -e 's/ *{ctrl-a} */,/g' {spooled file} > output.csvTo sedno można zamienić w scenariusz. Znak „*” przed i po ctrl-A usuwa wszystkie bezużyteczne spacje. Czy to nie wspaniale, że zadali sobie trud włączenia wyjścia HTML z sqlplus, ale NIE natywnego csv ?????
Robię to w ten sposób, ponieważ obsługuje przecinki w danych. Zamieniam je w średniki.
Wystąpił problem podczas używania sqlplus do tworzenia plików CSV. Jeśli chcesz, aby nagłówki kolumn były wyświetlane tylko raz, a istnieją tysiące lub miliony wierszy, nie możesz ustawić rozmiaru strony na tyle dużego, aby nie uzyskać powtórzenia. Rozwiązaniem jest rozpoczęcie od pagesize = 50 i przeanalizowanie nagłówków, a następnie ponowne wydanie polecenia select z pageize = 0, aby uzyskać dane. Zobacz skrypt bash poniżej:
#!/bin/bash
FOLDER="csvdata_mydb"
CONN="192.168.100.11:1521/mydb0023.world"
CNT=0376
ORD="0376"
TABLE="MY_ATTACHMENTS"
sqlplus -L logn/pswd@//${CONN}<<EOF >/dev/null
set pagesize 50;
set verify off;
set feedback off;
set long 99999;
set linesize 32767;
set trimspool on;
col object_ddl format A32000;
set colsep ,;
set underline off;
set headsep off;
spool ${ORD}${TABLE}.tmp;
select * from tblspc.${TABLE} where rownum < 2;
EOF
LINES=`wc -l ${ORD}${TABLE}.tmp | cut -f1 -d" "`
[ ${LINES} -le 3 ] && {
echo "No Data Found in ${TABLE}."
}
[ ${LINES} -gt 3 ] && {
cat ${ORD}${TABLE}.tmp | sed -e 's/ * / /g' -e 's/^ //' -e 's/ ,/,/g' -e 's/, /,/g' | tail -n +3 | head -n 1 > ./${ORD}${TABLE}.headers
}
sqlplus -L logn/pswd@//${CONN}<<EOF >/dev/null
set pagesize 0;
set verify off;
set feedback off;
set long 99999;
set linesize 32767;
set trimspool on;
col object_ddl format A32000;
set colsep ,;
set underline off;
set headsep off;
spool ${ORD}${TABLE}.tmp;
select * from tblspc.${TABLE};
EOF
LINES=`wc -l ${ORD}${TABLE}.tmp | cut -f1 -d" "`
[ ${LINES} -le 3 ] && {
echo "No Data Found in ${TABLE}."
}
[ ${LINES} -gt 3 ] && {
cat ${ORD}${TABLE}.headers > ${FOLDER}/${ORD}${TABLE}.csv
cat ${ORD}${TABLE}.tmp | sed -e 's/ * / /g' -e 's/^ //' -e 's/ ,/,/g' -e 's/, /,/g' | tail -n +2 | head -n -1 >> ${FOLDER}/${ORD}${TABLE}.csv
}Napisałem ten czysty skrypt SQLPlus, aby zrzucić tabele do CSV w 1994 roku.
Jak zauważono w komentarzach do skryptu, ktoś z Oracle umieścił mój skrypt w notatce Oracle Support, ale bez podania źródła.
https://github.com/jkstill/oracle-script-lib/blob/master/sql/dump.sql
Skrypt buduje również plik sterujący i plik parametrów dla SQL * LOADER
spool D:\test.txt
select * from emp
spool offMożesz użyć podpowiedzi CSV. Zobacz poniższy przykład:
select /*csv*/ table_name, tablespace_name
from all_tables
where owner = 'SYS'
and tablespace_name is not null;