Oracle SQL：导出到CSV避免换行 [英] Oracle SQL: Export to CSV avoiding newlines

问题描述

我需要做一个批处理，以便在csv文件中每天导出一些数据库的一些数据。
实际上，我使用SET COLSEP命令，但我有一些问题，导出一个大的大小为4000（VARCHAR2：4000）：它返回给我许多空白行和换行符的输出文件。 p>

我将尝试更好地解释一个例子：

  QUERY 
 ----- 
 
设置NEWPAGE 0 
设置空格0 
设置ECHO关闭
设置反馈关闭
设置HEADING OFF 
 SET PAGESIZE 0 
 SET LINESIZE 2000 
 SET COLSEP; 
 
 SPOOL test.csv 
 
 
 SELECT 
 C.COLUMN1，
 C.COLUMN2，
 C.COLUMN3， -  - 有问题的列
 C.COLUMN4，
 FROM TABLE1 
 WHERE CONDITION1 = TRUE; 
 
 SPOOL OFF 
 
 exit 
  

是我的csv文件上的输出：

  OUTPUT 
 ------ 
 $ b b + --------- + --------- + --------------------------- + --------- + 
 | COLUMN1; COLUMN2; COLUMN3; COLUMN4 | 
 + --------- + --------- + ------------------------- -  + --------- + 
 | VALUE1_1; VALUE1_2; Lorem ipsum dolor sit amet; VALUE1_4 | 
 + --------- + --------- + ------------------------- -  + --------- + 
 | consectetur adipiscing elit | 
 + --------- + --------- + ------------------------- -  + --------- + 
 | Donec a diam lectus | 
 + --------- + --------- + ------------------------- -  + --------- + 
 | VALUE2_1; VALUE2_2; Sed sit amet ipsum mauris; VALUE2_4 | 
 + --------- + + --------- + ------------------------- -  + --------- + 
 | | 
 + --------- + --------- + ------------------------- -  + --------- + 
 | Donec a diam lectus | 
 + --------- + --------- + ------------------------- -  + --------- + 
 | VALUE3_1; VALUE3_2; ; VALUE2_4 | 
 + --------- + --------- + ------------------------- -  + --------- + 
 | Pellentesque auctor nisi | 
 + --------- + --------- + ------------------------- -  + --------- + 
 | Donec a diam lectus | 
 + --------- + --------- + ------------------------- -  + --------- + 
  

正如你所看到的，在字段COLUMN3上的换行符字符，所以我想如果有一些有用的命令，如SET SOMETHING帮助我在这种情况下。

感谢所有的支持。

Luca

解决方案

  SELECT 
 C.COLUMN1，
 C.COLUMN2，
 replace（C.COLUMN3，CHR（10），null）AS COLUMN3，
 C.COLUMN4，
 FROM TABLE1 C 
 WHERE CONDITION1 = TRUE; 
  

由于这里的新行是因为数据，你必须在select查询中删除它们本身！

I need to do a batch to export daily some data of a DB in a "csv" file. Acually I'm using the SET COLSEP command but I have some problem to export a big coumn which have size 4000 (VARCHAR2 : 4000): it gives back to me many blank lines and newlines on the output file.

I'll try to better explain with an example:

QUERY
-----

SET NEWPAGE 0
SET SPACE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 2000
SET COLSEP ";"

SPOOL test.csv


SELECT 
  C.COLUMN1,
  C.COLUMN2,
  C.COLUMN3, -- column with issue
  C.COLUMN4,
FROM TABLE1 
WHERE CONDITION1 = TRUE;

SPOOL OFF

exit

And there is the output on my csv file:

OUTPUT
------

+---------+---------+---------------------------+---------+
| COLUMN1 ; COLUMN2 ;          COLUMN3          ; COLUMN4 |
+---------+---------+---------------------------+---------+
| VALUE1_1;VALUE1_2 ;Lorem ipsum dolor sit amet ;VALUE1_4 |
+---------+---------+---------------------------+---------+
|                    consectetur adipiscing elit          |
+---------+---------+---------------------------+---------+
|                    Donec a diam lectus                  |
+---------+---------+---------------------------+---------+
| VALUE2_1;VALUE2_2 ;Sed sit amet ipsum mauris  ;VALUE2_4 |
+---------+---------+---------------------------+---------+
|                                                         |
+---------+---------+---------------------------+---------+
|                    Donec a diam lectus                  |
+---------+---------+---------------------------+---------+
| VALUE3_1;VALUE3_2 ;                           ;VALUE2_4 |
+---------+---------+---------------------------+---------+
|                    Pellentesque auctor nisi             |
+---------+---------+---------------------------+---------+
|                    Donec a diam lectus                  |
+---------+---------+---------------------------+---------+

As you can see, I have many newlines character on the field "COLUMN3" so I guess if there is some useful command like SET SOMETHING to help me in this situation.

Thanks to all for the the support.

Luca

解决方案

SELECT 
  C.COLUMN1,
  C.COLUMN2,
  replace(C.COLUMN3,CHR(10),null) AS COLUMN3, 
  C.COLUMN4,
FROM TABLE1 C
WHERE CONDITION1 = TRUE;

As the new line here is because of the data, you have to probably remove them in your select query itself!

这篇关于Oracle SQL：导出到CSV避免换行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！