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:
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.
If you don’t like these pre-defined options, you can set your own to whatever you like. Just type it into the field.
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.
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.
19 Comments
I’m using sql developer on a Mac and my dropdown for Delimter is read-only. There is currently a comma in the field and I cannot change it. Any idea what would make this field read only?
Make sure you’re on the Delimited Format and not the Comma Separate Value (CSV) Format page.
CSV is hard coded to commas. But Delimited Format lets you pick anything you want.
close , but not perfekt.
let´ s say you have three irish names in a row
O’Reilly,O’Hara,O’Neill
how do you Export these?
Thanks for you fast Reply
Marco from Munich ( Home of the beer :–)
PS: I love SQL Devloper, and i’m working more then 5 hours / day with it …
Hi Jeff,
i have Columns containing a string quote. i would like to use q‘!string!‘ for each col. is this possible?
Thanks , Marco
You mean like this?
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.!
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
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.
That’s an excel question, yes?
This worked for me.
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.
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.
No, you would have to manually append that to the last column in your query a la
[sql]
select a, b, … n || ‘;’
from table;
[/sql]
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!
Not currently
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.
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…
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
Try this.
I meant how export clob table with INSERT format .
Not other formats
I don’t believe it’s supported. CLOBs can be extremely large. LOBs aren’t supported for all of the EXPORT types. INSERT is one of them.