Skip to Main Content
  • Questions

  • header record for comma delimited file using sql developer

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mark.

Asked: May 09, 2013 - 9:07 pm UTC

Last updated: May 10, 2013 - 4:42 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

I have to create a comma delimited file using sql developer 1.5.5
I'm using this syntax to generate the output:

select spriden_id||','||spriden_first_name||','||spriden_last_name
from spriden
where spriden_pidm = 1012;

The company I'm sending the file to wants a header record at the top of the file that has all the field names I'm selecting. What's the syntax to create the header record so it looks like this:

ID,First_Name,Last_Name

Thanks!!

and Tom said...

http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/

how about that :)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

missing one column header

Mark Underwood, May 10, 2013 - 4:29 pm UTC

It kind of worked, but it left off the 1st column heading of the select statement. Here's the syntax for the select:
select /*csv*/ first_name, last_name, id as banner_id, etc.

The output looks like this:
"LAST_NAME","BANNER_ID","EMAIL", etc.
"Mia","Tate","G0012345",

Any idea why it's leaving off the first_name column header even though it's returning data for that column? Also, is it standard csv format to have the quotes around the data?
Tom Kyte
May 10, 2013 - 4:42 pm UTC

I asked Jeff Smith to follow up and he's replied below!

How to get CSV in SQL Developer without the quoted strings

Jeff Smith, May 10, 2013 - 4:39 pm UTC

I can't explain the missing column header with the /*csv*/ comment in SQL Developer - that works for me in 11.2.0.3 DB with SQL Developer 3.2.20_09.

However, regarding the quoted strings, if you instead execute the statement normally, you can use the grid, right click, Export and set string enclosures for CSV files to null.

I go into this in detail and with an example here
http://www.thatjeffsmith.com/archive/2013/04/sql-developer-preferences-for-delimited-text-exports/
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.