1

I am using sqlite3 and I am writing my scripts in two files. The first one is called "Books.sql"

DROP TABLE IF EXISTS Books;

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

The second one is called "Import_books.sql "

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

My goal is to import in "Import_books.sql", the csv file "books_data.csv" that contains a list of books into the table Books that I created in "Books.sql"

I made sure the csv matches exactly the structure of the table but I get this error INSERT failed: datatype mismatch. How can I solve this?

  • can you post few rows in csv – user153556 Mar 29 at 19:39
  • @Sam the csv file contains a list of 200 books I cannot import them manually one by one the goal is to do this automatically with an import statement – Jim6834 Mar 29 at 20:28
  • I mean to post here 3 or 5 rows want to make sure if your csv has 3 columns or 2 columns since books_id is auto increment how are you adding that column from csv or skipping first column in csv? or another option is to remove autoincrement keyword in ddl and test it – user153556 Mar 29 at 20:32
  • @Sam sure: "337864 - Pride and Prejudice - Jane Austen" , "987786 - Harry Potter and the Goblet of Fire - J.K. Rowling" , "783356 - Ivanhoe - Walter Scott" these are the first rows of the file. It has 3 columns. I am not sure how...do you think autoincrement is not needed? do you think that is why I get the the error? – Jim6834 Mar 29 at 21:57
0

I tried to import using your csv format

    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"

Did you notice hyphens after first two columns not neat.

I fixed csv file to correct format and this is the result

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"
| improve this answer | |
  • Thank you for that! You are writing the import statement on the command line I suppose. I wanted to write it on the file Import_books.sql so the only thing I would have to do on the command line is to call the file – Jim6834 Mar 29 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.