SQL Developer: Preferences for Delimited Text Exports

thatjeffsmith SQL Developer 14 Comments

Tell Others About This Story:

Yes, it’s true. Some people export their data to formats other than Excel. Another popular format is ‘delimited,’ where fields are defined by a delimiter. The most famous of these is CSV, a comma separated values file.

So your data can be read by another program quite easily because it knows where one field stops and the next begins.

A quick example:

Values are delimited by a comma (,)

Values are delimited by a comma (,)

But I Don’t Want to Use Commas!

Ok, well no need to shout. This is a blog and I can’t hear you over the innertubes. So if you use our grid > export feature, you’ll see options for defining the delimiter.

The drop-down control exposes some predefined delimiters.

The drop-down control exposes some predefined delimiters.

If you don’t like these pre-defined options, you can set your own to whatever you like. Just type it into the field.

You can pick any delimiter you want, even if it doesn't make sense.

You can pick any delimiter you want, even if it doesn’t make sense.

I’ll go with something fun, like an umlaut. So now my formatted grid output looks like this:

[text] "EMPNO"¨"ENAME"¨"JOB"¨"MGR"¨"HIREDATE"¨"SAL"¨"COMM"¨"DEPTNO"
9999¨"ADAMS"¨"CLERK"¨7788¨23-MAY-1987 12.00.00¨1100¨¨20
7369¨"SMITH"¨"CLERK"¨7902¨17-DEC-1980 12.00.00¨800¨¨20
7499¨"ALLEN"¨"SALESMAN"¨7698¨20-FEB-1981 12.00.00¨1600¨300¨30
7521¨"WARD"¨"SALESMAN"¨7698¨22-FEB-1981 12.00.00¨1250¨500¨30
7566¨"JONES"¨"MANAGER"¨7839¨02-APR-1981 12.00.00¨2975¨¨20
7654¨"MARTIN"¨"SALESMAN"¨7698¨28-SEP-1981 12.00.00¨1250¨1400¨30
7698¨"BLAKE"¨"MANAGER"¨7839¨01-MAY-1981 12.00.00¨2850¨¨30
7782¨"CLARK"¨"MANAGER"¨7839¨09-JUN-1981 12.00.00¨2450¨¨10
7788¨"SCOTT"¨"ANALYST"¨7566¨19-APR-1987 12.00.00¨3000¨¨20
7839¨"KING"¨"PRESIDENT"¨¨17-NOV-1981 12.00.00¨5000¨¨10
7844¨"TURNER"¨"SALESMAN"¨7698¨08-SEP-1981 12.00.00¨1500¨0¨30
7876¨"ADAMS"¨"CLERK"¨7788¨23-MAY-1987 12.00.00¨1100¨¨20
7900¨"JAMES"¨"CLERK"¨7698¨03-DEC-1981 12.00.00¨950¨¨30
7902¨"FORD"¨"ANALYST"¨7566¨03-DEC-1981 12.00.00¨3000¨¨20
7934¨"MILLER"¨"CLERK"¨7782¨23-JAN-1982 12.00.00¨1300¨¨10
[/text]

Why Must I Quote the Strings?!?

You don’t have to, that’s another option. In the export dialog there are two fields for defining how strings are enclosed.

I don't want nothing around my text!

I don’t want nothing around my text!

And now my data comes out like this:

[text] EMPNO??ENAME??JOB??MGR??HIREDATE??SAL??COMM??DEPTNO
9999??ADAMS??CLERK??7788??23-MAY-1987 12.00.00??1100????20
7369??SMITH??CLERK??7902??17-DEC-1980 12.00.00??800????20
7499??ALLEN??SALESMAN??7698??20-FEB-1981 12.00.00??1600??300??30
7521??WARD??SALESMAN??7698??22-FEB-1981 12.00.00??1250??500??30
7566??JONES??MANAGER??7839??02-APR-1981 12.00.00??2975????20
7654??MARTIN??SALESMAN??7698??28-SEP-1981 12.00.00??1250??1400??30
7698??BLAKE??MANAGER??7839??01-MAY-1981 12.00.00??2850????30
7782??CLARK??MANAGER??7839??09-JUN-1981 12.00.00??2450????10
7788??SCOTT??ANALYST??7566??19-APR-1987 12.00.00??3000????20
7839??KING??PRESIDENT????17-NOV-1981 12.00.00??5000????10
7844??TURNER??SALESMAN??7698??08-SEP-1981 12.00.00??1500??0??30
7876??ADAMS??CLERK??7788??23-MAY-1987 12.00.00??1100????20
7900??JAMES??CLERK??7698??03-DEC-1981 12.00.00??950????30
7902??FORD??ANALYST??7566??03-DEC-1981 12.00.00??3000????20
7934??MILLER??CLERK??7782??23-JAN-1982 12.00.00??1300????10
[/text]

And now you know, the rest of the story.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 14

  1. Hello,

    I am not sure if this query belongs here but could find some relationship with my queries so proceeding..

    I just have started to use SQLcL (4.2) for my data extraction use case. There are few things which I am wondering how to achieve.

    #1:
    I need to extract the results of a SQL to a flat delimited file. So I used “SET SQLFORMAT DELIMITED |” and it did what I expected but in addition it inserted the right and left enclosures by itself. I understand from SQL developer that there is a option to specify none for enclosure to prevent double quotes being applied as enclosures. Similarly, could you please help me with an option in SQLcL as well.

    #2:
    Is there any LINESIZE limitation with SQLcL as in SQL*plus where 32767 is max ?

    #3:
    Is there a way to SET COLSEP as (hex – 0x01 or unicode – \u001 or ascii – CHR(1)) ?

    Thanks in advance.!

  2. Hi,
    I am currently using SQL Developer Version 4.0.2.15 and I generally connect to this from Citrix Receiver. I have a table in which there are CLOB columns in which multiple lines of data.

    How can I copy this data and put it in Excel sheet with the CLOB data getting stored in a single cell. Currently it is not stored in a single cell.

    If I try to export the file then it is getting stored in Citrix Receiver Server which I don’t have access to.

    Thanks

    1. thatjeffsmith Post
      Author
  3. Hello,
    I Try to use on sqlcl the command set sqlformat delimiter, but with separator ;
    I have tried set colsep ; without any success, always default separator ,

    Any help would be very appreciated.

    Thank you.

  4. Hello,

    I’d like to know if there’s an option to place the field delimiter after the last field, so if i choose the semicolon as a delimiter i’d like that the semicolon appears after the last field, just before the end of line.
    Thanks.

    1. thatjeffsmith Post
      Author
      1. Is the final delimiter something that is planned for a future update? I am trying to switch from TOAD for Oracle to SQL Developer, but that limitation represents a loss of functionality between the two.

        Thanks!

        1. thatjeffsmith Post
          Author
  5. Hi Jeff,
    Sorry to post it here but, this way of exporting data does not work for CLOBS (>4k ). Even the regular export from the result grids also truncates the text. Is there any way to export CLOBS from sql developer? I know toad has option to export and it works.

    1. thatjeffsmith Post
      Author

      When the tool started out, it didn’t support exports of CLOBs at all. We’ve since increased the support to the first 4k characters, and then further for certain export types like XMl, SQL*Loader, and Excel. Now we need to continue that to Text, CSV, and DSV.

      That MIGHT make it into version 4.0…

      1. Hi
        in 4.1.2 and still export table with INSERT format does not take the CLOB column. How to export CLOB including data from sqldeveloper?
        Thaks

        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author

Leave a Reply

Your email address will not be published. Required fields are marked *