Spool a CSV file from Oracle/ SQL*Plus without quotes

oracle spool csv

Quite useful, I had the case where I needed to provide a CSV file to an external party from our Oracle 10g database. No problem, there are loads of CSV export tools out there. But it then transpired that the CSV’s that I was generating were using quote (“) marks to identify fields e.g. “field1″,”field2” and so on…

When I ran the script in something like SQL Developer it would spool the file and also spool the query as well into the file no matter what I specified. Along with this it also wouldn’t recognise usage of variables in SQL Developer. Basically what I’m saying here is write the query in something like SQL Developer by all means but for writting and debugging a CSV spooler etc… just save the hassle and go straight to SQL*Plus.

First of all lets start with how to spool the CSV file without leading line breaks, no quote marks etc… the below is the SQL script to run, we’ll need to save this in order to run it later.

[ad#Google Ad in content]

set echo off
set verify off
set termout on
set heading off
set pages 50000
set feedback off
set newpage none
set linesize 160
spool /path/save/file/myFile.csv
SELECT 'Field1Name,Field2Name,Field3Name,Field4Name,Field5Name' from dual;
SELECT
field1||','||
field2||','||
field3||','||
field4||','||
field5
FROM
table1
WHERE
field1='etc...';
spool off;
exit;

We first set any settings we need at the start for the script these are as follows:

set echo off [Won’t display any SQL commands that are run]
set verify off [If turned on, it prints out each defined variable twice]
set termout on [Supresses the output of SQL commands but not the commands themselves]
set heading off [Sets whether column headings are outputted or not]
set pages 50000 [50000 lines without a page break, basically sets the height of the page]
set feedback off [If on it will feedback to user e.g. table created, row deleted etc..]
set newpage none [Removes any leading/ blank lines at the start of a page]
set linesize 200 [Sets the width of a line]

[ad#Google Ad in content]

We then start to build our file by calling the spool function with the directory of where to save the file and the file name. To get our headings or column names we then do a simple select statement on dual where each item will be what we want the column to be named – basically to print out the headings to the spool file. We could of course just set heading to ‘on’ and get the column names, but then column names are not always that useful.

We then run our query as normal, the only thing to note is the use of ||’,’|| which instead of double quotes it will still get used as the field separator, you can use ; or tabs for instance instead. The || || is actually used in Oracle for concatenation of fields to allow 2 strings to be used with an additional element imbetween – it also saves us writing CONCAT() for every 2 strings/ columns. If you do not specify this parameter after each column you select then the default double quotes will be used around your fields. We then turn spool off and then exit so when our .sql file is run it will stop and break the connection.

That’s our SQL script, we want to run this script in command prompt because we can launch SQL*Plus with various parameters and switches, the one we need here is silent,  -s, which tells SQL*Plus not to show the dialogue and thus not to spool it.

The command to run your script will then be something like:

sqlplus -s {schema_name}/{schema_pwd}@{service_name} @test.sql

That lauches SQL*Plus in silent mode, connects to your schema and then runs the .sql script spooling your .csv file and finally exiting SQL*Plus.

Quick note, if you want to append your filename with todays date for instance then you can do something like the below where we assign the date to a variable, &mydate and set it with the value from your databases sysdate.

...
set linesize 160
column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;
spool /path/to/save/file/&mydate.myFile.csv
SELECT 'Field1Name,Field2Name,Field3Name,Field4Name,Field5Name' from dual;
...

12 Comments

  • Nice post – proved very helpful for a report I needed to create. Thanks! Just something to add on your filename tip that I pulled from another source. If you have the variable at the end of the filename (myFile_&mydate.csv), you can end up with an odd name (myFile_&mydate.csv.LST). Something about SQL Plus not knowing where the variable ends. To fix this, you need to include an escape character at the end of the variable name.

    set escape /
    SET linesize 160;

    COLUMN dcol new_value mydate noprint
    SELECT to_char(sysdate,'YYYYMMDD') dcol FROM dual;
    spool c:\temp\myFile_&mydate/.csv
    set escape off;

    SELECT 'Field1Name,Field2Name,Field3Name,Field4Name,Field5Name' FROM dual;

    Any character works for the escape that you aren’t using between where you set the escape and set it off. The slash worked for me, but I had to set escape off after the spool command as I was using it in my query.

    Thanks again!

    • Thanks James. Glad it helped you out and it’s not just me! 🙂

      Interesting thing about the escape line and a good spot. I’m going to add that in on all my variables although I’ve not had the issue yet, but then I wouldn’t like to have the problem either.

  • Thanks! This is helpfull.

  • I need to delete a file which I created using spool option. I need to recreate the same file again using spool command.
    May I know the command for deleting the file.?

    Thx
    ajay

    • Spool will actually overwrite the file so no need to delete.

  • the code provide by you is not working please provide me with a technique to export a table from sql to .csv file format.

  • Hi,

    How can I run the spool script within a loop using a dynamic variable being used as the table name? Here, we have the table names in date format where every day a new table is created. So, to extract data from the respective date wise tables, I intend to call the spool script passing the parameter in the loop and generated the file for each date.

    Could you please help me with the options urgently?

  • Thanks for the post
    I created a sql script which captures the contents of various columns in a table that are going to be changed in a mass update routine. The captured values are output in “update” statements so they can be used for recovery purpose’s. There are going to be over 130,000 lines that will be built by this pre update capture script.

    I was going to run this script from toad and save it to a file, but the overhead of the Toad GUI was just not acceptable in the amount of time it took to run. So, I was looking for a DOS/SQLPLUS solution.

    This gave it to me.

    Thanks

  • Larry – this saved me so much time! Thanks.

  • hi larry,

    I did the same what you mentioned above, but i have a column ‘address’ which itself has comma [ like PEI,CANADA ], when i run the script the PEI and CANADA are coming in different tab in csv file.

    Can you please let me know how to handle this
    Regards,
    Raj

  • Thanks for the information.. very useful 🙂

You must be logged in to post a comment.