oracle SQLPlus 导出为 CSV（输出格式问题）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4826681/
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
SQLPlus export to CSV (output format problem)
提问by Laurent
I'm facing an issue with an interface script, supposed to export the content of some table of an ORACLE database into CSV file, which is then followed by an import of those CSV into a MYSQL database.
我面临一个接口脚本的问题，应该将 ORACLE 数据库的某个表的内容导出到 CSV 文件中，然后将这些 CSV 导入到 MYSQL 数据库中。
STEP1: SQLPlus export to CSV
STEP1：SQLPlus 导出到 CSV
set headsep off
set heading off
set term off
set echo off
SET RECSEPCHAR \n
set pagesize 0
set linesize 0
trimspool on
SET FEEDBACK OFF
spool as_ex_feature.csv
select '"AS'||'"|"'||feature_group||'"|"'||feature_desc||'"|"
||feature_order||'"|"'||prod_code||'"'
from MYVIEW WHERE MYCONDITIONS;
spool off;
-> this step is generating the CSV file, but the format seems incorrect, as I can find some carriage return in the output. Also you'll see in STEP2 that we define an "ENCLOSED BY" value how could I get that one included in the export format (doesn't seem to be the case right now).
-> 这一步正在生成 CSV 文件，但格式似乎不正确，因为我可以在输出中找到一些回车符。此外，您将在 STEP2 中看到我们定义了一个“ENCLOSED BY”值，我如何才能将其包含在导出格式中（现在似乎不是这种情况）。
STEP 2: MYSQL load
第 2 步：MYSQL 加载
LOAD DATA INFILE 'mycsvfile' REPLACE INTO TABLE `mt_feature`
FIELDS TERMINATED BY '|'
ENCLOSED BY '"'
ESCAPED BY '\'
LINES TERMINATED BY '\n';
This script had to be rebuilt for some technical reasons and the Mysql part had not been changed and is working fine with a proper CSV file to import.
由于某些技术原因，必须重新构建此脚本，并且 Mysql 部分没有更改，并且可以正常使用正确的 CSV 文件导入。
The issue seem to be coming from that SQLPlus export, where I need to admit I don't have much knowledge on. Maybe I should use another method to get those files generated?
问题似乎来自 SQLPlus 导出，我需要承认我对此知之甚少。也许我应该使用另一种方法来生成这些文件？
Please let me know if you need additional details, I feel blind...
如果您需要更多详细信息，请告诉我，我感到盲目...
Script running on oracle 10g, Linux, Mysql 4.x
在 oracle 10g、Linux、Mysql 4.x 上运行的脚本
Thanks!
谢谢！
回答by Alex Poole
SET LINESIZE 0isn't valid, the value has to be between 1 and 32767. So I imagine it's wrapping the content at the default line length, which is 80 unless you've already got it set in a gloginscript.
SET LINESIZE 0无效，该值必须在 1 到 32767 之间。所以我想它是以默认行长度包装内容，除非您已经在glogin脚本中设置了它，否则该长度为 80 。
回答by symcbean
If you prefix any lines of code with (at least) four spaces in SO then it'll be formatted correctly, e.g.
如果你在任何代码行前面加上（至少）四个空格，那么它就会被正确格式化，例如
select "AS'||'"|"'||
feature_group||'"|"'||
feature_desc||'"|"'||
feature_order||'"|"'||
prod_code||'"'
from MYVIEW
WHERE MYCONDITIONS;
Sounds like you may need to replace any embedded newline chars in the stored data....
听起来您可能需要替换存储数据中的任何嵌入换行符....
SELECT "AS'||'"|"'||
TRANSLATE(feature_group, CHR(10), '\n') ||'"|"'||
(etc).
And I'm not sure about setting the linesize to 0.
而且我不确定是否将 linesize 设置为 0。
