从 oracle 假脱机到 csv 时避免列周围的双引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38968666/
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
Avoid double quotes around column while spooling to csv from oracle
提问by kten
I need to export a select query output to text file using spool. The output by default adds double quotes around the column. How to Prevent it? The query returns only one column.
我需要使用假脱机将选择查询输出导出到文本文件。默认情况下，输出会在列周围添加双引号。如何预防？查询仅返回一列。
Ideally i need to remove headers as well as the double quotes from the output. below is the script file i use in oracle Developer. but heading is also not removed.
理想情况下，我需要从输出中删除标题以及双引号。下面是我在 oracle Developer 中使用的脚本文件。但标题也没有被删除。
set echo off
SET HEADING OFF
SET PAGESIZE 0
SET COLSEP ''
spool 'D:\public\cvs_txPCG.txt'
select /*csv*/
pcg from temptx;
spool off;
output
输出
"PCG"
"76259737020150320000504281565213310052440093515652109.2909.290101"
"19519905620160502000504283153419040044861008644759203.3903.390101"
"49424051620160220000504284594314590009220713032964404.3804.380202"
"88761197020151025000504284594315180036700812132964401.9901.990101"
回答by Alex Poole
You can't get rid of the double-quotes with the /*csv*/directive. The slightly more convenient way of doing this now is with the sqlformatcommand, but even with sqlformat delimitedyou have to have the string enclosed.
你不能用/*csv*/指令去掉双引号。现在稍微更方便的方法是使用sqlformat命令，但即使使用，sqlformat delimited您也必须将字符串括起来。
Using CSV format for a single column seems rather pointless anyway though, unlessyou have delimiters in your string you want to escape. If you are really exporting that one column and don't have delimiters then just leave out the /*csv*/directive. If you have multiple columns or strings that may contain delimiters then the enclosures are useful to stop those delimiters being misinterpreted when opening in Excel or some other external tool.
无论如何，对单个列使用 CSV 格式似乎毫无意义，除非您想转义字符串中的分隔符。如果您真的要导出那一列并且没有分隔符，则只需省略该/*csv*/指令。如果您有多个可能包含分隔符的列或字符串，那么在 Excel 或其他一些外部工具中打开时，附件可用于阻止这些分隔符被误解。
If you really want CSV format without enclosures you can either build up the output manually by concatenating the column(s) with comma literals, which still allows you to spool to a file:
如果你真的想要没有附件的 CSV 格式，你可以通过将列与逗号文字连接来手动构建输出，这仍然允许你假脱机到一个文件：
select pcg ||','|| some_other_col ||','|| etc from ...
Or you can run the query using Run Statement (Ctrl-Enter) and then export the results from the data grid, from the context menu that appears when you right-click on the output. When you export and choose CSV or delimited format you can choose whether to include the header and which delimiter and enclosures to use - and you're allowed to choose 'none'. If you don't want to set that on each export you can set it as a default, from Tools->Preferences:
或者，您可以使用 Run Statement (Ctrl-Enter) 运行查询，然后从数据网格中导出结果，从右键单击输出时出现的上下文菜单中导出。当您导出并选择 CSV 或分隔格式时，您可以选择是否包含标题以及要使用的分隔符和附件 - 您可以选择“无”。如果您不想在每次导出时都设置它，您可以从工具->首选项将其设置为默认值：
You can't do it with the /*csv*/directive or sqlformat csvthough.
你不能用/*csv*/指令来做到这一点sqlformat csv。
回答by Venkat
I know it is too old but got the answer with my search today, simply use
我知道它太旧了，但今天通过我的搜索得到了答案，只需使用
set markup csv on quote off
select 1 id, 'Venkat' name from dual;
Output will be
输出将是
ID,NAME
1,Venkat
Didn't even think of any other SET commands
甚至没有想到任何其他 SET 命令
回答by Rene
If the double quotes are part of the data you can use the trim() function to remove them.
如果双引号是数据的一部分，您可以使用 trim() 函数删除它们。
select /*csv*/
trim(both '"' from pcg) as pcg from temptx;
Oracle documentation for the trim function: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions199.htm
修剪功能的 Oracle 文档：https: //docs.oracle.com/cd/B19306_01/server.102/b14200/functions199.htm
