英文:
How to deal with comma and line break when generating csv file using plsql
问题
我有一个包含换行和逗号的varchar2列。然而,当使用PL/SQL生成CSV文件时,逗号的存在会导致数据跳到下一列,而换行会导致数据跳到下一行。不幸的是,我还没有找到解决方案来保持所需内容在同一列中。请有人帮助我解决这个问题吗?提前谢谢。
我varchar2列中的示例格式:
{
"AAA": "BBB",
"CCC": "DDD"
}
REPLACE(',', ',')没有起作用。
英文:
I have a varchar2 column that contains line breaks and commas. However, when generating a CSV file using PL/SQL, the presence of commas causes the data to jump to the next column, and line breaks cause the data to jump to the next row. Unfortunately, I haven't been able to find a solution to maintain the desired content in the same column. Could someone please assist me in resolving this issue? Thank you in advance.
example format in my varchar2:
{
"AAA": "BBB",
"CCC": "DDD"
}
REPLACE(',', ',') did not work.
答案1
得分: 1
SQL*Plus主要是一个命令工具和一个基本的报告工具。它并不是为了强大的编程数据传输而设计的,但通过一些努力可以使其适应这个需求。
在你的情况下,由于换行符也会干扰你可能要通过管道传递文件数据进行清理的基于行的Unix工具,所以很有用的是在数据源处将它们移除,并在数据达到最终目的地之前保持不变。
为此,可以将任何干扰SQL*Plus输出的特殊字符替换为它不会对其进行任何操作的字符,但这些字符不能出现在你的数据中。设备控制字符几乎不会被任何东西使用,因此是很好的选择:
SELECT TRANSLATE(mytext,CHR(10)||',',CHR(19)||CHR(20))
FROM table
然后在消费端,无论在哪里,都要反向进行翻译。你可以在Unix中使用tr
或sed
,或者如果是另一个Oracle数据库:
TRANSLATE(mytext,CHR(19)||CHR(20),CHR(10)||',')
你还可以使用嵌套的REPLACE
,以及你确定不会在数据中出现的任何其他字符。
当然,还要确保将linesize
、long
、longchunksize
、pagesize
、header
等设置为适当的值,以获得干净的输出。
英文:
SQL*Plus is primarily a command tool and a rudimentary report tool. It wasn't designed for robust programmatic data feeds, but it can be adapted to produce them with a bit of effort.
In your case, since line breaks also mess up line-based Unix tools that you might be piping file data through for cleansing, it's useful to get them out of your data at the source and keep them out until it reaches it's final destination.
To do this, replace any special characters that mess up SQL*Plus output with characters it won't do anything with, but which cannot occur in your data. Device control characters are used by virtually nothing so are good candidates:
SELECT TRANSLATE(mytext,CHR(10)||',',CHR(19)||CHR(20))
FROM table
Then on the consuming end, wherever that is, reverse the translation. You can use tr
or sed
in Unix, or if it's another Oracle database:
TRANSLATE(mytext,CHR(19)||CHR(20),CHR(10)||',')
You can use nested REPLACE
s as well, and any other character you are sure won't occur in your data.
Of course, also make sure you set linesize
, long
, longchunksize
, pagesize
, header
, etc.. and the -S
flag to appropriate values to get clean output.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论