CODEWITHSUNDEEP
sqloracle-databasecsv
Lc0rE
Lc0rE

Reputation: 2356

Oracle SQL: Export to CSV avoiding newlines

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

Upvotes: 4

Views: 7122

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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!

Upvotes: 9

Related Questions