Oracle SQL: Export to CSV avoiding newlines

csv, oracle, sql

Solution

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!

Problem

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

Original source