Как мне спулировать файл в формате CSV с помощью SQLPLUS?
Я хочу извлечь некоторые запросы в выходной формат CSV. К сожалению, я не могу использовать какой-либо причудливый клиент SQL или любой другой язык для этого. Я должен использовать SQLPLUS.
Как мне это сделать?
13 ответов
Вы также можете использовать следующее, хотя оно вводит пробелы между полями.
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;
Выход будет как:
TABLE_PRIVILEGE_MAP ,SYSTEM
SYSTEM_PRIVILEGE_MAP ,SYSTEM
STMT_AUDIT_OPTION_MAP ,SYSTEM
DUAL ,SYSTEM
...
Это было бы намного менее утомительно, чем печатать все поля и соединять их запятыми. Вы можете использовать простой скрипт для удаления пробелов, которые появляются перед запятой, если хотите.
Нечто подобное может сработать... (мои навыки работы с sed очень ржавые, так что это, вероятно, потребует работы)
sed 's/\s+,/,/' myfile.csv
Если вы используете 12.2, вы можете просто сказать
set markup csv on
Я использую эту команду для сценариев, которые извлекают данные для таблиц измерений (DW). Итак, я использую следующий синтаксис:
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
И работает. Я не использую sed для форматирования выходного файла.
Я вижу похожую проблему...
Мне нужно спулировать файл CSV из SQLPLUS, но вывод имеет 250 столбцов.
Что я сделал, чтобы избежать раздражающего форматирования вывода 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
проблема в том, что вы потеряете имена заголовков столбцов...
Вы можете добавить это:
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
Я знаю, что это хардкор, но у меня это работает...
В более новых версиях клиентских инструментов существует несколько вариантов форматирования вывода запроса. Остальное - спулингировать в файл или сохранить результат в виде файла, в зависимости от клиентского инструмента. Вот несколько способов:
- SQL*Plus
Используя команды SQL*Plus, вы можете отформатировать, чтобы получить желаемый результат. Используйте SPOOL длябуферизации вывода в файл.
Например,
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 до 4.1
В качестве альтернативы, вы можете использовать новый/*csv*/ подсказка в SQL Developer.
/*csv*/
Например, в моем SQL Developer версии 3.2.20.10:
Теперь вы можете сохранить вывод в файл.
- SQL Developer Версия 4.1
Новое в SQL Developer версии 4.1, используйте следующую команду sqlplus и запустите ее как скрипт. Нет необходимости в подсказке в запросе.
SET SQLFORMAT csv
Теперь вы можете сохранить вывод в файл.
Я знаю, что это старая тема, однако я заметил, что никто не упомянул опцию подчеркивания, которая может удалить подчеркивания под заголовками столбцов.
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;
Это грубо, но:
set pagesize 0 linesize 500 trimspool on feedback off echo off
select '"' || empno || '","' || ename || '","' || deptno || '"' as text
from emp
spool emp.csv
/
spool off
Вы можете явно отформатировать запрос, чтобы получить строку с разделителями с чем-то вроде:
select '"'||foo||'","'||bar||'"'
from tab
И настройте параметры вывода соответствующим образом. Как вариант, переменная COLSEP в SQLPlus позволит вам создавать файлы с разделителями без явной генерации строки с объединенными полями. Однако вам придется заключать в кавычки строки в любых столбцах, которые могут содержать встроенные запятые.
Предпочитайте использовать "set colsep" в приглашении sqlplus вместо редактирования имени col по одному. Используйте sed для редактирования выходного файла.
set colsep '","' -- separate columns with a comma
sed 's/^/"/;s/$/"/;s/\s *"/"/g;s/"\s */"/g' $outfile > $outfile.csv
Однажды я написал небольшой скрипт SQL*Plus, который использует dbms_sql а также dbms_output создать CSV (на самом деле SSV). Вы можете найти его в моем хранилище githup.
Вы должны знать, что значения полей могут содержать запятые и кавычки, поэтому некоторые из предложенных ответов не будут работать, так как выходной файл CSV будет неправильным. Чтобы заменить символы кавычки в поле и заменить их символом двойной кавычки, вы можете использовать функцию REPLACE, которую предоставляет oracle, чтобы заменить одинарную кавычку на двойную кавычку.
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
Или, если вы хотите использовать символ одинарной кавычки для полей:
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
Используйте vi или vim для написания sql, используйте colsep с элементом управления-A (в vi и vim перед ctrl-A стоит ctrl-v). Обязательно установите размер линий и размер страниц на что-то рациональное и включите тримпул и триммер.
спул это в файл. Затем...
sed -e 's/,/;/g' -e 's/ *{ctrl-a} */,/g' {spooled file} > output.csv
Эта вещь sed может быть превращена в сценарий. " *" До и после ctrl-A сжимает все ненужные пробелы. Разве это не здорово, что они потрудились включить вывод html из sqlplus, но НЕ нативный csv?????
Я делаю это таким образом, потому что он обрабатывает запятые в данных. Я превращаю их в точки с запятой.
Я написал этот чисто SQLPlus-скрипт для дампа таблиц в CSV в 1994 году.
Как отмечено в комментариях к сценарию, кто-то в Oracle поместил мой сценарий в заметку службы поддержки Oracle, но без указания авторства.
https://github.com/jkstill/oracle-script-lib/blob/master/sql/dump.sql
Сценарий также создает управляющий файл и файл параметров для SQL*LOADER.
Существует проблема с использованием sqlplus для создания CSV-файлов. Если вы хотите, чтобы заголовки столбцов выводились только один раз, а строк насчитывалось тысячи или миллионы, вы не можете установить размер страницы достаточно большим, чтобы не получить повтор. Решение состоит в том, чтобы начать с pageize = 50 и разобрать заголовки, а затем снова выполнить выбор с pagesize = 0, чтобы получить данные. Смотрите скрипт bash ниже:
#!/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
}
Вы могли бы использовать подсказку CSV. Смотрите следующий пример:
select /*csv*/ table_name, tablespace_name
from all_tables
where owner = 'SYS'
and tablespace_name is not null;