Execute Query to a File

Use our editor commands to spool the results of a SQL statement(s) to a file(s).

To do this you must issue the following commands:

  • Use the #+sql command to tell the editor you will be writing SQL in this section.
  • You will then use the: output file command to tell the editor you will spool the results to an output file
  • In the table below is a series of parameters and descriptions to be used when spooling
  • Use the #+begin statement to tell the editor you from here until the #+end command you will have SQL to execute.
  • Write your SQL
  • End with #+end
  • Use the Run commands as you would any normal SQL statement as described in the Run Scope section above.

The code below is an example you can use to build off of.

 
    -- This will write the SQL statement between #+begin and #+end to the file specified.

    #+sql
     :output file
        :path '/users/jschlitt/Downloads/jeff1'
        :delimiter ','
        :null_value 'NULL'
        :quote_char '"'
        :overwrite true
    #+begin
    select * from wb_pro_marketing_list
    where emaildomain = 'aginity.com'
    #+end
Note
We only support today the file type of csv. You can still change the delimiter but all files with have a csv extension

The table below illustrates the current values accepted in execute to file commands

Parameter Options
path On Mac this should be in the format “/dir/dir/filename”. On Windows it should be in the format “drive:directoryfilename”.
delimiter You can use any value but we recommend, or | or ^ or t for (tab)
null_value enter a character field for null typically ‘’ (empty string) or ‘NULL’
quote_char enter how to enclose character string typically the quotation (“)
overwrite whether or not to allow overwrite if file exists: true or false
header true or false on whether you want to display headers in file
encoding the encoding of the file UTF_8 is the only value supported currently.
compression we can compress the file after it is written. Values are NONE, ZIP and GZIP.
 
See also
We have a great knowledge base article on how to use the catalog to facilitate using the export as file editor feature.

 

Note: You can also export query results to a file within the results grid.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.