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
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
|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.|
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.