生成CSV文件时如何处理逗号和换行符的问题,使用PLSQL。

huangapple go评论78阅读模式
英文:

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中使用trsed,或者如果是另一个Oracle数据库:

TRANSLATE(mytext,CHR(19)||CHR(20),CHR(10)||',')

你还可以使用嵌套的REPLACE,以及你确定不会在数据中出现的任何其他字符。

当然,还要确保将linesizelonglongchunksizepagesizeheader等设置为适当的值,以获得干净的输出。

英文:

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 REPLACEs 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.

huangapple
  • 本文由 发表于 2023年6月6日 08:00:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/76410661.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定