Menu Home

SQLDeveloper: Exporting query results as CSV

This post will show you how to use the SQLFORMAT command from SQL Developer to transform a query output to CSV.

Out of the box CSV

Enter the following into SQL Developer, I am using SQL Developer version 18.1 and execute it as a script by pressing F5.

set sqlformat csv
select e.employee_id, 
       e.last_name, 
       e.salary
  from employees e
 where e.department_id = 50;

and here is the output:

"EMPLOYEE_ID","LAST_NAME","SALARY"
198,"OConnell",2600
199,"Grant",2600
120,"Weiss",8000
121,"Fripp",8200
122,"Kaufling",7900
...

What customisation options are available?

The SQLFORMAT DELIMITED option can be used to change the delimiter and the enclosures. The command takes the following format:

SET SQLFORMAT DELIMITED <delimiter> <left enclosure> <right enclosure> 

Here is the same query as above changed to use # as the delimiter and single quote enclosures.

set sqlformat csv
set sqlformat delimited # ' '
select e.employee_id, 
       e.last_name, 
       e.salary
  from employees e
 where e.department_id = 50;
'EMPLOYEE_ID'#'LAST_NAME'#'SALARY'
198#'OConnell'#2600
199#'Grant'#2600
120#'Weiss'#8000
121#'Fripp'#8200
122#'Kaufling'#7900

Alternatives to SQLFORMAT?

Check out the answers to this StackOverflow question.

Need more help?

Please get in touch and I will be glad to help.

Acknowledgements

The SQLcl documentation where you can find other formats that are supported by the SQLFORMAT command such as JSON.

Categories: Oracle Oracle Database SQL SQL Developer

oraclefrontovik

Developer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.