將 csv 文件導入 sqlite3 表 - 錯誤“插入失敗：數據類型不匹配”

我正在使用 sqlite3，我在兩個文件中編寫腳本。第一個稱為“Books.sql”

DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
   books_id INTEGER PRIMARY KEY AUTOINCREMENT,
   title TEXT,
   author TEXT,
);

第二個稱為“Import_books.sql”

.mode csv Books
.import C:/Users/Jim/books_data.csv Books

我的目標是在“Import_books.sql”中導入包含書籍列表的 csv 文件“books_data.csv”到我在“Books.sql”中創建的表 Books

我確保 csv 與表的結構完全匹配，但出現此錯誤 INSERT failed: datatype mismatch。我該如何解決這個問題？

我嘗試使用您的 csv 格式導入

   CREATE TABLE Books1 (
   books_id INTEGER PRIMARY KEY AUTOINCREMENT,
   title TEXT,
   author TEXT
   );
   sqlite> .mode csv
   sqlite> .separator "-"
   sqlite> .import books1.csv books1
   books1.csv:1: unescaped " character
   books1.csv:2: unescaped " character
   books1.csv:2: unescaped " character
   books1.csv:3: unescaped " character
   books1.csv:1: expected 3 columns but found 1 - filling the rest with NULL
   books1.csv:1: INSERT failed: datatype mismatch

I fixed csv removing quotes for each record and comma that separates records 

sqlite> .import books1.csv books1
sqlite> select * from books1;
337864-" Pride and Prejudice "-" Jane Austen"
783356-" Ivanhoe "-" Walter Scott"
987786-" Harry Potter and the Goblet of Fire "-" J.K. Rowling"

您是否注意到前兩列後的連字元不整齊。

我將 csv 文件修復為正確的格式，這就是結果

sqlite> select * from books;     
337864,"Pride and Prejudice","Jane Austen"
783356," Ivanhoe","Walter Scott"
987786," Harry Potter and the Goblet of Fire","J.K. Rowling"

引用自：https://dba.stackexchange.com/questions/263877