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.
What's on the screen
| No. | Name | Description |
|---|---|---|
| 1 | Step indicator | The 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). |
| 2 | Format options | Options of the chosen format. JSON has no format options. See the tables below. |
| 3 | Advanced | The notice about loss on re-import and the extraction settings. |
| 4 | Step buttons | The 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
- Run the SQL and click the Export button in the result tools. The Export Data dialog opens.
- In the Format step, choose CSV, JSON or Excel. The SQL to export (SELECT Query) is shown below.
- In the Options step, set the format options.
- 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.
- If there is an Output step, pick a folder in the Output folder field and check the File name pattern field.
- 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 | |
|---|---|---|
| Rows | Of the fetched rows, those visible on the screen. Filters, sorting and the search field of Client mode apply as on the screen | The whole result, read by the local server running the SQL again. Filters and sorting of Server mode apply |
| Long text and binary | The 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 step | None. The file is saved as a download named table_date_time | Present. Set the folder and the file name pattern |
| CSV Encoding | UTF-8 | UTF-8, EUC-KR (CP949), UTF-16 LE |
| Large result splitting | Split into sheets | Split into sheets or files |
| Data extraction | None | Present |
- 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
| Option | Description |
|---|---|
| CSV Encoding | The file's character encoding. Turn on the Include BOM option so Excel recognizes UTF-8. EUC-KR has no BOM |
| Field delimiter | Choose comma, semicolon or pipe, or type one character. Turn on the Use tab delimiter option to separate with tabs |
| Header | Whether the first line holds the column names. The Header case option turns the names into upper or lower case |
| Quote character, Quoting | The character that wraps values, and whether values are wrapped only when needed or always |
| NULL string | The text written for NULL. Leave it empty to write an empty value |
| Line breaks in cells | Whether line breaks inside values are kept or replaced with a space or another character |
Excel
| Option | Description |
|---|---|
| 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 splitting | Past 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
| Option | Description |
|---|---|
| Binary Data Handling | Whether binary values are written as markers such as [BLOB] or as Base64 with the original bytes. Available only in Server mode |
| Fetch size | How many rows the local server reads from the DB at a time |
| Use paginated queries | Queries the result several times, Rows per query rows at a time. See 'Things to keep in mind' below |
| Concurrent export jobs | How 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 occurs | How to avoid it |
|---|---|
CLOB written from the screen value (first 1,000 characters) and binary markers | Export in Server mode and write binary as Base64 |
| Excel cells over 32,767 characters continued or truncated | Export 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 area | Exporting results | |
|---|---|---|
| Where | Web and desktop | Desktop |
| Rows | Selected rows or all fetched rows | Fetched rows or the whole result (table above) |
| Formats | TSV, CSV, Markdown table, INSERT statements | CSV, JSON, Excel, with format options |
| Values | The values as shown | The 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.
Related screens
- Result grid: the result area, the two filter modes, and copying.
- Data export, import and native backup: the same dialog for exporting tables from the explorer.
- Web and desktop: the list of desktop-only features.
- App menu, top bar and status bar: the status bar where export tasks appear.