Skip to main content

Exporting Data

Exporting lets you move data from SQLite into tools like spreadsheets, scripts, and reports.

In the CLI, the usual pattern is set output mode, route output to file, run query.

Core Concepts​

flowchart TD
  A[Choose output format] --> B[Set .headers and .mode]
  B --> C[Set .output file.csv]
  C --> D[Run SELECT query]
  D --> E[Reset .output stdout]
CommandPurpose
.mode csvCSV formatting
.headers onInclude column header row
.output file.csvWrite results to file
.output stdoutReturn output to terminal

Code Examples​

-- Configure CSV export with headers.
.headers on
.mode csv
.output people_export.csv

-- Export selected columns.
SELECT id, name FROM people;

-- Restore output back to terminal.
.output stdout
Expected output
people_export.csv is created with header row and query results.

SQLite-Specific Nuances​

SQLite Nuance

CSV export behavior here belongs to the sqlite3 shell layer (.mode, .output), not core SQL.

Common Pitfalls / Best Practices​

Pitfall

Forgetting .output stdout after exporting.

Later query output may keep going to file, which looks like "no terminal output".

Best Practice

Always include .headers on before CSV exports unless you explicitly need headerless files.

Quick Challenge​

Export the result of SELECT 'Alice' AS name, 90 AS score; to scores.csv.

View Solution
.headers on
.mode csv
.output scores.csv
SELECT 'Alice' AS name, 90 AS score;
.output stdout
Expected file row
name,score then Alice,90