First column of sqlite table throwing error: no such column

Question

This seems odd to me and violates my previous experience with SQLite. I have a trivial database with one table and 3 columns. When I query the first column I get 'Error: no such column: Name'. The other columns work fine. Log below. There must be something ridiculously basic that I am missing. My sleazy work-around is to add a dummy first column, at which point querying on 'Name' works fine, but that is pretty unsatisfactory. Running on a Mac mini (M1, 2020), macOS Monterey Version 12.5.1.

bash-3.2$ head Names/nameTest1.csv
Name,Gender,Count
James,M,5304407
John,M,5260831
Robert,M,4970386
...

bash-3.2$ sqlite3 test1.db
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .separator ','
sqlite> .import Names/nameTest1.csv testtable1
sqlite> .schema
.schema
CREATE TABLE testtable1(
  "Name" TEXT,
  "Gender" TEXT,
  "Count" TEXT
);
sqlite> select * from testtable1 where Count='5304407';
James,M,5304407
sqlite> select * from testtable1 where Name='James';
Error: no such column: Name
sqlite> .quit
.quit

user_17472988 · Answer

Your CSV file has the BOM field. It does not correspond to a printable character, so you do not see it, but SQLite fails to strip it and includes it as the the prefix of your Name field. This is a known issue, but recent versions of SQLite should handle it properly. You might be using an older version. Either upgrade your SQLite or strip the BOM code before importing.

First column of sqlite table throwing error: no such column

1 Answers