Executing to a File
Use our editor commands to spool the results of your SQL statements to .csv files. You can change the delimiter, but all files will have a .csv extension by default.
To utilize this functionality, do the following:
- Use the #+sql command to tell the editor you will be writing SQL in this section
- Then use the output file command to tell the editor you will spool the results to a file
- Use a series of parameters and descriptions for spooling from the table below
- Use the #+begin statement to tell the editor that from here until the #+end command you'll be executing this SQL
- Write your SQL
- End the code with #+end
- Use the Run commands as you would for any regular SQL statement
Here is an example of how you can execute SQL to a file:
1 -- This will write the SQL statement between #+begin and #+end to the file specified.
2 #+sql
3 :output file
4 :path '/users/jschlitt/Downloads/jeff1'
5 :delimiter ','
6 :null_value 'NULL'
7 :quote_char '"'
8 :overwrite true
9 #+begin
10 select * from wb_pro_marketing_list
11 where emaildomain = 'aginity.com'
12 #+end
The currently accepted parameters in the Execute to a File commands are the following:
|
Parameter |
Options |
|
path |
On Mac, it should be in the format "/dir/dir/filename." On Windows, the format will be "drive:directoryfilename." |
|
delimiter |
You can use any value, but we recommend | or ^, or t for (tab) |
|
null_value |
Enter 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 overwriting if a file already exists: true or false |
|
header |
True or false on whether you want to display headers in a file |
|
encoding |
The UTF_8 file encoding is the only value supported for now |
|
compression |
We can compress a file after it's written—values are NONE, ZIP, and GZIP |
🔎 TIP: Check our knowledge base article on how to navigate the catalogs
and export to a file via CoginitiScript.