Execute Query to a File

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

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