oracle 在 sqlplus 中后台处理 csv 文件时的标题格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17980038/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Header formatting while spooling a csv file in sqlplus
提问by Tech_Coder
I am required to spool a csv from a table in Oracle, using sqlplus. Following is the format required:
我需要使用 sqlplus 从 Oracle 中的表中缓冲 csv。以下是所需的格式：
"HOST_SITE_TX_ID","SITE_ID","SITETX_TX_ID","SITETX_HELP_ID"
"664436565","16","2195301","0"
"664700792","52","1099970","0"
Following is the relevant piece of the shell script I wrote:
以下是我编写的 shell 脚本的相关部分：
sqlplus -s $sql_user/$sql_password@$sid << eof >> /dev/null
set feedback off
set term off
set linesize 1500
set pagesize 11000
--set colsep ,
--set colsep '","'
set trimspool on
set underline off
set heading on
--set headsep $
set newpage none
spool "$folder$filename$ext"
select '"'||PCL_CARRIER_NAME||'","'||SITETX_EQUIP_ID||'","'||SITETX_SITE_STAT||'","'||SITETX_CREATE_DATE||'","'||ADVTX_VEH_WT||'"'
from cvo_admin.MISSING_HOST_SITE_TX_IDS;
spool off
(I have used some commented statements in, to signify the things that I tried but couldn't get to work)
（我在其中使用了一些注释语句来表示我尝试过但无法工作的事情）
The output I receive is:
我收到的输出是：
'"'||PCL_CARRIER_NAME||'","'||SITETX_EQUIP_ID||'","'||SITETX_SITE_STAT||'","'||SITETX_CREATE_DATE||'","'||ADVTX_VEH_WT||'"'
"TRANSPORT INC","113","00000000","25-JAN-13 10.17.51 AM",""
"TRANSPORT INC","1905","00000000","25-JAN-13 05.06.44 PM","0"
Which shows that the header is messed up - it is literally printing the whole string that should have been interpreted as an sql statement, as is the case with the data displayed.
这表明标题搞砸了 - 它实际上打印了应该被解释为 sql 语句的整个字符串，就像显示的数据一样。
Options I am considering:
我正在考虑的选项：
1) Using colsep
1) 使用 colsep
set colsep '","'
spool
select * from TABLE
spool off
This introduces other problems as the data having leading and trailing spaces, first and the last values in the files are not enclosed by quotes
这引入了其他问题，因为数据具有前导和尾随空格，文件中的第一个和最后一个值没有用引号括起来
HOST_SITE_TX_ID"," SITE_ID"
" 12345"," 16"
" 12345"," 21
I concluded that this method gives me more heartburn than the one I described earlier.
我得出的结论是，这种方法比我之前描述的方法更让我胃灼热。
2) Getting the file and use a regex to modify the header.
2）获取文件并使用正则表达式修改标题。
3) Leaving the header altogether and manually adding a header string at the beginning of the file, using a script
3）完全离开标题并使用脚本在文件开头手动添加标题字符串
Option 2 is more doable, but I was still interested in asking, if there might be a better way to format the header somehow, so it comes in a regular csv, (comma delimited, double quote bounded) format.
选项 2 更可行，但我仍然有兴趣问，是否有更好的方法来格式化标题，所以它采用常规 csv，（逗号分隔，双引号有界）格式。
I am looking to do as less hard coding as possible - the table I am exporting has around 40 columns and I am currently running the script for around 4 million records - breaking them in a batch of around 10K each. I would really appreciate any suggestions, even totally different from my approach - I am a programmer in learning.
我希望尽可能减少硬编码——我导出的表有大约 40 列，我目前正在运行大约 400 万条记录的脚本——将它们分成大约 10K 的批次。我真的很感激任何建议，甚至与我的方法完全不同 - 我是一名正在学习的程序员。
回答by higuita
One easy way to have a csv with just one header is to do
有一个只有一个标题的 csv 的一种简单方法是
set embedded on
set pagesize 0
set colsep '|'
set echo off
set feedback off
set linesize 1000
set trimspool on
set headsep off
the embeddedis a hidden option but it is important to have JUST one header
这embedded是一个隐藏的选项，但只有一个标题很重要
回答by the_slk
This is how I created a header:
这就是我创建标题的方式：
set heading off
/* header */
SELECT '"'||PCL_CARRIER_NAME||'","'||SITETX_EQUIP_ID||'","'||SITETX_SITE_STAT||'","'||SITETX_CREATE_DATE||'","'||ADVTX_VEH_WT||'"'
FROM
(
SELECT 'PCL_CARRIER_NAME' AS PCL_CARRIER_NAME
, 'SITETX_EQUIP_ID' AS SITETX_EQUIP_ID
, 'SITETX_SITE_STAT' AS SITETX_SITE_STAT
, 'SITETX_CREATE_DATE' AS SITETX_CREATE_DATE
, 'ADVTX_VEH_WT' AS ADVTX_VEH_WT
FROM DUAL
)
UNION ALL
SELECT '"'||PCL_CARRIER_NAME||'","'||SITETX_EQUIP_ID||'","'||SITETX_SITE_STAT||'","'||SITETX_CREATE_DATE||'","'||ADVTX_VEH_WT||'"'
FROM
(
/* first row */
SELECT to_char(123) AS PCL_CARRIER_NAME
, to_char(sysdate, 'yyyy-mm-dd') AS SITETX_EQUIP_ID
, 'value3' AS SITETX_SITE_STAT
, 'value4' AS SITETX_CREATE_DATE
, 'value5' AS ADVTX_VEH_WT
FROM DUAL
UNION ALL
/* second row */
SELECT to_char(456) AS PCL_CARRIER_NAME
, to_char(sysdate-1, 'yyyy-mm-dd') AS SITETX_EQUIP_ID
, 'value3' AS SITETX_SITE_STAT
, 'value4' AS SITETX_CREATE_DATE
, 'value5' AS ADVTX_VEH_WT
FROM DUAL
) MISSING_HOST_SITE_TX_IDS;
