如何使用 SQLPLUS 将假脱机转换为 CSV 格式的文件？

我想提取一些查询到一个 CSV 输出格式。不幸的是，我不能使用任何花哨的 SQL 客户端或任何语言来做到这一点。我必须使用 SQLPLUS。

我该怎么做？

626047 次浏览
小开

可以显式地对查询进行格式化，以生成一个带分隔符的字符串，具体内容如下:

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

并设置适当的输出选项。作为一个选项，SQLPlus 上的 COLSEP 变量将允许您生成带分隔符的文件，而不必显式地生成字符串，并将字段连接在一起。但是，对于任何可能包含嵌入逗号字符的列，都必须在字符串周围加引号。

小开

很粗糙，但是:

set pagesize 0 linesize 500 trimspool on feedback off echo off


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


spool emp.csv
/
spool off
小开

您还可以使用以下内容，尽管它确实在字段之间引入了空格。

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 技能已经非常生疏，所以这可能需要改进)

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

我看到了类似的问题。

我需要假脱机从 SQLPLUS 的 CSV 文件，但输出有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

我知道这有点难，但对我很有用。

小开

更喜欢在 sqlplus 提示符中使用“ set colsep”，而不是逐个编辑 colname。使用 sed 编辑输出文件。

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

对于为维度表(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 格式化输出文件。

小开

我曾经写过一个小的 SQL * Plus 脚本，它使用 dbms_sql和 dbms_output创建一个 csv (实际上是一个 ssv)。你可以找到它 在我的 Githup 仓库里。

小开

使用 vi 或 vim 编写 sql，使用 colsep 和 control-A (vi 和 vim 中的 ctrl-A 前面加一个 ctrl-v)。一定要设置线条大小和页面大小的东西合理，并打开裁剪和修剪。

把它卷到一个文件中。 然后..。

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

那个 sed 可以变成一个脚本。Ctrl-A 前面和后面的“ *”将挤出所有无用的空格。他们不厌其烦地从 sqlplus 启用 html 输出，而不是本地的 csv，这不是很好吗？

我这样做是因为它处理数据中的逗号，我把它们转换成分号。

小开

对于较新版本的客户端工具，有多个选项可以格式化查询输出。其余的工作是根据客户端工具将其假脱机到一个文件或将输出保存为一个文件。以下是一些方法:

  • SQL * Plus

使用 SQL * Plus 命令可以格式化以获得所需的输出。使用 线轴将输出假脱机到文件中。

比如说,

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之前

或者，您可以在 SQL 开发人员中使用新的 /*csv*/提示。

/*csv*/

例如，在我的 SQL 开发器版本3.2.20.10中:

enter image description here

现在您可以将输出保存到一个文件中。

  • SQL 开发人员版本4.1

SQL Developer 版本4.1中的新版本，可以像 sqlplus 命令那样使用以下命令并作为脚本运行。查询中不需要提示。

SET SQLFORMAT csv

现在您可以将输出保存到一个文件中。

小开

您可以使用 csv 提示:

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

我知道这是一个老线程，但是我注意到没有人提到下划线选项，它可以删除列标题下的下划线。

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;
小开

您应该意识到字段的值可能包含逗号和引号字符，因此一些建议的答案不起作用，因为 CSV 输出文件不正确。 若要替换字段中的引号字符，并将其替换为双引号字符，可以使用 Oracle 提供的 REPLACE 函数将单引号更改为双引号。

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
小开
最佳答案

如果您使用的是12.2，您可以简单地说

set markup csv on
spool myfile.csv
小开

使用 sqlplus 创建 csv 文件有一个问题。如果希望列标题在输出中只出现一次，并且有数千或数百万行，则不能将页面大小设置得足够大以避免重复。 解决方案是从 pagesize = 50开始，解析出头文件，然后再次发出 select，使用 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
}
小开

我在1994年编写了这个纯 SQLPlus 脚本来将表转储到 CSV。

正如脚本注释中指出的那样，Oracle 的某人将我的脚本放在了 Oracle 支持注释中，但是没有署名。

Https://github.com/jkstill/oracle-script-lib/blob/master/sql/dump.sql

该脚本还为 SQL * LOADER 构建了一个控制文件和一个参数文件

小开 
spool D:\test.txt


select * from emp
    

spool off

提交答案