ThatJeffSmith

SQL Developer: Preferences for Delimited Text Exports

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:

"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

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:

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

And now you know, the rest of the story.