How to import file into sqlite?

Question

On a Mac, I have a txt file with two columns, one being an autoincrement in an sqlite table:

, "mytext1"
, "mytext2"
, "mytext3"

When I try to import this file, I get a datatype mismatch error:

.separator ","
.import mytextfile.txt mytable

How should the txt file be structured so that it uses the autoincrement?

Also, how do I enter in text that will have line breaks? For example:

"this is a description of the code below.
The text might have some line breaks and indents.  Here's
the related code sample:

foreach (int i = 0; i < 5; i++){

  //do some stuff here

}

this is a little more follow up text."

I need the above inserted into one row. Is there anything special I need to do to the formatting?

For one particular table, I want each of my rows as a file and import them that way. I'm guessing it is a matter of creating some sort of batch file that runs multiple imports.

Edit

That's exactly the syntax I posted, minus a tab since I'm using a comma. The missing line break in my post didn't make it as apparent. Anyways, that gives the mismatch error.

Answer 1

I was looking on the same problem. Looks like I've found an answer on the first part of your question — about importing a file into a table with ID field.

So yes, create a temporary table without ID, import your file into it, then do insert..select to copy its data into your target table. (Remove leading commas from mytextfile.txt).

-- assuming your table is called Strings and
-- was created like this:
-- create table Strings( ID integer primary key, Code text )

create table StringsImport( Code text );
.import mytextfile.txt StringsImport
insert into Strings ( Code ) select * from StringsImport;
drop table StringsImport;

Do not know what to do with newlines. I've read some mentions that importing in CSV mode will do the trick (.mode csv), but when I tried it did not seem to work.

Answer 2

I'm in the process of moving data containing long text fields with various punctuation marks (they are actually articles on coding) into SQLite and I've been experimenting with various text imports.

I created a database in SQLite with a table:

CREATE TABLE test (id PRIMARY KEY AUTOINCREMENT, textfield TEXT);") 

then do a backup with .dump.

I then add the text below the "CREATE TABLE" line manually in the resulting .dump file as such:

INSERT INTO test textfield VALUES (1,'Is''t it great to have
                     really long text with various punctaution marks and
          newlines');

Change any single quotes to two single quotes (change ' to ''). Note that an index number needs to be added manually (I'm sure there is an AWK/SED command to do it automatically). Change the auto increment number in the "sequence" line in the dump file to one above the last index number you added (I don't have SQLite in front of me to give you the exact line, but it should be obvious).
With the new file, I can then do a restore onto the database