264

I'm using Oracle SQL Developer 3.0. Trying to figure out how to export a query result to a text file (preferably CSV). Right clicking on the query results window doesn't give me any export options.

Ken Liu
  • 22,503
  • 19
  • 75
  • 98

6 Answers6

401

Version I am using

Oracle SQL Developer Version 3.0.02; Build MAIN-02.37

Update 5th May 2012

Jeff Smith has blogged showing, what I believe is the superior method to get CSV output from SQL Developer. Jeff's method is shown as Method 1 below:

Method 1

Add the comment /*csv*/ to your SQL query and run the query as a script (using F5 or the 2nd execution button on the worksheet toolbar)

select /*csv*/ *
from emp;

Screenshot of SQL developer executing the SQL statement above as a script showing the output automatically formatted as valid CSV.

That's it.

You can also use spool to automatically save it as a CSV file:

spool "/path/to/file.csv";
select /*csv*/ *
from emp;
spool off;

Just be sure to "Run as Script" or press F5.

Method 2

Run a query

alt text

Right click and select unload.

Update. In Sql Developer Version 3.0.04 unload has been changed to export Thanks to Janis Peisenieks for pointing this out

alt text

Revised screen shot for SQL Developer Version 3.0.04

enter image description here

From the format drop down select CSV

alt text

And follow the rest of the on screen instructions.

starball
  • 20,030
  • 7
  • 43
  • 238
Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
  • 25
    +1. The term "Unload" seems anti-intuitive to me. To "Load" some data means to insert it into the database; therefore "Unload" should mean the data is deleted... – Jeffrey Kemp Nov 13 '10 at 07:22
  • 3
    Also note method 2 can run into a bug (still present in my version 3.0.04) where it just hangs with large row count (3K rows or so). I'm using this old school SQLPLUS cause I didn't see method 1 above first, but some may like this: {code} SET UNDERLINE OFF SET COLSEP ',' SET LINES 20000 PAGES 20000 SET FEEDBACK off --optional SET HEADING off Spool C:\Export\EMP.csv --Now the query SELECT * FROM EMP; Spool OFF – Jim P Jan 10 '13 at 15:36
  • I'm using the same method but I've noticed that the query is executed 2nd time on when export selected. Is there any way to avoid that and export in one step? For heavy queries double querying is quite a no-go. – topr Oct 21 '13 at 12:51
  • 2
    @topr Use method 1, then select all, copy and paste into a text editor and save as csv. You might even be able to paste directly into Excel, but I'm not sure about that. – Travis Jan 03 '14 at 20:08
  • the first method of using CSV is not working. It displays the same output as query result. My query is : select /*csv*/* from employee; Method2 is working fine. – Gunjan Shah Apr 03 '14 at 07:38
  • 1
    From your comment it looks like there is no space after the end of the comment. What happens when you try the following. SELECT /*csv*/ * FROM employee; – Ian Carpenter Apr 03 '14 at 08:25
  • In version 4.0.2, right-clicking on the query gives neither an export nor unload option. Has anyone figured this out? – half-pass Nov 25 '14 at 23:33
  • 2
    @half-pass I don't have access to 4.02. In 4.03 it works as described in method 2. The /*csv*/ method doesn't create a file. You copy the results from the script output tab and paste into the target file\app – Ian Carpenter Nov 26 '14 at 07:41
  • 1
    Please use text instead of images, this answer is useless to me because imgur.com is blocked at my workplace. – Slogmeister Extraordinaire Nov 26 '14 at 17:12
  • @SlogmeisterExtraordinaire: Each screen shot is complemented with text, so perhaps instead of saying the answer is useless you could say which step or what exactly you are struggling to understand. – Ian Carpenter Nov 26 '14 at 19:28
  • @IanCarpenter Really? All of the steps. Try reading this answer with no images. The accompanying text does nothing. For example, "Version I am using" - image. "Add the comment /*csv*/ to your SELECT" - image. Where to add this string? It is good practice not to use images in answers unless absolutely necessary particularly because some people have to work in overly restrictive environments. – Slogmeister Extraordinaire Dec 04 '14 at 01:17
  • Note: Method 1 applies to execution as a script (icon with a page and a green arrow, F5) while Method 2 is when you execute a single query (just the green arrow, F9). The first method with a single query execution will display in a table anyway. – Eric Darchis Nov 25 '16 at 10:03
  • 1
    Oracle: making the simple complex since 1977 – Niall Connaughton Dec 30 '16 at 20:53
  • @NiallConnaughton: you should see how "easy" this is in SQL Server Management Studio. Believe me, this is way easier. – Greg Burghardt Mar 09 '22 at 19:37
53

Not exactly "exporting," but you can select the rows (or Ctrl-A to select all of them) in the grid you'd like to export, and then copy with Ctrl-C.

The default is tab-delimited. You can paste that into Excel or some other editor and manipulate the delimiters all you like.

Also, if you use Ctrl-Shift-C instead of Ctrl-C, you'll also copy the column headers.

Hulk1991
  • 3,079
  • 13
  • 31
  • 46
BQ.
  • 9,393
  • 3
  • 25
  • 35
  • 7
    awesome, i was looking specifically on how to copy headers. thanks! – mishkin Jun 27 '12 at 20:09
  • 3
    The only issue, if you have a lot of rows, this will mean it will have to query the entire dataset again. and if it's a long running query, this means you wait a lot for the first page, and then wait a lot for all pages after pressing ctrl+A. in other words, great solution, but works only most of the time, and for relatively faster or smaller queries. – Eran Medan Nov 09 '12 at 18:50
  • On long row count it will strip your copied stuff! Do not use this. – Hash Aug 29 '16 at 11:31
  • @Mark Never seen that happen myself. Did it only keep the first 50 rows or something like that? If so there's a separate preference for that. It only copies what's currently in the grid, but if you scroll to the bottom of the grid SQL Dev will automatically fetch more rows, so I can see how someone might interpret that as a bug. – BQ. Aug 29 '16 at 11:45
  • All of the rows are fethed, and all of them is selected. (In my example ~43k rows) after copy there is only 300 rows on my clipborad. The only reason I do not recommend this solution is because you could not be sure if all of the rows are there. – Hash Aug 29 '16 at 11:56
  • 2
    I tried doing this on 43K rows of data and it just runs forever--export option way quicker. This is awesome if you have just a few rows of data. – izikandrw Feb 14 '17 at 01:53
31

FYI, you can substitute the /*csv*/ for other formats as well including /*xml*/ and /*html*/. select /*xml*/ * from emp would return an xml document with the query results for example. I came across this article while looking for an easy way to return xml from a query.

Yves Rochon
  • 1,492
  • 16
  • 28
6

FYI to anyone who runs into problems, there is a bug in CSV timestamp export that I just spent a few hours working around. Some fields I needed to export were of type timestamp. It appears the CSV export option even in the current version (3.0.04 as of this posting) fails to put the grouping symbols around timestamps. Very frustrating since spaces in the timestamps broke my import. The best workaround I found was to write my query with a TO_CHAR() on all my timestamps, which yields the correct output, albeit with a little more work. I hope this saves someone some time or gets Oracle on the ball with their next release.

BoeroBoy
  • 1,094
  • 11
  • 17
  • 1
    My version 3.0.04 still has a bug where it just hangs with larger exports (mine is 3K rows). My simple fix was to use SQLPLUS instead: – Jim P Jan 10 '13 at 15:39
  • In my case the Spatial Data Type (http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objrelschema.htm) were being exported like this (notice the commas) `MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(-122.39096,37.79251,NULL),NULL,NULL)` without being wrapped in quotes. And I'm using dynamic SQL so I can't TO_CHAR() these columns. Any suggestions? – Nate Anderson Jul 22 '15 at 22:22
  • Even though I'm using Dynamic SQL and just selecting *, (and I don't want to modify this for an exception for a single SQL column of Spatial datatypes), maybe I could try two things: 1) Use a different COLSEP like '|', or 2) Before the SELECT statement, specify how this column should be formatted using http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12013.htm – Nate Anderson Jul 22 '15 at 22:29
4

To take an export to your local system from sql developer. 

Path : C:\Source_Table_Extract\des_loan_due_dtls_src_boaf.csv

    SPOOL "Path where you want to save the file"
    SELECT /*csv*/ * FROM TABLE_NAME;
Arpan Saini
  • 4,623
  • 1
  • 42
  • 50
1

CSV Export does not escape your data. Watch out for strings which end in \ because the resulting \" will look like an escaped " and not a \. Then you have the wrong number of " and your entire row is broken.

Anonymous
  • 11,748
  • 6
  • 35
  • 57
John
  • 11
  • 1
  • That's probably not a bug - you can decide how quotes are escaped, and the default is to escape it with another quote character, not a backslash. In that case, `"foo\"` is a perfectly valid quoted string. – Ken Williams May 30 '14 at 14:04
  • It's simple enough to replace every occurence of \ with \\, if you know about it in advance. Thanks! – jpaugh Dec 30 '15 at 20:01
  • 1
    Yes, simple enough and likely wrong for most CSV import tools. – Wormbo Jun 02 '16 at 09:45