Screens: SQL editor

Exporting results

Save a SQL result as a CSV, JSON or Excel file. The desktop app's local server writes the file on this PC, so this works only in the desktop app (Web and desktop). Exporting tables to files from the explorer (Data export, import and native backup) uses the same dialog; this page describes the dialog's options.

Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects

What's on the screen

No.NameDescription
1Step indicatorThe Format, Options, Preview and Output steps. The Output step exists only when the whole result is written to the file (see 'Which rows go into the file' below).
2Format optionsOptions of the chosen format. JSON has no format options. See the tables below.
3AdvancedThe notice about loss on re-import and the extraction settings.
4Step buttonsThe Previous button, the Next button, and the Download button on the last step. Clicking outside the dialog or pressing Esc does not close it.

Exporting a result

  1. Run the SQL and click the Export button in the result tools. The Export Data dialog opens.
  2. In the Format step, choose CSV, JSON or Excel. The SQL to export (SELECT Query) is shown below.
  3. In the Options step, set the format options.
  4. In the Preview step, set how each column's values are written in the Column Value Formats table, and see in the preview below how the first 20 rows of the result go into the file.
  5. If there is an Output step, pick a folder in the Output folder field and check the File name pattern field.
  6. Click the Download button.
  • The Export button is enabled only on SELECT result tabs.
  • The dialog closes and the export runs as a task in the status bar at the bottom. When the whole result is written, the task list shows the number of rows written so far and lets you cancel, and a notice shows the row count when it finishes.
  • The format, options and output folder you chose are remembered on this device and prefilled the next time you open the dialog.

Which rows go into the file

Which rows go into the file and which options are available depend on the mode chosen in the Filter/Sort bar of the result area. The modes are explained in Result grid.

Client mode (default)Server mode
RowsOf the fetched rows, those visible on the screen. Filters, sorting and the search field of Client mode apply as on the screenThe whole result, read by the local server running the SQL again. Filters and sorting of Server mode apply
Long text and binaryThe values as shown (CLOB up to the first 1,000 characters, binary as markers such as [BLOB])The full original values. Binary can also be written as Base64
Output stepNone. The file is saved as a download named table_date_timePresent. Set the folder and the file name pattern
CSV EncodingUTF-8UTF-8, EUC-KR (CP949), UTF-16 LE
Large result splittingSplit into sheetsSplit into sheets or files
Data extractionNonePresent
  • If the result comes from one table, the file name is based on that table's name; otherwise it is query_result.
  • In Databricks, a result that uses the SQL tab's session writes only the fetched rows even in Server mode, because temporary views and variables exist only in that session.

Options

CSV

OptionDescription
CSV EncodingThe file's character encoding. Turn on the Include BOM option so Excel recognizes UTF-8. EUC-KR has no BOM
Field delimiterChoose comma, semicolon or pipe, or type one character. Turn on the Use tab delimiter option to separate with tabs
HeaderWhether the first line holds the column names. The Header case option turns the names into upper or lower case
Quote character, QuotingThe character that wraps values, and whether values are wrapped only when needed or always
NULL stringThe text written for NULL. Leave it empty to write an empty value
Line breaks in cellsWhether line breaks inside values are kept or replaced with a space or another character

Excel

OptionDescription
Long text handling (cells over 32,767 chars)What to do with values longer than the 32,767 characters an Excel cell holds. Choose the Split into continuation rows (keeps content, modifies row structure), Truncate at the limit (loses content beyond 32,767 chars) or Fail the export (guarantees fidelity) option
Large result splittingPast the set number of rows (Rows per split), continue in the next sheet or the next file. The default is the maximum number of rows in one sheet
Include info sheet (first "Info" sheet with SQL, columns, row count)Writes the executed SQL, the column list and the row count to a first sheet named Info

Common and advanced

OptionDescription
Binary Data HandlingWhether binary values are written as markers such as [BLOB] or as Base64 with the original bytes. Available only in Server mode
Fetch sizeHow many rows the local server reads from the DB at a time
Use paginated queriesQueries the result several times, Rows per query rows at a time. See 'Things to keep in mind' below
Concurrent export jobsHow many tables are exported at the same time when you export several tables from the explorer

Column value formats

In the Column Value Formats table of the Preview step, choose for each column's Cell Format field the General, Text (lossless), Number or Date option, and type a display format for numbers and dates. Excel uses that format as the cell format; CSV and JSON write the text converted to that format. Formats chosen here do not count as the loss described below.

Loss on re-import

If even one value could not be written as its original because of format limits, _lossy is added to the end of the file name and a warning appears when the export finishes. Such a file cannot be re-imported as the original data (If any value is modified due to format limits, the file cannot be re-imported as original data. Files with actual modifications are marked with "_lossy" in the filename.).

When loss occursHow to avoid it
CLOB written from the screen value (first 1,000 characters) and binary markersExport in Server mode and write binary as Base64
Excel cells over 32,767 characters continued or truncatedExport as CSV or JSON
Characters that EUC-KR cannot represent (replaced with ?)Export as UTF-8

The preview in the Preview step shows in advance how many cells will be affected.

Differences from copying to the clipboard

Copying in the result areaExporting results
WhereWeb and desktopDesktop
RowsSelected rows or all fetched rowsFetched rows or the whole result (table above)
FormatsTSV, CSV, Markdown table, INSERT statementsCSV, JSON, Excel, with format options
ValuesThe values as shownThe values with the column value formats applied

How to copy is explained in Result grid.

In the web app

The web app also shows the Export button. Clicking it only shows the notice This feature is only available in the desktop app.. In the web app, copy from the result area instead.

Things to keep in mind

  • In Client mode, only the fetched rows visible on the screen go into the file, in the screen's order. If the result was truncated ((truncated)), the remaining rows are missing.
  • Server mode runs the SQL once more. A large result keeps the DB busy for a long time, and the content may differ from the result you saw on screen.
  • With the Use paginated queries option on, the result is queried in several parts. Without a stable ORDER BY, or if the data changes during the export, rows can be duplicated or skipped.
  • In an account project, columns masked for your role are written to the file with the masked values.