Theory
The marks arrive as a spreadsheet
The faculty will never type INSERT statements. What lands in ResultDesk's inbox is dbms_marks.csv, exported from Excel:
roll,subject,score
101,DBMS,78
102,DBMS,55
Sixty rows. Typing sixty INSERTs is a punishment, not a workflow.
The sqlite3 shell can swallow the whole file in two commands, IF you handle one small detail correctly: that first line.
Theory
The delivery van and the gate register
A CSV file is a delivery van: goods stacked in rows, a label sheet on top (the header) naming what each column carries.
.import is the gate: it unloads every row into your table. The catch: if the storeroom (table) already exists with its own labels, the van's label sheet is just another box to the gate: it gets shelved as data. Deciding what happens to the label sheet is the entire skill of importing.
Theory
The two-command import
.mode csv switches the shell's parser to comma-separated format (it also handles quoted fields with commas inside).
.import dbms_marks.csv marks reads the file into the table.
Then everything depends on the target:
- Table does not exist: .import creates it, using the header line for column names (all TEXT).
- Table exists: .import inserts every line as data, header included: a bogus row ('roll', 'subject', 'score') lands in your table.
Follow along
Import into the existing marks table, cleanly
- .mode csv Tell the shell the incoming file is comma-separated.
- .import --csv --skip 1 dbms_marks.csv marks Modern SQLite: --skip 1 jumps the header row. (Older shells: import, then DELETE FROM marks WHERE roll = 'roll'; to remove the bogus row.)
- SELECT COUNT(*) FROM marks; Verify the count grew by exactly the file's data rows: 60 rows in the van, 60 new rows shelved.
- Spot-check types: SELECT roll, score FROM marks LIMIT 3; Values imported into an EXISTING table follow its affinities; a table CREATED by .import holds everything as TEXT.
Quiz
A student runs .mode csv then .import marks.csv marks into the EXISTING marks table, then finds a row ('roll', 'DBMS'... wait, actually ('roll','subject','score')) in the data. Why?
- The header line was imported as a data row: .import into an existing table treats every line as data
- The CSV file was corrupted during download
- .mode csv always adds a title row to tables
- SQLite requires headers to be stored for later exports
Show the answer
The header line was imported as a data row: .import into an existing table treats every line as data
When the target table already exists, .import has no reason to treat line 1 specially: the header travels in like any row, becoming the classic bogus record. Fixes: --skip 1 on the import, strip the header first, or DELETE the bogus row after. Options B to D invent behaviours; the header-as-data trap is real, common, and precisely what this topic exists to teach.
Think first
Predict the created table
The table allmarks does NOT exist. You run .mode csv then .import all_terms.csv allmarks on a file whose header is roll,subject,score. Before tapping: does the import work, what columns does allmarks get, and what is the catch with the score column?
Show the answer
It works: .import creates allmarks, taking column names roll, subject, score from the header (used as labels, not inserted as data in this case).
The catch: every column is created with TEXT affinity: score holds '78' the string, not 78 the number. SUM(score) may still work through conversion, but ORDER BY can sort '9' above '78'. Professional habit: create the typed table yourself FIRST, then import into it with --skip 1.
Watch out
The import checklist exams reward
1. .mode csv BEFORE .import, or the parser misreads the file.
2. Existing table: deal with the header (--skip 1, pre-strip, or post-delete).
3. New table via .import: all columns TEXT: numbers need care.
4. Verify with COUNT(*) against the file's row count.
Write these four in a "how do you import a CSV" answer and it is complete.
Theory
CSV is the lingua franca
Every tool you meet after this speaks CSV: Excel exports it, banks and exam boards mail it, pandas reads it with one call (read_csv, Unit 4, where THIS import happens in Python instead of the shell). Master the header rule once here and it repays you in every tool: the question is always "is line 1 labels or data?"
Summary
Key takeaways
- CSV: plain-text rows, comma-separated, first line usually a header.
- Import = .mode csv, then .import file.csv table.
- Existing table: every line becomes data, header included: skip it (--skip 1) or delete the bogus row.
- Non-existing table: .import creates it from the header, but all columns are TEXT.
- Verify every import with SELECT COUNT(*).
- Memory hook: the delivery van's label sheet: labels or cargo?