Search code examples
csvoracle-sqldeveloper

How to have "NULL" instead of "" in export csv file


I would like to have "NULL" in export csv file using sql developer .

So now I can do it like this:

enter image description here

but want to have like this:

enter image description here

How can I achieve this?

Edit: Table I'm using and one query I'm talking about (with some data changed):

CREATE EXAMPLE_TABLE
   (    "ID" NUMBER(15,0), 
    "NAME" VARCHAR2(200 BYTE), 
    "DESCRIPTION" VARCHAR2(500 BYTE), 
    "CATEGORY" VARCHAR2(200 BYTE), 
    "CLASS_NAME" VARCHAR2(200 BYTE), 
    "ATA_CHAPTER" VARCHAR2(200 BYTE), 
    "MEL_NUMBER" NUMBER(15,0), 
    "MEL_CODE" VARCHAR2(50 BYTE), 
    "MEL_TEXT" VARCHAR2(200 BYTE), 
    "MODIFIED_TIME" TIMESTAMP (6), 
    "MODIFIED_BY" VARCHAR2(40 BYTE) DEFAULT '$SYSTEM', 
    "AIRCRAFT_GROUP_ID" NUMBER(15,0), 
    "ORDERING" NUMBER(15,0), 
    "EDITABLE" NUMBER(1,0) DEFAULT 1, 
    "OPT_SCOPE" VARCHAR2(5 BYTE), 
    "REMOVABLE" NUMBER(1,0) DEFAULT 1, 
    "MEL_PENALTY" VARCHAR2(50 BYTE)
   )

Insert into EXAMPLE_TABLE(ID,NAME,DESCRIPTION,CATEGORY,CLASS_NAME,ATA_CHAPTER,MEL_NUMBER,MEL_CODE,MEL_TEXT,MODIFIED_TIME,MODIFIED_BY,AIRCRAFT_GROUP_ID,ORDERING,EDITABLE,OPT_SCOPE,REMOVABLE,MEL_PENALTY) values ('1712','TORA','SAD','RUNWAY','SAD',null,null,null,null,null,'$SYSTEM$','521','28','0','SAD','0',null);

After Littlefoot select

enter image description here

  • for the first column lack of ""

Solution

  • Looks like a NVL might help, e.g.

    SQL> select ename, nvl(to_char(comm), 'NULL') comm
      2  from emp
      3  where rownum <= 2;
    
    ENAME      COMM
    ---------- ----------------------------------------
    SMITH      NULL
    ALLEN      300
    
    SQL>
    

    I used to_char as well (because the comm column's datatype is number). If you don't have such a situation, even better.


    If you want to enclose all values into double quotes, then concatenate them to column values, e.g.

    SQL> set heading off
    SQL> set linesize 200
    SQL> set colsep ','
    SQL>
    SQL> select
      2  '"' || name        || '"',
      3  '"' || description || '"',
      4  '"' || mel_number  || '"',
      5  '"' || aircraft_group_id || '"'
      6  from example_table;
    
    "TORA"      ,"SAD"       ,""                                        ,"521"
    
    SQL>
    

    If you use Oracle 12.2, you can set markup csv on and see whether it does any good in your case.