Fixing Expected Columns but Found Errors in SQLite Imports

The message looks like an error and behaves like a note: bad-shapes.csv:3: expected 3 columns but found 2 - filling the rest with NULL. The import went on, the row is in the table, and the shell's exit status is 0, so a script running it considers the job done. Each of the five messages the SQLite shell prints while importing a CSV means something specific about the file, and two of them mean the data in the table is not what the file says. This article reproduces each from a five line file, shows what lands in the table, and gives the check that fails properly. The full import path is in the SQLite import guide. The messages come from the shell's import code, which the command line shell documentation describes, and the quoting rules it applies are those of RFC 4180.

The file that produces most of them

Five records, three of which are damaged. Record 2 has two fields instead of three, record 3 has four, and record 4 opens a quote that never closes:

id,name,qty
1,Ana,3
2,Bo
3,Cy,5,extra
4,"Dee,6
5,Eli,7

Imported with the usual command, the shell reports four problems and loads what it understands:

rm -f e.db; sqlite3 e.db ".import --csv bad-shapes.csv t" "SELECT count(*) AS rows_imported FROM t" "SELECT * FROM t" 2>&1

bad-shapes.csv:3: expected 3 columns but found 2 - filling the rest with NULL
bad-shapes.csv:4: expected 3 columns but found 4 - extras ignored
bad-shapes.csv:5: unterminated "-quoted field
bad-shapes.csv:5: expected 3 columns but found 2 - filling the rest with NULL
4
1|Ana|3
2|Bo|
3|Cy|5
4|Dee,6
5,Eli,7
|

The count says 4 rows, and the table has records 1 to 3 and then something odd. Walk through the messages in order.

The most common one: short records

bad-shapes.csv:3: expected 3 columns but found 2 - filling the rest with NULL. Line 3 is 2,Bo. SQLite padded the missing qty with NULL, and the table row is 2|Bo|. The usual causes: a comma that was lost, a field that contained a newline and was exported without quotes, a line that was cut off, or a record from a different layout mixed into the file. The fix is upstream, or in the file, and the pad is a convenience that hides the problem. If a missing quantity matters, a NULL there is wrong, and there is no way to tell from the table which NULLs came from padding and which came from empty fields.

Long records

bad-shapes.csv:4: expected 3 columns but found 4 - extras ignored. Line 4 is 3,Cy,5,extra, and the table row is 3|Cy|5: the fourth field was dropped. The usual cause is an unquoted comma inside a value, for example a company name like Smith, Jones and Co, which splits into two fields and shifts every later field one place right. In that case the dropped field is not extra data, it is the end of something, and the values in the later columns are wrong too. When you see this message, look at the line; if the fourth field looks like the continuation of the third, the writer failed to quote. The quoting article explains why, and the PostgreSQL version of this error stops the load instead.

The unterminated quote

bad-shapes.csv:5: unterminated "-quoted field followed by expected 3 columns but found 2. Line 5 is 4,"Dee,6: the field opens a quote and never closes it, so the reader keeps reading into the next line looking for the closing quote and finds the end of the file. Everything after the quote becomes one field, Dee,6\n5,Eli,7\n, which is why the output showed a row 4|Dee,6 and then a line 5,Eli,7 and a lone |. The count of 4 is wrong too: there were five data records, and the unterminated quote ate the fifth. The cause is a quote character in text that was not escaped, such as a measurement in inches. A separate file shows the sibling message, for a quote after a closing quote:

rm -f e.db; sqlite3 e.db ".import --csv quote-after.csv t" "SELECT count(*) AS rows_imported FROM t" "SELECT * FROM t" 2>&1

quote-after.csv:3: unescaped " character
quote-after.csv:3: unterminated "-quoted field
quote-after.csv:3: expected 3 columns but found 2 - filling the rest with NULL
2
1|Ana|3
2|Bo"x,4
3,Cy,5
|

quote-after.csv:3: unescaped " character came with unterminated "-quoted field, since "Bo"x closed the quote early and the stray x,4 carried on. The second record's name field became Bo"x,4 followed by the whole next line, so one table row holds two records and the quantity is NULL.

The quote that gives no message

A quote in the middle of an unquoted field is not an error in SQLite's reader. It is kept as data:

rm -f e.db; sqlite3 e.db ".import --csv quote-inside.csv t" "SELECT count(*) AS rows_imported FROM t" "SELECT * FROM t" 2>&1

2
1|Ana|3
2|B"o|4

The record 2,B"o,4 loaded as 2|B"o|4 and the shell printed nothing. That is lenient and consistent with how most readers behave, as the CSV format guide shows in its comparison, and PostgreSQL is the one that rejects it. If a quote character in the middle of values worries you, search for it: grep -n '[^,]"[^,]' file.csv finds lines with a quote that is not at a field boundary.

Why the exit status is 0

The shell treats all of these as notices. I ran an import with .bail on, the setting that makes a script stop at the first error, and the load carried on through all four messages and exited with status 0:

rm -f e.db; sqlite3 e.db ".bail on" ".import --csv bad-shapes.csv t" "SELECT count(*) FROM t" 2>&1; echo "exit status $?"

bad-shapes.csv:3: expected 3 columns but found 2 - filling the rest with NULL
bad-shapes.csv:4: expected 3 columns but found 4 - extras ignored
bad-shapes.csv:5: unterminated "-quoted field
bad-shapes.csv:5: expected 3 columns but found 2 - filling the rest with NULL
4
exit status 0

The same with 2>/dev/null, discarding standard error, showed exit status ... 0 for the file with the stray quote:

exit status with the warning on stderr: 0

.bail governs SQL errors, and these are not that. So a script that checks $? and nothing else cannot see them. The errors with INSERT failed are different: a constraint violation or a type mismatch in a STRICT table is an error, the row is skipped, the rest load and the exit status is 1.

Finding the line before the import

The strict way is to check the file first. qsv count is a CSV reader that stops at a record whose width differs from the previous one:

qsv count bad-shapes.csv 2>&1 | head -2

csv error: CSV error: record 2 (line: 3, byte: 20): found record with 2 fields, but the previous record has 3 fields

found record with 2 fields, but the previous record has 3 fields with the record number (2), the line (3) and the byte offset (20). That is as good as an error message gets, and it is a failure with a nonzero exit status. When you need the whole list and not the first, count the fields of every record with Python's csv module, which understands quotes:

python - <<'PY'
import csv
with open("bad-shapes.csv", newline="") as f:
    for n, row in enumerate(csv.reader(f), start=1):
        if len(row) != 3:
            print(f"record {n} has {len(row)} fields: {row}")
PY

record 3 has 2 fields: ['2', 'Bo']
record 4 has 4 fields: ['3', 'Cy', '5', 'extra']
record 5 has 2 fields: ['4', 'Dee,6\n5,Eli,7\n']

Three records are wrong: record 3 with 2 fields, record 4 with 4 and record 5 with 2 fields, the last being ['4', 'Dee,6\n5,Eli,7\n'], the swallowed text of the unterminated quote. The numbers are record numbers counting the header as record 1, which matches the shell's line numbers here because no record contains a quoted newline.

Making the script fail

Run the import with standard error captured and treat any output as failure, and compare the count to the file's. The load script in the import guide does both:

log=$(sqlite3 db.db ".import --csv --skip 1 file.csv t" 2>&1 >/dev/null || true)
[ -z "$log" ] || { echo "import said: $log" >&2; exit 1; }

For a one-off, sqlite3 ... 2>&1 | grep -q . does the same. And read the count against the file's record count: if the unterminated quote ate a record, the count is lower than expected.

When the message is a symptom of something else

If every record is reported as short, the delimiter is wrong; check the first lines, as in telling the delimiter of an unknown file. If the first line is reported and the table has a single column named after the whole header, you forgot --csv. If messages begin only after a certain line, two files were concatenated or the export was cut off, and the same count check finds the line. And if a file with Windows line endings reports nothing wrong and the last column carries a carriage return, that is a different problem entirely, covered in the line endings article.