How to export query result to csv in Oracle SQL De

2019-01-02 17:10发布

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.

6条回答
何处买醉
2楼-- · 2019-01-02 17:28

Version I am using

alt text

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)

enter image description here

That's it.

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.

查看更多
若你有天会懂
3楼-- · 2019-01-02 17:33

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.

查看更多
零度萤火
4楼-- · 2019-01-02 17:39

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.

查看更多
何处买醉
5楼-- · 2019-01-02 17:45

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.

查看更多
君临天下
6楼-- · 2019-01-02 17:50

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;
查看更多
人间绝色
7楼-- · 2019-01-02 17:53

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.

查看更多
登录 后发表回答