Import, Export, .once/.output, CSV, and Reproducible Data Exchange

Learning outcomes

The CLI can move tabular data between SQLite and text files, but that convenience creates a dangerous misconception: a CSV export is not a copy of the database. This lesson teaches import/export as an explicit data pipeline—parse, stage, validate, transform, verify, and only then keep the result.

CSV is rows and fields, not a database schema

A CSV file can represent rectangular text data. It does not intrinsically preserve SQLite primary keys, foreign keys, CHECK constraints, indexes, triggers, views, type affinity, STRICT-table rules, journal mode, or application metadata. Even a header row is only text by convention.

Understand RFC-style quoting before .import

CSV uses delimiters to separate fields and quotation rules so a field can itself contain a comma, quote, or newline. Current SQLite CLI documentation applies RFC 4180-style parsing in normal delimited modes, with configurable separators. A learner who treats CSV as “split each line on commas” will eventually corrupt data.

device_code,device_name,status,site_namePUMP-008,"Cooling Pump, East",active,North PlantVALVE-021,"O'Brien bypass valve",inspection_due,North PlantSENS-004,"Temperature sensor",active,Harbor Lab

The comma inside Cooling Pump, East is data because the field is quoted. An importer must understand the CSV grammar; a naive text split would invent an extra column.

Import into staging, not directly into trusted tables

If the target table does not exist, .import can create a table and use the first input row as column names. That is convenient for exploration but is a weak production pattern because the file then defines schema implicitly. This course creates the staging table first, so every input row—including its types and validation status—enters a controlled structure.

DROP TABLE IF EXISTS temp.stage_device_import;CREATE TEMP TABLE stage_device_import (    source_row   INTEGER,    device_code  TEXT,    device_name  TEXT,    status       TEXT,    site_name    TEXT);

Because the CSV has a header row and the staging table already exists, tell .import to skip the first row.

.import --csv --skip 1 incoming_devices.csv stage_device_importSELECT COUNT(*) AS staged_rows FROM stage_device_import;SELECT * FROM stage_device_import;

Validate before transforming

Staging is valuable because invalid records can be diagnosed without contaminating the final tables. For FieldNotes, validate required text, allowed statuses, site existence, and duplicate device codes before inserting.

-- Missing required valuesSELECT * FROM stage_device_importWHERE trim(device_code) = ''   OR trim(device_name) = ''   OR trim(status) = ''   OR trim(site_name) = '';-- Unknown statusesSELECT * FROM stage_device_importWHERE status NOT IN ('active','inspection_due','retired');-- Unknown sitesSELECT s.*FROM stage_device_import AS sLEFT JOIN site AS p ON p.site_name = s.site_nameWHERE p.site_id IS NULL;-- Existing device codesSELECT s.device_codeFROM stage_device_import AS sJOIN device AS d ON d.device_code = s.device_code;

A clean validation result is an observation you can explain. “The import command printed no error” is weaker: syntactically valid CSV can still violate your business rules.

Transform into final rows inside a transaction

Once validation passes, map human-readable site names to foreign-key IDs and insert final device rows. Use a transaction so the transformation is atomic at the database level.

BEGIN;INSERT INTO device(site_id, device_code, device_name, status)SELECT p.site_id,       s.device_code,       s.device_name,       s.statusFROM stage_device_import AS sJOIN site AS p ON p.site_name = s.site_name;SELECT changes() AS inserted_rows;-- Verify before commit.SELECT device_code, device_name, statusFROM deviceWHERE device_code IN ('PUMP-008','VALVE-021','SENS-004')ORDER BY device_code;COMMIT;

If verification shows the wrong rows, use ROLLBACK instead of COMMIT. Later transaction chapters will explore locking and failure behavior in depth; here the goal is safe workflow discipline.

.once redirects exactly the next result; .output stays redirected

Exports fail surprisingly often because a script leaves output redirected and subsequent diagnostics disappear into a file. .once FILE is safer for a single query because the shell automatically returns to the previous destination afterward. .output FILE persists until you change it back, usually with .output stdout or the no-argument form supported by your build.

.mode csv --titles on.once exported_devices.csvSELECT d.device_code,       d.device_name,       d.status,       s.site_nameFROM device AS dJOIN site AS s ON s.site_id = d.site_idORDER BY d.device_code;

.output report.txt.mode markdownSELECT device_code, status FROM device ORDER BY device_code;SELECT COUNT(*) AS total_devices FROM device;.output stdout

NULL is a data-contract problem in CSV

A blank CSV field might mean SQL NULL, an empty string, missing data, or “not applicable,” depending on the producing system. SQLite's output formatter can choose a string for NULL, but the receiving system must agree. Do not invent a sentinel such as NULL unless both sides document that convention and escape literal occurrences safely.

CREATE TEMP TABLE null_demo(value TEXT);INSERT INTO null_demo VALUES (NULL),(''),('NULL');.mode csv --titles on --null "<SQL-NULL>"SELECT rowid, value, typeof(value) FROM null_demo;

The export now distinguishes the three cases for a human reader, but the string <SQL-NULL> is still an interchange convention, not a built-in CSV type.

Export modes are presentation; backup is state preservation

A CSV export is useful for interchange and reporting. .dump creates SQL text representing schema/data more broadly. .backup works at the database level. These tools solve different problems, and reliable backup/recovery has transaction, WAL, verification, and restore considerations that deserve an entire chapter.

End-to-end lab: import → validate → transform → export

Perform this lab only on chapter02_cli.db or another disposable copy.

1. Create incoming_devices.csv with the three valid rows shown earlier.
2. Create the staging table explicitly.
3. Run .import --csv --skip 1.
4. Run all four validation queries and require zero problem rows.
5. Begin a transaction, insert through the site join, verify exactly three new devices, then commit.
6. Export all devices to exported_devices.csv with explicit CSV mode and titles.
7. Open the exported file as text and verify quoting around the comma-containing and apostrophe-containing names.
8. Drop the temporary staging table or simply close the connection, because TEMP objects are connection-scoped.

SELECT COUNT(*) AS total_devices FROM device;SELECT d.device_code, d.device_name, d.status, s.site_nameFROM device AS dJOIN site AS s ON s.site_id=d.site_idORDER BY d.device_code;

Starting from the three Chapter 1 devices and importing three new valid devices, the expected final count is six.

Chapter 2 completion checklist

• You can tell SQL from dot-commands and recover from continuation state.
• You inspect CLI capabilities with .help and shell state with documented current commands.
• You choose explicit result modes rather than relying on context-sensitive defaults.
• You can run a script non-interactively and make failures visible to the parent shell.
• You bind values with parameters instead of constructing SQL source text.
• You stage and validate imported data before promoting it into trusted tables.
• You distinguish result export, logical dump, and database backup.

Summary and bridge to Chapter 3

You now have a transparent sqlite3 workflow: investigate the active database, control rendering explicitly, execute repeatable scripts, observe failure status, bind values safely, and exchange tabular data without confusing a CSV file with a database. Chapter 3 shifts attention from the shell to the SQLite schema itself: sqlite_schema, rowid tables, INTEGER PRIMARY KEY, WITHOUT ROWID, temporary objects, and deliberate table design.

References

• SQLite — Command Line Shell For SQLite.
• SQLite — Query Result Formatting In The CLI.
• SQLite — Download Page.
• SQLite — Release History.